#1
  1. No Profile Picture
    CNx
    Registered User
    ASP Explorer (0 - 99 posts)

    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
  3. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    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
  4. #3
  5. No Profile Picture
    CNx
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2008
    Location
    Los Angeles, CA
    Posts
    25
    Rep Power
    0
    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.
  6. #4
  7. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Mar 2006
    Location
    South Wales
    Posts
    3,674
    Rep Power
    2043
    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;

Similar Threads

  1. ASP Keeps telling me isnull is false??
    By olger901 in forum ASP Development
    Replies: 48
    Last Post: April 28th, 2006, 04:09 PM
  2. Display Data from different table
    By erickh in forum ASP Development
    Replies: 31
    Last Post: April 12th, 2006, 09: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, 05:02 AM
  4. Edit date range on a CrossTab query
    By warrenh in forum Microsoft Access Help
    Replies: 1
    Last Post: November 10th, 2005, 06: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, 11:16 AM

IMN logo majestic logo threadwatch logo seochat tools logo