Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- EmpNr | datefrom | dateto | hours
- 0815 | 01.01.2019 | 03.01.2019 | 15
- 0815 | 05.01.2019 | 15.01.2019 | 15
- 0815 | 20.01.2019 | 31.12.9999 | 40
- 0815 | 01.01.2019 | 15
- 0815 | 02.01.2019 | 15
- 0815 | 03.01.2019 | 15
- 0815 | 04.01.2019 | NULL
- 0815 | 05.01.2019 | 15
- ...
- 0815 | 15.01.2019 | 15
- 0815 | 16.01.2019 | NULL
- 0815 | 17.01.2019 | NULL
- 0815 | 18.01.2019 | NULL
- 0815 | 19.01.2019 | NULL
- 0815 | 20.01.2019 | 40
- 0815 | 21.01.2019 | 40
- ...
- 0815 | 31.01.2019 | 40
- declare @year int = 2019, @month int = 1;
- WITH numbers
- as
- (
- Select 1 as value
- UNion ALL
- Select value + 1 from numbers
- where value + 1 <= Day(EOMONTH(datefromparts(@year,@month,1)))
- )
- SELECT b.empnr, b.hours, datefromparts(@year,@month,numbers.value) Datum FROM numbers left outer join
- 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