Generate Date Ranges in MYSQL and UNION

Question

I'm trying to generate a report base on pre defined dates(cutoff dates). I have two tables, the in and out and overtime table.

IN and OUT table

in and out table

Overtime Table

overtime table

Expected Result

enter image description here

Using UNION, it doubles the dates, but that can be solve supposedly by group by, but group seems not to work in UNION.

And the tricky part is to show the BLANK 6-Jan and 5-Jan. I need to show this coz its a timesheet report.

Any help or clue will be greatly appreciated

CODE:

SELECT emp_id,in,out,null 
FROM normal 
WHERE date >= '1-Jan' 
  AND date <= '10-Jan' 
  AND emp_id = '10001'
UNION
SELECT emp_id,null,null,minutes 
FROM overtime 
WHERE date >= '1-Jan' 
  AND date <= '10-Jan' 
  AND emp_id = '10001'

See here http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-eithe ? It's a MySQL/perl answer but the principle is the same. — liquorvicar, May 27 '12 at 09:21
thanks the link helped me a lot with my other questions in mind :) — genpet, May 27 '12 at 14:51

aleroot · Accepted Answer · 2012-05-27T09:34:31.140

Something like this should do what you want(if i weel understood the question) :

SELECT n.date, n.emp_id, n.in, n.out, o.minutes
FROM normal n 
JOIN overtime o USING(emp_id)
WHERE n.date BETWEEN '2012-01-01' AND '2012-01-10'

Keep in mind that if you want to show even the dates with no matches, you have to create a table with the list of this dates :

dates

+------------+
| timestamp  |
|------------+
| 2012-01-01 |
| 2012-01-02 |
| ....       |

Then you can change the query in this way to get the result set you want :

SELECT d.timestamp AS date, b.emp_id, b.in, b.out, b.minutes
FROM dates d
LEFT JOIN 
(SELECT n.date AS timestamp, n.emp_id AS emp_id, n.in AS in, n.out AS out, o.minutes AS minutes
 FROM normal n 
 JOIN overtime o USING(emp_id)
 ) b 
 USING(timestamp)
 WHERE d.timestamp BETWEEN '2012-01-01' AND '2012-01-10'

If you want to avoid the dates table you can try with this more complex query, should give you the same result, without the needs of a third table that store the range of dates :

SELECT d.timestamp AS date, b.emp_id, b.in, b.out, b.minutes
FROM ( 
SELECT '2012-01-01' + INTERVAL a + b DAY AS timestamp
FROM
(SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9 ) d,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20 
UNION SELECT 30 UNION SELECT 40) m
WHERE '2012-01-01' + INTERVAL a + b DAY  <  '2012-01-11'
ORDER BY a + b
) d
LEFT JOIN 
(SELECT n.date AS timestamp, n.emp_id AS emp_id, n.in AS in, n.out AS out, o.minutes AS minutes
 FROM normal n 
 JOIN overtime o USING(emp_id)
 ) b 
 USING(timestamp)

Generate Date Ranges in MYSQL and UNION

1 Answers1

dates