How do I transform the data in this SQL Table into a specific format?

Question

I am using SQL Server 2012 and I have a table called T1 (extract below) which contains around 100,000 records.

Property    Room    Season   Datefrom    Dateto      Name
ABC LTD     DLX       P     01-01-2018  01-03-2018   John
XYZ LTD     SUP       P     01-01-2018  01-04-2018   Alan

I need a SQL query that will transform the data from this table T1 and give me the following output:

Property    Room    Season     Date       Name
ABC LTD     DLX        P    01-01-2018    John
ABC LTD     DLX        P    01-02-2018    John
ABC LTD     DLX        P    01-03-2018    John
XYZ LTD     SUP        P    01-01-2018    Alan
XYZ LTD     SUP        P    01-02-2018    Alan
XYZ LTD     SUP        P    01-03-2018    Alan
XYZ LTD     SUP        P    01-04-2018    Alan

How can I do this with a T-SQL query?

Use a Calendar Table (for example [Bones of SQL - The Calendar Table](http://www.sqlservercentral.com/articles/calendar/145206/)) and then `JOIN` onto it using your `DateTo` and `DateFrom` and `BETWEEN`. — Thom A, Feb 21 '18 at 12:52
Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. Things like _explaining_ what you want to accomplish and what you've tried are helpful. — HABO, Feb 21 '18 at 14:14

score 2 · Answer 1 · answered Feb 21 '18 at 12:58

2

Another option is a CROSS APPLY in concert with an ad-hoc Tally Table

Example

Declare @YourTable Table ([Property] varchar(50),[Room] varchar(50),[Season] varchar(50),[Datefrom] varchar(50),[Dateto] date,[Name] varchar(50))
Insert Into @YourTable Values 
 ('ABC LTD','DLX','P','01-01-2018','01-03-2018','John')
,('XYZ LTD','SUP','P','01-01-2018','01-04-2018','Alan')


Select Property
      ,Room
      ,Season
      ,Date = B.D
      ,Name
 from @YourTable A
 Cross Apply ( 
                Select Top (DateDiff(DAY,Datefrom,IsNull([DateTo],DateFrom))+1) 
                       D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),DateFrom) 
                 From  master..spt_values n1  --,master..spt_values << remove comment of span can be > 6 years 
             ) B

Returns

answered Feb 21 '18 at 12:58

John Cappelletti

79,615
7
44
66

John, this was timely and I really liked it, I just used it to help another op over on https://stackoverflow.com/questions/48908324/how-can-i-compare-two-tables-without-relationship-1-table-with-40k-records-and/48909999#48909999 – Random_User Feb 21 '18 at 16:40
I did up vote, but it appears the op on this thread went with the tally table and the case statement answer. – Random_User Feb 21 '18 at 16:50
@Random_User Sorry I had it in my little head that it was your question – John Cappelletti Feb 21 '18 at 16:52

score 1 · Accepted Answer · answered Feb 21 '18 at 14:24

A tally table or a calendar table is the way to solve this. Recursive solutions are usually slower and can cause other errors. This answer is a lot like the answer from @JohnCappelletti, I included a more reliable tally table and rejection of invalid intervals.

DECLARE @YourTable TABLE
(
  [Property] varchar(50),
  [Room] varchar(50),
  [Season] char(1),
  [Datefrom] date,
  [Dateto] date,
  [Name] varchar(50)
)
INSERT @YourTable VALUES
 ('ABC LTD','DLX','P','01-01-2018','01-03-2018','John')
,('XYZ LTD','SUP','P','01-01-2018','01-04-2018','Alan')

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT 1 FROM N,N a,N b,N c,N d)
SELECT
  Property,
  Room,
  Season,
  b.Date,
  Name
FROM @YourTable a
CROSS APPLY
( 
  SELECT top(datediff(d,Datefrom,case when DateTo >= DateFrom
             then dateadd(d, 1, DateTo) else DateFrom end))
    DATEADD(d,row_number()over(order by 1/0)-1, DateFrom) Date
  FROM tally
) b

If dateto is before datefrom, the row will not be included.

Excellent! Transformed around 115,000 rows into 2,830,000 rows in 33 seconds! — user3115933, Feb 22 '18 at 07:17

score 0 · Answer 3 · edited Feb 21 '18 at 12:52

0

If there are not very many days, then this is a good opportunity to learn about recursive CTEs:

with cte as (
      select Property, Room, Season, datefrom as dte, Name, dateto
      from t
      union all
      select Property, Room, Season, dateadd(day, 1, dte), Name, dateto
      from cte 
      where dte < dateto
     )
select *
from cte;

By default this will work for up to 100 days. You can use option (maxrecursion 0) so it works for any number.

For performance, a numbers table is faster, but recursive CTEs have surprisingly reasonable performance under many circumstances.

edited Feb 21 '18 at 12:52

Yogesh Sharma

49,870
5
26
52

answered Feb 21 '18 at 12:51

Gordon Linoff

1,242,037
58
646
786

Thanks. By 'work for up to 100 days', do you mean that the difference between the Datefrom and Dateto for each record should not exceed 100 days? – user3115933 Feb 21 '18 at 12:57
1

@user3115933 . . . Yes, unless you use the max recursion option. – Gordon Linoff Feb 21 '18 at 13:24

score 0 · Answer 4 · answered Feb 21 '18 at 12:55

Try using Recursive CTE

DECLARE @T TABLE
(
    Property    NVARCHAR(255),
    Room    NVARCHAR(255),
    Season   NVARCHAR(255),
    Datefrom    DATE,
    Dateto      DATE,
    Name NVARCHAR(255)
)

INSERT INTO @T
VALUES('ABC LTD','DLX','P','01-01-2018','01-03-2018','John'),
('XYZ LTD','SUP','P','01-01-2018','01-05-2018','Alan')

;WITH CTE
AS
(
    SELECT
       Property,
       Room,
       Season,
       MyDate = Datefrom,
       Name,
       Dateto
       FROM @T

    UNION ALL

    SELECT
       Property,
       Room,
       Season,
       MyDate = DATEADD(D,1,MyDate),
       Name,
       Dateto
       FROM CTE
          WHERE MyDate < Dateto

)
SELECT
    *
    FROM CTE
    ORDER BY Property
    OPTION(MAXRECURSION 0)

score 0 · Answer 5 · answered Feb 21 '18 at 13:22

Same or similar to other answers. I think Gordon was first. I am practicing with recursive CTE.

declare @t table (Property varchar(20), Room varchar(20), Season varchar(20), Datefrom datetime, Dateto datetime, Name varchar(20));
insert into @t values 
       ('ABC LTD','DLX','P','01-01-2018','01-03-2018','John')
     , ('XYZ LTD','SUP','P','01-01-2018','01-05-2018','Alan');

with cte as 
( select Property, Room, Season, Datefrom as [Date], Dateto, Name
  from @t 
  union all
  select Property, Room, Season, DATEADD(day, 1, [date]), Dateto, Name 
  from cte 
  where [date] < Dateto
)

select Property, Room, Season, casT([Date] as date) as [Date], Name 
from cte 
order by property, [date];

paparazzo · Answer 6 · 2018-02-21T13:49:49.327

Using a date range table.

declare @t table (Property varchar(20), Room varchar(20), Season varchar(20), DateFrom date, DateTo date, Name varchar(20));
insert into @t values 
       ('ABC LTD','DLX','P','01-01-2018','01-03-2018','John')
     , ('XYZ LTD','SUP','P','01-01-2018','01-05-2018','Alan');

declare @dateRange table (dt date primary key);
declare @minDate date = (select min(datefrom) from @t);
declare @maxDate date = (select max(dateto)   from @t);
with dates as 
(
     select @minDate as dt 
     union all 
     select DATEADD(day, 1, dt) 
     from dates 
     where dt < @maxDate
)
insert into @dateRange 
select dt from dates option (maxrecursion 10000);

select t.Property, t.Room, t.Season, d.dt as [Date], t.Name
  from @t t 
  join @dateRange d
    on d.dt >= t.DateFrom 
   and d.dt <= t.DateTo
 order by t.Property, t.Room, d.dt;

How do I transform the data in this SQL Table into a specific format?

6 Answers6