SQL how to convert row with date range to many rows with each date
Using some sample data...
create table data (begindate datetime, enddate datetime, data int);
insert data select
'20130101', '20130104', 7 union all select
'20130105', '20130106', 9;
The Query: (Note: if you already have a numbers/tally table - use it)
select dateadd(d,v.number,d.begindate) adate, data
from data d
join master..spt_values v on v.type='P'
and v.number between 0 and datediff(d, begindate, enddate)
order by adate;
Results:
| COLUMN_0 | DATA |
-----------------------------------------
| January, 01 2013 00:00:00+0000 | 7 |
| January, 02 2013 00:00:00+0000 | 7 |
| January, 03 2013 00:00:00+0000 | 7 |
| January, 04 2013 00:00:00+0000 | 7 |
| January, 05 2013 00:00:00+0000 | 9 |
| January, 06 2013 00:00:00+0000 | 9 |
Alternatively you can generate a number table on the fly (0-99) or as many numbers as you need
;WITH Numbers(number) AS (
select top(100) row_number() over (order by (select 0))-1
from sys.columns a
cross join sys.columns b
cross join sys.columns c
cross join sys.columns d
)
select dateadd(d,v.number,d.begindate) adate, data
from data d
join Numbers v on v.number between 0 and datediff(d, begindate, enddate)
order by adate;
SQL Fiddle Demo
SQL how to convert row with date range to many rows with date range with gaps based on a data column
I would like to give the credit to @Gordon Linoff whom one of the answer helpmed me with Gaps and islands problems which i am just sharing with you.
The reason I posted this as an answer due to the title which could be found in search results for this type of problems
I have done it using Oracle database and it should work with all standard sql database. dbfiddle for reference
SELECT t.key_id
,MIN(fromdate)
,MAX(todate)
FROM (SELECT t.*
,row_number() over(ORDER BY fromdate) AS startseq
,row_number() over(PARTITION BY t.key_id ORDER BY fromdate) AS endseq
FROM some_table t) t
GROUP BY t.key_id
,(startseq - endseq);
SQL Convert each date range into each day row
You can use the technique described here, in order to generate a date range for each interval of your table. Then simply group by Cname and date to get the desired result set:
;WITH natural AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 AS val
FROM sys.all_objects
)
SELECT m.Cname, d = DATEADD(DAY, natural.val, m.StartDate),
SUM(value) AS value
FROM mytable AS m
INNER JOIN natural ON natural.val <= DATEDIFF(DAY, m.StartDate, m.EndDate)
GROUP BY Cname, DATEADD(DAY, natural.val, m.StartDate)
ORDER BY Cname, d
The CTE is used to create a tally table. The numbers of this table are then used to add 1,2,3, ... days to StartDate until EndDate is reached.
If you group by Cname, [Date], then SUM will return the required value since it will add any overlapping records within each Cname partition.
SQL Fiddle Demo
Break into multiple rows based on date range of a single row
You can use a Calendar or dates table for this sort of thing.
For only 152kb in memory, you can have 30 years of dates in a table with this:
/* 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]);
Without taking the actual step of creating a table, you can use it inside a common table expression with just this:
declare @fromdate date = '20161229';
declare @thrudate date = '20170103';
;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 [Date]
from dates;
Use either like so:
select
t.AppointmentId
, StartDate = d.date
, EndDate = d.date
from dates d
inner join appointments t
on d.date >= t.StartDate
and d.date <= t.EndDate
rextester demo: http://rextester.com/TNWQ64342
returns:
+---------------+------------+------------+
| AppointmentId | StartDate | EndDate |
+---------------+------------+------------+
| 9 | 2017-04-12 | 2017-04-12 |
| 10 | 2017-05-01 | 2017-05-01 |
| 10 | 2017-05-02 | 2017-05-02 |
| 10 | 2017-05-03 | 2017-05-03 |
| 11 | 2017-06-01 | 2017-06-01 |
+---------------+------------+------------+
Number and Calendar table reference:
- Generate a set or sequence without loops - 1 - Aaron Bertrand
- Generate a set or sequence without loops - 2 - Aaron Bertrand
- Generate a set or sequence without loops - 3 - Aaron Bertrand
- The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden
- Creating a Date Table/Dimension in sql Server 2008 - David Stein
- Calendar Tables - Why You Need One - David Stein
- Creating a date dimension or calendar table in sql Server - Aaron Bertrand
- tsql Function to Determine Holidays in sql Server - Aaron Bertrand
- F_table_date - Michael Valentine Jones
Create a row for each date in a range, and add 1 for each day within a date range for a record in SQL
You can join your table to a calendar table containing all the dates you need:
with calendar as
(select cast('2022-01-01' as datetime) as d
union all select dateadd(day, 1, d)
from calendar
where d < '2022-02-01')
select d as "Date", count(*) as NumberOfCustomers
from calendar inner join table_name
on d between LiveDate and coalesce(ServiceEndDate, '9999-12-31')
group by d;
Fiddle
Split date range to multiple rows using SQL
CREATE TABLE #InputTABLE
(
startdate DATETIME,
enddate DATETIME,
other_columns varchar(20)
)
INSERT INTO #InputTABLE VALUES('1956-05-06','1960-04-05','myvalues');
SELECT * FROM #InputTABLE
Output:
startdate enddate other_columns
1956-05-06 00:00:00.000 1960-04-05 00:00:00.000 myvalues
Query:
CREATE TABLE #OutputTABLE
(
startdate DATETIME,
enddate DATETIME,
other_columns varchar(20)
)
DECLARE @cnt int
DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @incr int
DECLARE @tempDate datetime
SET @startDate=(Select startdate from #InputTABLE)
SET @endDate=(Select enddate from #InputTABLE)
SET @cnt=DATEDIFF(yy,@startDate,@endDate)
SET @incr=0
SET @tempDate=DATEADD(yy,@incr,Cast(@startDate As datetime))
WHILE @cnt>=0
BEGIN
IF @cnt = 0
BEGIN
INSERT INTO #OutputTABLE VALUES(@tempDate,@endDate,'myvalues');
END
ELSE
BEGIN
insert into #OutputTABLE values(@tempDate,DATEADD(yy, DATEDIFF(yy,0,@tempDate)+1, -1),'myvalues');
END
SET @tempDate=DATEADD(yy,@incr+1,DATEADD(yy,DATEDIFF(yy,0,@startDate),0))
SET @cnt=@cnt-1
SET @incr=@incr+1
END
Result : SELECT * FROM #OutputTABLE;
startdate enddate other_columns
1956-05-06 00:00:00.000 1956-12-31 00:00:00.000 myvalues
1957-01-01 00:00:00.000 1957-12-31 00:00:00.000 myvalues
1958-01-01 00:00:00.000 1958-12-31 00:00:00.000 myvalues
1959-01-01 00:00:00.000 1959-12-31 00:00:00.000 myvalues
1960-01-01 00:00:00.000 1960-04-05 00:00:00.000 myvalues
For each day between two dates, add a row with the same info but only that day in the start/end columns
May be you need a Recursive CTE.
CREATE TABLE #dates(NAME VARCHAR(50),START DATETIME,[END] DATETIME)
INSERT INTO #dates
VALUES ('Bob','2014-10-30','2014-11-02')
DECLARE @maxdate DATETIME = (SELECT Max([end]) FROM #dates);
WITH cte
AS (SELECT NAME,
START,
[END]
FROM #dates
UNION ALL
SELECT NAME,
Dateadd(day, 1, start),
Dateadd(day, 1, start)
FROM cte
WHERE start < @maxdate)
SELECT *
FROM cte
OUTPUT :
name START END
---- ---------- ----------
Bob 2014-10-30 2014-10-30
Bob 2014-10-31 2014-10-31
Bob 2014-11-01 2014-11-01
Bob 2014-11-02 2014-11-02
Related Topics
SQL Server , Split a Time Duration Row by 24 Hour Period
How to Check If a Value Is a Number in SQLite
Nolock VS. Transaction Isolation Level
Efficient Way to Convert Second to Minute and Seconds in SQL Server 2005
Fill Null Values with Last Non-Null Amount - Oracle SQL
Postgresql: Fill Null Values in Timeserie Query with Previous Value
How to Convert Int to Date in SQL Server 2008
SQL Server Bulk Insert CSV with Data Having Comma
SQL How to Convert Row with Date Range to Many Rows with Each Date
T-SQL - Group by with Like - Is This Possible
Select Random Row for Each Group
Pl/Sql: How to Prompt User Input in a Procedure
How to Use Regular Expression in SQL Server