Guest User

Untitled

a guest
Jun 20th, 2018
105
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.51 KB | None | 0 0
  1. SELECT id, title,
  2. SUM(CASE WHEN day = 1 AND lunchStatus = 1 THEN datee ELSE 0 END) '1',
  3. SUM(CASE WHEN day = 2 AND lunchStatus = 1 THEN datee ELSE 0 END) '2',
  4. SUM(CASE WHEN day = 3 AND lunchStatus = 1 THEN datee ELSE 0 END) '3',
  5. SUM(CASE WHEN day = 4 AND lunchStatus = 1 THEN datee ELSE 0 END) '4',
  6. .
  7. .
  8. SUM(CASE WHEN day = 31 AND lunchStatus = 1 THEN datee ELSE 0 END) '31'
  9. FROM
  10. (
  11. SELECT m.id, m.title, l.month, l.day, l.lunchStatus, l.datee
  12. FROM `months` m
  13. LEFT OUTER JOIN
  14. (
  15. SELECT MONTHNAME(issuedDateTime) as month, DAY(issuedDateTime) as day, lunchStatus,
  16. userId, DATE(issuedDateTime) as datee
  17. FROM lunch_status
  18. WHERE userId = $query_user AND YEAR(issuedDateTime) = YEAR('$query_date')
  19. ) l
  20. ON m.title = l.month
  21. ) as s
  22. GROUP BY title
  23. ORDER BY id;
  24.  
  25. title | 1 | 2 | 3 | 4 | ....... | 31
  26. --------------------------------------------------------
  27. Jan | 0 | 2018 | 0 | 2018 | ....... | 0
  28. Feb | 0 | 0 | 0 | 0 | ....... | 0
  29. Mar | 0 | 2018 | 0 | 2018 | ....... | 0
  30. .
  31. .
  32. .
  33.  
  34. title | month | day | lunchStatus | datee
  35. -------------------------------------------------------------
  36. May | May | 31 | 0 | 2018-05-31
  37. May | May | 29 | 1 | 2018-05-29
  38. Jan | Jan | 31 | 0 | 2018-05-31
  39. May | May | 4 | 1 | 2018-01-04
  40. .
  41. .
Add Comment
Please, Sign In to add comment