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

    Unanswered: Adding "Missing" Days

    I have a table which contains records of transactions. And I have a query which retrieves those transactions out and shows the daily totals. There should always be seven rows of data displayed.

    Problem. If there are transactions for each weekday in a period, the data will display correctly. However if, during the selected period, a particular weekday does not have any transactions, or a period of less than a week is selected, fewer than seven rows of daya will be displayed.

    For example, I select a range of dates from, say, 2006-12-04 to 2006-12-17 (two full weeks). For whatever reason no transactions were made on 2006-12-06 or 2006-12-13 (both Wednesdays of the range) the data will display six records omitting Wednesday.

    As another example, I select a range from 2006-12-04 to 2006-12-09 (Monday through Saturday). The display shows six records but omits Sunday.

    How do I get the query to display all seven days, beginning with Monday and ending with Sunday, whether or not there is a transaction for each weekday?

    Oh, yeah, my existing query:

    Code:
    SELECT 
         DAYNAME(TransDt) AS 'Day'
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = '07' THEN 1 ELSE 0 END) AS Hour7
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = '08' THEN 1 ELSE 0 END) AS Hour8
        #DATE_FORMAT's '09' through '19' trimmed out for length
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = '20' THEN 1 ELSE 0 END) AS Hour20
         ,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = '21' THEN 1 ELSE 0 END) AS Hour21
         ,SUM(CASE WHEN (DATE_FORMAT(TransDt, '%H') < '07') OR (DATE_FORMAT(TransDt, '%H') > '21') THEN 1 ELSE 0 END) AS HourOther
         ,COUNT(InvNum) AS DayTotal
        ,1 AS Position1
    FROM 
        tblTransactions
    WHERE 
        (StoreNum = 123)
        AND (Date(TransDt) >= '2006-12-04') 
        AND (Date(TransDt) <= '2006-12-17')
    GROUP BY 
         DAYNAME(TransDt)

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    I don't know if MySQL has any nifty functions to solve this but the simplest way is to create a table with all relevant dates (from the earliest date you need to maybe five years ahead should suffice for your applications life cycle) in it and join this table in your query.

    The data for this "dates" table is easily generated with a little loop that each pass adds a day to the table.

  3. #3
    Join Date
    Jan 2007
    Posts
    6
    I can't create a table, but I was playing around and came up with this:
    (It doesn't work, but I'm hoping I'm on the right track)
    Code:
     
            LEFT JOIN (
                SELECT 'Mon' As TempDay
                UNION
                SELECT 'Tue' As TempDay
                UNION
                SELECT 'Wed' As TempDay
                UNION
                SELECT 'Thu' As TempDay
                UNION
                SELECT 'Fri' As TempDay
                UNION
                SELECT 'Sat' As TempDay
                UNION
                SELECT 'Sun' As TempDay
                            ) AS tblTempDay
                    ON DATE_FORMAT(TransDt, '%a') = tblTempDay.TempDay

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it doesn't work because you've got the tables in the wrong order for the LEFT OUTER JOIN

    you want all rows from tblTempDay, with matching rows from tblTransactions if any

    it should work if you just change LEFT to RIGHT

    however, i never write RIGHT OUTER JOIN, i always switch them over and say LEFT OUTER JOIN -- it's just easier to understand that way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    @clbardwe@gpi.com
    I see now you didn't mention the actual date in the result, missed that
    With r937's rewrite it should work fine. Good thinking!

    Quote Originally Posted by r937
    it's just easier to understand that way
    It's all in the mind but I agree! I'm always driving people crazy by reformatting queries before I even try to understand them

  6. #6
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    @Lexiflex:
    Nasty habbit, keep doing this and you might end up working for an insurance company Anyway, remember the trick we pulled on one of the IPO systems ? Something like that might work here as well ...

    So to the rest of the world, clbardwe@gpi.com in particular, you might want to try this:
    - Examine the date entered. If not Monday, change the date to the most recent Monday prior to the date. So the entered date turns up in the week showed, even though it's not the Monday.
    - Select the distinc dates from the week starting with the Monday from the first itemlist. If you end up with 7, everything will be fine, if not, prepare to do a correction after the selection ...
    - Select all transactions for the week.
    - If a correction is needed (because less than seven days in the select) loop through the resultset, looking for the missing dates and make a union with some kind of dummy record.

    ... just a thought.
    ... guess a stored procedure is required, rather than a simple select statement ...

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Yveau01
    and make a union with some kind of dummy record.
    ????????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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