jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes Unsure how to count records that are the same on a day of a date range Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Unsure how to count records that are the same on a day of a date range" Watch "Unsure how to count records that are the same on a day of a date range" New topic
Author

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

Al Johnston
Ranch Hand

Joined: May 02, 2009
Posts: 99

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

Joined: Dec 20, 2006
Posts: 2491
    
    8


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))


OCUP UML fundamental and ITIL foundation
youtube channel
Al Johnston
Ranch Hand

Joined: May 02, 2009
Posts: 99

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
 
subject: Unsure how to count records that are the same on a day of a date range
 
Similar Threads
pagination in jsp
urgent please
order of request.getParameterValues()
XML Help
need sugesition