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

[Al Johnston]

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:

   

SELECT  offerID, (CAST(FLOOR(CAST(addDate as FLOAT)) AS DateTime))as "date", COUNT (offerID) as "clicks"

FROM dbo.T_TransLog 
WHERE (publisherID = 4) AND (logAction = 2101) AND (addDate BETWEEN '2009-04-20' AND '2009-06-10')
GROUP BY offerID, addDate
ORDER BY addDate

 

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]

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]

Thanks Jan! That worked when I did this:

    
SELECT  (CAST(FLOOR(CAST(addDate as FLOAT)) AS DateTime))as "date", offerID, COUNT (offerID) as "clicks"
FROM dbo.T_TransLog 
WHERE (PublisherID = 4) AND (logAction = 2101) AND (addDate BETWEEN '2009-04-20' AND '2009-06-10')
GROUP BY (CAST(FLOOR(CAST(addDate as FLOAT))AS DateTime)), offerID
ORDER BY (CAST(FLOOR(CAST(addDate as FLOAT))AS DateTime))

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