September 25th, 2008, 11:07 AM
COUNT number of occurances in a table given a date range
I need help counting records with certain criteria.
First, prior to posting I attempted to search the forums to find a solution. My question is very similar to http://forums.aspfree.com/sql-development-6/count-the-number-of-times-an-item-appears-126369.html?&highlight=count but I could not adapt their solution for my own.
The task seems very simple.
Find the number of times a card number appears in the last two days. My table looks very similar to this:
CardNum (textfield) | Date (DateTime)
011111 | 9/25/2008 8:52:58 AM
222222 | 9/23/2008 9:37:38 PM
011111 | 9/24/2008 11:48:51 PM
123456 | 9/25/2008 3:45:01 AM
123456 | 9/22/2008 12:42:52 AM
011111 | 9/23/2008 7:08:54 PM
Assuming today is the 9/25/2008, I expect the following results:
CardNum | Seen
011111 | 2
222222 | 1
123456 | 1
September 25th, 2008, 11:21 AM
I dont know which DB you are using, which would dictate the syntax for the currentdate, but that aside, try using a Count in conjunction with a group by, not tested:
SELECT CardNum, count(CardNum) AS Seen FROM CARDS WHERE Date = getdate() GROUP BY CardNum
September 25th, 2008, 11:43 AM
I'm using MS Access which poses more problems for me. MS Access uses Now() to which works like getDate() except, now also returns timestamp.
Originally Posted by sync_or_swim
I’ve ran into two problems. First, I find it difficult to run a query to return a specific date due to the way the database column is set (Date with Timestamp). If I do a
SELECT * FROM CARDS WHERE Date = Now() I get nothing.
SELECT * FROM CARDS WHERE Date = #9/25/2008# I get nothing.
SELECT * FROM CARDS WHERE Date = Format(Now(), “mm/dd/yyyy”) I get nothing.
My solution to this was
SELECT * FROM CARDS WHERE Date BETWEEN Format$(DateAdd(“d”, 0, Now(), “mm/dd/yyyy”) AND Format$(Now(), “mm/dd/yyyy”)
In any case, your solution does not address the date issue.
September 26th, 2008, 05:46 AM
It was very hard to give you any code that would address the date issue without knowing what Database you were using, as the way in which different dbs treat dates is radically different. I thought that you just wanted logic with the GROUP BY.
The following query should count occurances in the last two days.
SELECT CARDS.CardNum, Count(CARDS.CardNum) AS Seen
WHERE (((CARDS.[Date]) Between DateAdd("d",-2,Now()) And Now()))
GROUP BY CARDS.CardNum;