This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Unsure how to count records that are the same on a day of a date range

 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry for the long subject but hopefully it was descriptive enough.

I need to run a report that will count the number of clicks a certain item has in the DB on any given day of any given date range. I have tried doing this any number of ways. I never get more than a click count of 1 though. Here's my latest attempt:



When I run this, what I get is 5,906 records all with a click count of 1. What I want is a click count for each occurrence of an offer on any given day. So, if offer 256 was clicked 20 times on 04/20, I will have an entry that reflects 20 for that offer on that day. I want the count to start over for all offers on the next day.

If you can help me understand how to filter this further, it would be even more appreciated. A click count of 20 for offer 256 on 4-20 is step one. But I'd really like a click count for offer 256 on 4-20 restricted to unique IP address. In other words, if 192.1.1.0 clicked the offer 19 times on 4-20 and 192.1.1.1 clicked it once on 4-20, I want the result to show 2 clicks for that offerID on that day and then move to the next day with a new counter.

I don't want to do this in code because it is expensive to do with an iteration and a counter. When I did try doing it that way, the web server usually times me out anyway.

In case you were wondering, I'm casting the date for a comparison I need to do later that does not have a time associated with it (but does have a date).

Thanks for your help.

Best,
Al
 
Jan Cumps
Bartender
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I can't check if the mechanism you use to throw away the time is correct - don't know your database,
but if it is correct, then also use it in the group by:

group by offerID, (CAST(FLOOR(CAST(addDate as FLOAT))
 
Al Johnston
Ranch Hand
Posts: 99
Flex Java Postgres Database
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jan! That worked when I did this:



The DB I am using is MS SQL 2008. My dates all come back with 00:00:00.0000 appended to them now (which is what I want I think - we'll find out when I do my compares I suppose.

I really (really) appreciate your help. In case you can't tell, I'm a total greenhoren on the database side and would like to learn more and do more in the SQL statements to be able to optimize the code a lot better.

Have a great one.

Best,
Al
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic