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 220.127.116.11 clicked the offer 19 times on 4-20 and 18.104.22.168 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).
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.