Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I create intervals fro start-dates only Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 2:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 7:30 AM
Points: 37, Visits: 220
Let's assume we have

1) A key value, like a product number
2) and a ValidFrom date, like this

MyProductKey, 20100111
MyProductKey, 20110205
MyProductKey, 20120101

What we want is the following

MyProductKey, 20100111, 20110204
MyProductKey, 20110205, 20111231
MyProductKey, 20120101, NULL

What would be the simplest way to do this?

TIA

Peter



Post #1432337
Posted Monday, March 18, 2013 2:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
Can you post ddl and sample data so we don't have to guess on what your tables look like? Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1432339
Posted Monday, March 18, 2013 3:13 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:57 PM
Points: 546, Visits: 1,062
with much information I had to make some guesses.. something like this?

CREATE TABLE #temp 
(
id INT,
fromdate DATE
)

INSERT INTO #temp
VALUES (1,
'20100111'),
(1,
'20110205'),
(1,
'20120101');

WITH cte
AS (SELECT id,
fromdate,
Row_number()
OVER (
partition BY id
ORDER BY fromdate ASC) rownum
FROM #temp)
SELECT cte.id,
cte.fromdate,
Dateadd(d, -1, cte2.fromdate)
FROM cte
LEFT JOIN cte cte2
ON cte.rownum = cte2.rownum - 1

DROP TABLE #temp

Post #1432359
Posted Tuesday, March 19, 2013 2:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 7:30 AM
Points: 37, Visits: 220
Yes, that's exactly what I was trying to achieve.
Thank you very much for your help.

Post #1432526
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse