SQL Server - Duplicate rows and add date iterator column between two date values

Question

I have a table, with types varchar, varchar, date, and date:

NAME | ID   | FROM       | THRU
Bob  | A123 | 10/30/2010 | 11/2/2010
Bob  | B567 | 10/30/2010 | 11/2/2010

I want to add a Date of Service (DOS) column that duplicates the rows and iterates for each day between, and including, the FROM and THRU dates. The finished table should look like this:

NAME | ID   | FROM       | THRU       | DOS
Bob  | A123 | 10/30/2010 | 11/02/2010 | 10/30/2010
Bob  | A123 | 10/30/2010 | 11/02/2010 | 10/31/2010
Bob  | A123 | 10/30/2010 | 11/02/2010 | 11/01/2010
Bob  | A123 | 10/30/2010 | 11/02/2010 | 11/02/2010
Bob  | B567 | 10/30/2010 | 11/02/2010 | 10/30/2010
Bob  | B567 | 10/30/2010 | 11/02/2010 | 10/31/2010
Bob  | B567 | 10/30/2010 | 11/02/2010 | 11/01/2010
Bob  | B567 | 10/30/2010 | 11/02/2010 | 11/02/2010

I saw another answer that used cte but did not retain the original date values and add a DOS column. How could I accomplish this in SQL Server?

Answer 1

I think a calendar table is not quite the right tool here. Since you want sequential dates a tally table seems like a good way to go.

First let's setup your data.

declare @Something table
(
    NAME varchar(10)
    , ID varchar(10)
    , DateFrom date
    , THRU date
)

insert @Something values
('Bob', 'A123', '20101030', '20101102')
, ('Bob', 'B567', '20101030', '20101102')

Next we need our tally table. I keep one as a view on my system and it is blistering fast with zero reads. Feel free to adjust the row count to suit your needs.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Now the query for your situation is pretty simple.

select s.Name
    , s.ID
    , s.DateFrom
    , s.THRU
    , DOS = DATEADD(day, t.N - 1, DateFrom)
from @Something s
join cteTally t on t.N <= datediff(day, DateFrom, THRU) + 1
order by s.Name
    , s.ID
    , t.N