Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: COUNT number of occurances in a table given a date range

Results 1 to 4 of 4
Share This Thread →
  1. #1
    CNx
    CNx is offline Registered User
    Join Date
    Jul 2008
    Location
    Los Angeles, CA
    Posts
    25
    Rep Power
    0

    Arrow COUNT number of occurances in a table given a date range

    Fellows,

    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:

    TABLE: CARDS

    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

  2. #2
    sync_or_swim's Avatar
    sync_or_swim is offline Contributing User
    Join Date
    Mar 2006
    Location
    South Wales
    Posts
    3,674
    Rep Power
    2043
    Hi,

    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:
    Code:
    SELECT CardNum, count(CardNum) AS Seen FROM CARDS WHERE Date = getdate() GROUP BY CardNum

  3. #3
    CNx
    CNx is offline Registered User
    Join Date
    Jul 2008
    Location
    Los Angeles, CA
    Posts
    25
    Rep Power
    0
    Quote Originally Posted by sync_or_swim
    Hi,

    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:
    Code:
    SELECT CardNum, count(CardNum) AS Seen FROM CARDS WHERE Date = getdate() GROUP BY CardNum
    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.

    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.

  4. #4
    sync_or_swim's Avatar
    sync_or_swim is offline Contributing User
    Join Date
    Mar 2006
    Location
    South Wales
    Posts
    3,674
    Rep Power
    2043
    Quote Originally Posted by CNx
    In any case, your solution does not address the date issue.
    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.
    Code:
    SELECT CARDS.CardNum, Count(CARDS.CardNum) AS Seen
    FROM CARDS
    WHERE (((CARDS.[Date]) Between DateAdd("d",-2,Now()) And Now()))
    GROUP BY CARDS.CardNum;

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. ASP Keeps telling me isnull is false??
    By olger901 in forum ASP Development
    Replies: 48
    Last Post: April 28th, 2006, 03:09 PM
  2. Display Data from different table
    By erickh in forum ASP Development
    Replies: 31
    Last Post: April 12th, 2006, 08:35 PM
  3. Return Date Range. Eg FROM 12/02/2004 TO 31/12/2004
    By DOLL in forum SQL Development
    Replies: 1
    Last Post: February 20th, 2006, 04:02 AM
  4. Edit date range on a CrossTab query
    By warrenh in forum Microsoft Access Help
    Replies: 1
    Last Post: November 10th, 2005, 05:01 PM
  5. How do I create a date that link to my Pivot Table
    By Crystal in forum ASP Development
    Replies: 0
    Last Post: March 6th, 2003, 10:16 AM

ASP Free Advertisers and Affiliates