• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 99
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
eat bricks! HA! And here's another one! And a tiny ad!
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic