Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007
    Posts
    6

    Unanswered: Count of Invoices for Each Hour

    This is actually a MySQL query, but I think it's generic enough that any SQL flavour should help.

    I need to get the number of invoices per hour between 7am to 9pm and "the rest" (for each day of a week). I thought I knew what I was doing, but I'm getting the total transactions for the day placed in an hour's field (and not any particular hour, that I can tell)

    Here's my query:

    Code:
    SELECT 
         DAYNAME(TransDt) As 'Day'
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 7 THEN COUNT(InvNum) ELSE 0 END) as NumTrans7
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 8 THEN COUNT(InvNum) ELSE 0 END) as NumTrans8
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 9 THEN COUNT(InvNum) ELSE 0 END) as NumTrans9
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 10 THEN COUNT(InvNum) ELSE 0 END) as NumTrans10
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 11 THEN COUNT(InvNum) ELSE 0 END) as NumTrans11
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 12 THEN COUNT(InvNum) ELSE 0 END) as NumTrans12
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 13 THEN COUNT(InvNum) ELSE 0 END) as NumTrans13
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 14 THEN COUNT(InvNum) ELSE 0 END) as NumTrans14
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 15 THEN COUNT(InvNum) ELSE 0 END) as NumTrans15
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 16 THEN COUNT(InvNum) ELSE 0 END) as NumTrans16
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 17 THEN COUNT(InvNum) ELSE 0 END) as NumTrans17
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 18 THEN COUNT(InvNum) ELSE 0 END) as NumTrans18
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 19 THEN COUNT(InvNum) ELSE 0 END) as NumTrans19
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 20 THEN COUNT(InvNum) ELSE 0 END) as NumTrans20
         ,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 21 THEN COUNT(InvNum) ELSE 0 END) as NumTrans21
         ,(CASE WHEN (DATE_FORMAT(TransDt, '%H') < 7) OR (DATE_FORMAT(TransDt, '%H') > 21) THEN COUNT(InvNum) ELSE 0 END) as NumTransOther
         ,COUNT(InvNum) AS TotalTrans
    FROM 
        tblTransactions
    WHERE 
        (StoreNum = 123)
        and (TransDt >= '2006-12-04 01:00:00') 
        and (TransDt <= '2006-12-11 00:59:59')
    group by 
         DAYNAME(TransDt)
    ORDER BY
         TransDt;
    What did I do wrong?

    TIA

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Try SUM() instead of COUNT():
    Code:
    SELECT 
         DAYNAME(TransDt) As 'Day'
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = 7 THEN 1 ELSE 0 END) as NumTrans7
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = 8 THEN 1 ELSE 0 END) as NumTrans8
         ,...etc...

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2007
    Posts
    6
    That was it. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •