1

I have a table named CN in SQL Server 2005:

ID    Startdate                 Enddate
1417  2015-09-30 09:45:00.000   2015-10-10 09:45:00.000
973   2015-09-14 11:03:00.000   2015-09-24 11:03:00.000

I need a query which give me the following result

 ID         Dates
 1417       2015-09-30
 1417       2015-10-01
 1417       2015-10-02
 1417       2015-10-03
 1417       2015-10-04
 1417       2015-10-05
 1417       2015-10-06
 1417       2015-10-07
 1417       2015-10-08
 1417       2015-10-09
 1417       2015-10-10
 973        2015-09-14
 973        2015-09-15
 973        2015-09-16
 973        2015-09-17
 973        2015-09-18
 973        2015-09-19
 973        2015-09-20
 973        2015-09-21
 973        2015-09-22
 973        2015-09-23
 973        2015-09-24

That is the id and the list of dates from startdate to enddate including startdate and enddate in the result.

1

2 Answers 2

4

You can use a numbers table for this. Here's a very simple way to create one that will support 5 year+ ranges:

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY);

INSERT dbo.Numbers SELECT ROW_NUMBER() OVER (ORDER BY name) 
FROM sys.all_objects;

Now, given the sample data:

CREATE TABLE #CN(ID INT, StartDate SMALLDATETIME, EndDate SMALLDATETIME);

INSERT #CN(ID, StartDate, EndDate)
SELECT           1417,'2015-09-30 09:45','2015-10-10 09:45'
UNION ALL SELECT 973 ,'2015-09-14 11:03','2015-09-24 11:03';

We can join against the numbers table like this in order to produce the result you're looking for:

;WITH cn AS 
(
  SELECT ID,
         s = DATEADD(DAY, 0, DATEDIFF(DAY, '19000101', StartDate)),
         e = DATEADD(DAY, 0, DATEDIFF(DAY, '19000101', EndDate))
  FROM #CN
)
SELECT cn.ID, DATEADD(DAY, n.Number-1, cn.s)
  FROM cn
  INNER JOIN dbo.Numbers AS n
  ON n.Number <= DATEDIFF(DAY, cn.s, cn.e) + 1;

Clean up your #temp table:

DROP TABLE #CN;
3
  • I can't remember... Can we use multiple CTE with 2005? => with C1 AS(...), C2(...)... I only find it with 2008 on MSDN... Feb 17, 2016 at 18:50
  • @Julien Yes, you can, but why not just store a Numbers table instead of dynamically generating one every time? Feb 17, 2016 at 18:55
  • I usually use it for temp query, hence the dynamic table. for long term solution, I guess it is probably better to store it with enough numbers... I will remove my answer. I doesn't add much to yours. Feb 17, 2016 at 18:57
1

As per https://stackoverflow.com/q/1378593/2451726

...

Try something like this:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

You then use:

SELECT *
FROM dbo.ExplodeDates('20090401','20090531') as d;

Or:

SELECT * FROM dbo.CN CROSS APPLY dbo.ExplodeDates(CN.startdate, CN.enddate) AS d;

Please note... if you already have a sufficiently large nums table then you should use:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

And you can create such a table using:

CREATE TABLE dbo.nums (num int PRIMARY KEY);
INSERT dbo.nums values (1);
GO
INSERT dbo.nums SELECT num + (SELECT COUNT(*) FROM nums) FROM nums
GO 20

These lines will create a table of numbers containing 1M rows... and far quicker than inserting them one by one.

You should NOT create your ExplodeDates function using a function that involves BEGIN and END, as the Query Optimizer becomes unable to simplify the query at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.