Advertisement
Guest User

Untitled

a guest
Jun 24th, 2019
92
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.93 KB | None | 0 0
  1. EmpNr | datefrom | dateto | hours
  2. 0815 | 01.01.2019 | 03.01.2019 | 15
  3. 0815 | 05.01.2019 | 15.01.2019 | 15
  4. 0815 | 20.01.2019 | 31.12.9999 | 40
  5.  
  6. 0815 | 01.01.2019 | 15
  7. 0815 | 02.01.2019 | 15
  8. 0815 | 03.01.2019 | 15
  9. 0815 | 04.01.2019 | NULL
  10. 0815 | 05.01.2019 | 15
  11. ...
  12. 0815 | 15.01.2019 | 15
  13. 0815 | 16.01.2019 | NULL
  14. 0815 | 17.01.2019 | NULL
  15. 0815 | 18.01.2019 | NULL
  16. 0815 | 19.01.2019 | NULL
  17. 0815 | 20.01.2019 | 40
  18. 0815 | 21.01.2019 | 40
  19. ...
  20. 0815 | 31.01.2019 | 40
  21.  
  22. declare @year int = 2019, @month int = 1;
  23. WITH numbers
  24. as
  25. (
  26. Select 1 as value
  27. UNion ALL
  28. Select value + 1 from numbers
  29. where value + 1 <= Day(EOMONTH(datefromparts(@year,@month,1)))
  30. )
  31. SELECT b.empnr, b.hours, datefromparts(@year,@month,numbers.value) Datum FROM numbers left outer join
  32. emptbl b on b.empnr = '0815' and (datefromparts(@year,@month,numbers.value) >= b.datefrom and datefromparts(@year,@month,numbers.value) <= case b.dateto )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement