Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT id, title,
- SUM(CASE WHEN day = 1 AND lunchStatus = 1 THEN datee ELSE 0 END) '1',
- SUM(CASE WHEN day = 2 AND lunchStatus = 1 THEN datee ELSE 0 END) '2',
- SUM(CASE WHEN day = 3 AND lunchStatus = 1 THEN datee ELSE 0 END) '3',
- SUM(CASE WHEN day = 4 AND lunchStatus = 1 THEN datee ELSE 0 END) '4',
- .
- .
- SUM(CASE WHEN day = 31 AND lunchStatus = 1 THEN datee ELSE 0 END) '31'
- FROM
- (
- SELECT m.id, m.title, l.month, l.day, l.lunchStatus, l.datee
- FROM `months` m
- LEFT OUTER JOIN
- (
- SELECT MONTHNAME(issuedDateTime) as month, DAY(issuedDateTime) as day, lunchStatus,
- userId, DATE(issuedDateTime) as datee
- FROM lunch_status
- WHERE userId = $query_user AND YEAR(issuedDateTime) = YEAR('$query_date')
- ) l
- ON m.title = l.month
- ) as s
- GROUP BY title
- ORDER BY id;
- title | 1 | 2 | 3 | 4 | ....... | 31
- --------------------------------------------------------
- Jan | 0 | 2018 | 0 | 2018 | ....... | 0
- Feb | 0 | 0 | 0 | 0 | ....... | 0
- Mar | 0 | 2018 | 0 | 2018 | ....... | 0
- .
- .
- .
- title | month | day | lunchStatus | datee
- -------------------------------------------------------------
- May | May | 31 | 0 | 2018-05-31
- May | May | 29 | 1 | 2018-05-29
- Jan | Jan | 31 | 0 | 2018-05-31
- May | May | 4 | 1 | 2018-01-04
- .
- .
Add Comment
Please, Sign In to add comment