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:
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
(StoreNum = 123)
AND (Date(TransDt) >= '2006-12-04')
AND (Date(TransDt) <= '2006-12-17')
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.
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)
LEFT JOIN (
SELECT 'Mon' As TempDay
SELECT 'Tue' As TempDay
SELECT 'Wed' As TempDay
SELECT 'Thu' As TempDay
SELECT 'Fri' As TempDay
SELECT 'Sat' As TempDay
SELECT 'Sun' As TempDay
) AS tblTempDay
ON DATE_FORMAT(TransDt, '%a') = tblTempDay.TempDay
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, firstname.lastname@example.org 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)