All geek questions in one place

How to output rows from an SQL table as extended on a daily basis using start and end dates?

I use SQL Server 2012, and I have a table with the name StayInfo.

It has the following structure (extract):

Name    ArrDate        DepDate       ID
A       2016-03-29     2016-04-02    100
B       2016-05-10     2016-05-12    250

I want to get the following result from a T-SQL query:

Name    Date          ID
A       2016-03-29   100
A       2016-03-30   100 
A       2016-03-31   100
A       2016-04-01   100
A       2016-04-03   100
B       2016-05-10   250
B       2016-05-11   250
B       2016-05-12   250

The main difficulty I came across is the SQL code needed to do the split on a daily basis.

+4
source share
3 answers

Another option is a table table with Cross Apply agreement.

Example

Select A.Name
      ,B.Date
      ,A.ID
 From  YourTable A
 Cross Apply (
                Select Top (DateDiff(DAY,A.[ArrDate],A.[DepDate])+1) Date=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),A.[ArrDate])  
                From  master..spt_values n1,master..spt_values n2
                ) B

Returns

Name    Date        ID
A       2016-03-29  100
A       2016-03-30  100
A       2016-03-31  100
A       2016-04-01  100
A       2016-04-02  100
B       2016-05-10  250
B       2016-05-11  250
B       2016-05-12  250
+3
source

You can use a table of recursive CTEs or numbers:

with cte as (
      select Name, ArrDate, DepDate, ID
      from t
      union all
      select Name, dateadd(day, 1, ArrDate), DepDate, ID
      from cte
      where ArrDate < DepDate
     )
select Name, ArrDate, ID
from cte;

You can set the maximum recursion parameter if you have intervals of more than 100 days.

+6

.

adhoc , :

declare @fromdate date = '20160101'
declare @thrudate date = '20171231'
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                 cross join n as tenK cross join n as hundredK
   order by [Date]
)
select t.Name, d.Date, t.Id
from t
  inner join dates d
    on d.date >= t.arrdate
   and d.date <= t.depdate

: http://rextester.com/WCM12325

:

+------+------------+-----+
| Name |    Date    | Id  |
+------+------------+-----+
| A    | 2016-03-29 | 100 |
| A    | 2016-03-30 | 100 |
| A    | 2016-03-31 | 100 |
| A    | 2016-04-01 | 100 |
| A    | 2016-04-02 | 100 |
| B    | 2016-05-10 | 250 |
| B    | 2016-05-11 | 250 |
| B    | 2016-05-12 | 250 |
+------+------------+-----+

152 30- :

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK  cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date on dbo.Dates([Date]);
/* query */
select t.Name, d.Date, t.Id
from t
  inner join dates d
    on d.date >= t.arrdate
   and d.date <= t.depdate

: http://rextester.com/WCM12325

:

+------+------------+-----+
| Name |    Date    | Id  |
+------+------------+-----+
| A    | 2016-03-29 | 100 |
| A    | 2016-03-30 | 100 |
| A    | 2016-03-31 | 100 |
| A    | 2016-04-01 | 100 |
| A    | 2016-04-02 | 100 |
| B    | 2016-05-10 | 250 |
| B    | 2016-05-11 | 250 |
| B    | 2016-05-12 | 250 |
+------+------------+-----+

:

+3

Source: https://habr.com/ru/post/1685956/

More articles:


All Articles