March 18, 2013 at 2:23 pm
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
March 18, 2013 at 2:28 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2013 at 3:13 pm
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
March 19, 2013 at 2:41 am
Yes, that's exactly what I was trying to achieve.
Thank you very much for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply