Re-create rows between dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Re-create rows between dates

Author

Topic

parody
Posting Yak Master

111 Posts

Posted - 2011-12-05 : 07:09:31

Hi

I am investigating various methods and performance of this, and wonder if anyone can suggest and nuggets of wisdom I havent yet considered.

So for a table thus:
Key Data FromDate ToDate
1 A 20111201 20110101
1 B 20111202 NULL
2 A 20111205 NULL
3 A 20111201 20111202
3 B 20111203 20111204
3 C 20111205 NULL

The result, from a query that can be used in the definition of a view, should be:
Key Data FromDate ToDate Date
1 A 20111201 20111201 20111201
1 B 20111202 NULL 20111202
1 B 20111202 NULL 20111203
1 B 20111202 NULL 20111204
1 B 20111202 NULL 20111205
2 A 20111201 NULL 20111201
2 A 20111201 NULL 20111202
2 A 20111201 NULL 20111203
2 A 20111201 NULL 20111204
2 A 20111201 NULL 20111205
3 A 20111201 20111202 20111201
3 A 20111201 20111202 20111202
3 B 20111203 20111204 20111203
3 B 20111203 20111204 20111204
3 C 20111205 NULL 20111205

So, create a row for every date between each rows from and to dates. There is the possibility of some preprocessing if it is worthwhile.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 07:16:06

[code]
SELECT t.*,DATEADD(dd,v.number,FromDate) AS [Date]
FROM yourtable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND (DATEADD(dd,v.number,FromDate) BETWEEN FromDate AND ToDate
OR (DATEADD(dd,v.number,FromDate) BETWEEN FromDate AND GETDATE() AND ToDate IS NULL))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

parody
Posting Yak Master

111 Posts

Posted - 2011-12-05 : 07:26:03

OK, similar to one I had:

FROM mytable mt
INNER JOIN mylookup lk
ON lk.Date BETWEEN mt.FromDate AND isnull(mt.EndDate,convert(date,getdate())

but will look at the dateadd option thanks. Any more?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 07:30:56

quote:
Originally posted by parody

OK, similar to one I had:

FROM mytable mt
INNER JOIN mylookup lk
ON lk.Date BETWEEN mt.FromDate AND isnull(mt.EndDate,convert(date,getdate())

but will look at the dateadd option thanks. Any more?!

what does that mean?
isnt given suggestion enough for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

parody
Posting Yak Master

111 Posts

Posted - 2011-12-05 : 07:32:50

It's great, thanks.

I just meant does anyone else have any alternatives, more than one way to skin a cat and all that...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 07:42:58

you can use CTE method also if you're on sql 2005 or above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

parody
Posting Yak Master

111 Posts

Posted - 2011-12-05 : 07:46:40

Unfortunately the dateadd method does not have favourable performance compared to the existing methods.

Yes I used a CTE to start with for lookup, but decided to store in permanent table as it is static and doesnt need to be recalculated on every execution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 11:35:31

quote:
Originally posted by parody

Unfortunately the dateadd method does not have favourable performance compared to the existing methods.

Yes I used a CTE to start with for lookup, but decided to store in permanent table as it is static and doesnt need to be recalculated on every execution.

can you tell which existing methods you're comparing it to?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources