Adding "Missing" Days

clbardwe@gpi.com · #1 (**permalink**) 01-25-07, 15:47

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)

Lexiflex · #2 (**permalink**) 01-25-07, 16:02

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.

clbardwe@gpi.com · #3 (**permalink**) 01-25-07, 16:21

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

r937 · #4 (**permalink**) 01-25-07, 16:58

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

Lexiflex · #5 (**permalink**) 01-26-07, 14:50

@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

Yveau01 · #6 (**permalink**) 02-04-07, 16:37

@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 ...

r937 · #7 (**permalink**) 02-04-07, 16:42

Quote:

Originally Posted by Yveau01

and make a union with some kind of dummy record.

????????

Internet Services

Web Development

Digital Marketing

Consumer Tech