SQL Server : splitting a row into several rows based off it's start and end date

Question

SQL Server : I have an absence table that holds one record per employee absence, it has the absence start and end date in it. e.g.

Person Ref    Start Date     End Date
---------------------------------------
1234          01/01/2021     05/01/2021

I want to split this row into several rows based upon the date range, so instead of one row representing the absence, it would have a row for each day of absence, like this:

Person Ref    Start Date   End Date
------------------------------------
1234          01/01/2021   01/01/2021
1234          02/01/2021   02/01/2021
1234          03/01/2021   03/01/2021
1234          04/01/2021   04/01/2021
1234          05/01/2021   05/01/2021

Is it possible to split the row based upon the date range into separate rows, so that each day of absence has it's own row? Thanks in advance.

It may be worth noting that this is source data, and would be transformed into a staging area.

UPDATE: I have another slight niggle, would you be able to help? In my current table there is also a column for how many shifts were lost in the absence along with person reference, start and end date. At the minute, splitting the record into several rows representing each day rather than a time frame means the column for number of shifts lost is duplicated. e.g. in the old table, if someone is off for 5 days and loses 3 shifts, in the new table there would be 3 rows - one for each day- and each day is showing as 3 shift loss. Is there a way to split this?

To try and explain this better the table used to look like this:

Person Ref    Start Date   End Date    Shifts Lost
--------------------------------------------------
1234          01/01/2021   05/01/2021  3    

Now it looks like this (due to splitting we have done)

Person Ref    Start Date   End Date    Shifts Lost
--------------------------------------------------
1234          01/01/2021   01/01/2021  3    
1234          02/01/2021   02/01/2021  3
1234          03/01/2021   03/01/2021  3
1234          04/01/2021   04/01/2021  3
1234          05/01/2021   05/01/2021  3

Is there a way to split the count of 3 shifts lost between rows? Due to shift patterns someone who is absent for 5 days won’t necessarily miss 5 shifts, in this case they miss 3. Is there a way to make it like:

Person Ref    Start Date   End Date    Shifts Lost
--------------------------------------------------
1234          01/01/2021   01/01/2021  1    
1234          02/01/2021   02/01/2021  1
1234          03/01/2021   03/01/2021  1
1234          04/01/2021   04/01/2021  0
1234          05/01/2021   05/01/2021  0

Thanks

Answer 1

If you don't have a Calendar Table (highly recommended), you can use an ad-hoc tally table in concert with a simple JOIN.

You may notice Top 1000, this can be adjusted to a more reasonable level.

Example or dbFiddle

Select [Person Ref]
      ,[Start Date] = dateadd(DAY,N,[Start Date])
      ,[End Date]   = dateadd(DAY,N,[Start Date])
 From  YourTable A
 Join  (
        Select Top 1000 N=-1+Row_Number() Over (Order By (Select Null))
         From  master..spt_values n1,master..spt_values n2
       ) B on N <= datediff(DAY,[Start Date],[End Date])

Returns

Person Ref  Start Date  End Date
1234        2021-01-01  2021-01-01
1234        2021-01-02  2021-01-02
1234        2021-01-03  2021-01-03
1234        2021-01-04  2021-01-04
1234        2021-01-05  2021-01-05

Update --- Shifts Lost

Select [Person Ref]
      ,[Start Date] = dateadd(DAY,N,[Start Date])
      ,[End Date]   = dateadd(DAY,N,[Start Date])
      ,[Shifts Lost]= case when N<[Shifts Lost] then 1 else 0 end
 From  YourTable A
 Join  (
        Select Top 10000 N=-1+Row_Number() Over (Order By (Select Null))
         From  master..spt_values n1,master..spt_values n2
       ) B on N <= datediff(DAY,[Start Date],[End Date])

Results

Person Ref  Start Date  End Date    Shifts Lost
1234        2021-01-01  2021-01-01  1
1234        2021-01-02  2021-01-02  1
1234        2021-01-03  2021-01-03  1
1234        2021-01-04  2021-01-04  0
1234        2021-01-05  2021-01-05  0