SQL Query Timestamp group by date

Discussion in 'Computer Science & Culture' started by one_raven, May 27, 2008.

Thread Status:
Not open for further replies.
  1. one_raven God is a Chinese Whisper Valued Senior Member

    Messages:
    13,406
    I have a query in which I want to sort the results by date.
    I only have a timestamp field (named: opened), with which to use.

    An entry of the timestamp field looks like this:
    5/11/2008 12:00:26 AM

    Here is my query:

    SELECT t.opened, (select count(environment) from Current where opened=t.opened) AS TOTAL, (select count(environment) from Current where environment="P" and opened=t.opened) AS PROD, (select count(environment) from Current where environment="C" and opened=t.opened) AS COB, (select count(environment) from Current where environment="U" and opened=t.opened) AS UAT, (select count(environment) from Current where environment="D" and opened=t.opened) AS DEV
    FROM [Current] AS t
    GROUP BY t.opened;

    Unfortunately, it groups by the whole timestamp field, not by the date.
    I want it to group by the date.

    Please help.
     
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. mapsdnasggeyerg fubar Registered Senior Member

    Messages:
    62
    What database product are you using?
     
  4. Google AdSense Guest Advertisement



    to hide all adverts.
  5. one_raven God is a Chinese Whisper Valued Senior Member

    Messages:
    13,406
    Right now it is in MS Access, but I will likely convert it to SQL.
     
  6. Google AdSense Guest Advertisement



    to hide all adverts.
  7. nietzschefan Thread Killer Valued Senior Member

    Messages:
    7,721
    So add date..

    GROUP BY t.opened, t(?).Date(?)

    Oh...you don't have date..

    I guess you need another select(~parse the date alone in the field imtoolazytodoit)
     
  8. mapsdnasggeyerg fubar Registered Senior Member

    Messages:
    62
    I don't know much about Access, it does seem to have a function DateValue that extracts the Date portion from the timestamp. You would replace every reference to opened with DateValue(opened).

    As a side note I would write the SQL you gave in the following way (but since I don't know Access I am not sure if it would be valid).

    Code:
    select DateValue(opened) as OPENED, 
           count(environment) as TOTAL, 
           sum(case environment when 'P' then 1 else 0 end) as PROD, 
           sum(case environment when 'C' then 1 else 0 end) as COB,
           sum(case environment when 'U' then 1 else 0 end) as UAT,
           sum(case environment when 'D' then 1 else 0 end) as DEV
    from [Current]
    group by DateValue(opened)
    
     
  9. one_raven God is a Chinese Whisper Valued Senior Member

    Messages:
    13,406
    Thanks.

    I'll take a stab at it when I get back to work.
     
  10. Rick Valued Senior Member

    Messages:
    3,336
  11. one_raven God is a Chinese Whisper Valued Senior Member

    Messages:
    13,406
Thread Status:
Not open for further replies.

Share This Page