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

Convert consecutive rows with a date to date range Expand / Collapse
Author
Message
Posted Thursday, February 27, 2014 6:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 1:47 PM
Points: 57, Visits: 214
OK this is more of an academic question but having looked all over I haven't been able to find a solution to a seemingly easy problem. I have a table that has a date field as the primary key and then some data field eg:
Keydate           ValueField
-------------------------
2012-01-01 A
2012-08-14 B
2013-04-12 C
2013-12-05 D

I wonder if it is possible without cursors, using just a SQL statement to create a result set like this:
DateFrom         DateTo           Valuefield
----------------------------------------
2012-01-01 2012-08-14 A
2012-08-14 2013-04-12 B
2013-04-12 2013-12-05 C
2013-12-05 (current date) D

essentially each row uses its key value as a DateFrom value and then gets the DateTo value from the next row in order. Since the magical word is order, I have experimented with ROW_NUMBER creating serial number for the rows using the ordering of the dates but it came to a whole lot of nulls. I got *almost* there but I could not get the last record to display the current date using GETDATE().

Then I tried to use OVER and PARTITION but I have yet a lot to learn in order to use them and had no luck there either.

I am not necessarily looking for a full and complete answer (although that would be welcome). Any comment to point me to a possible solution would be appreciated.
Post #1545858
Posted Thursday, February 27, 2014 7:08 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:58 AM
Points: 5,077, Visits: 8,918
Use ROW_NUMBER, and join on a.RowNo = b.RowNo+1. Use COALESCE or ISNULL to deal with the NULL in the final row.

John
Post #1545863
Posted Thursday, February 27, 2014 8:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
d viz (2/27/2014)
OK this is more of an academic question but having looked all over I haven't been able to find a solution to a seemingly easy problem. I have a table that has a date field as the primary key and then some data field eg:
Keydate           ValueField
-------------------------
2012-01-01 A
2012-08-14 B
2013-04-12 C
2013-12-05 D

I wonder if it is possible without cursors, using just a SQL statement to create a result set like this:
DateFrom         DateTo           Valuefield
----------------------------------------
2012-01-01 2012-08-14 A
2012-08-14 2013-04-12 B
2013-04-12 2013-12-05 C
2013-12-05 (current date) D

essentially each row uses its key value as a DateFrom value and then gets the DateTo value from the next row in order. Since the magical word is order, I have experimented with ROW_NUMBER creating serial number for the rows using the ordering of the dates but it came to a whole lot of nulls. I got *almost* there but I could not get the last record to display the current date using GETDATE().

Then I tried to use OVER and PARTITION but I have yet a lot to learn in order to use them and had no luck there either.

I am not necessarily looking for a full and complete answer (although that would be welcome). Any comment to point me to a possible solution would be appreciated.


Just a hint... you'll get coded answers if you post your data as readily consumable data in the future. Please see the first link under "Helpful Links" in my signature line below for the best way to do such a thing.

I'll do it for you this first time. This is slightly different than what is done in the article but the table is simple so I took a shortcut.

--===== One way to post readily consumable test data
SELECT KeyDate = CAST(d.KeyDate AS DATETIME)
,d.ValueField
INTO #TestTable
FROM (
SELECT '2012-01-01','A' UNION ALL
SELECT '2012-08-14','B' UNION ALL
SELECT '2013-04-12','C' UNION ALL
SELECT '2013-12-05','D'
) d (KeyDate,ValueField)
;


Here's the solution to your problem like John Mitchell stated above. I'm assuming that you wanted a coded answer because you explicitly stated that you "tried to use OVER and PARTITION but I have yet a lot to learn in order to use them and had no luck there either". That's what happens when you don't post readily consumable data... people will, many times, just make a suggestion rather than writing code that demonstrates how to solve the problem.

WITH
cteEnumerateRowsByKeyDate AS
( --=== Number the rows in order by date
SELECT RowNum = ROW_NUMBER()OVER(ORDER BY KeyDate)
,KeyDate
,ValueField
FROM #TestTable
) --=== Do a self join with a "1 offset" to get the date
-- from the next row.
SELECT DateFrom = lo.KeyDate
,DateTo = ISNULL(hi.KeyDate,GETDATE())
,lo.ValueField
FROM cteEnumerateRowsByKeyDate lo
LEFT JOIN cteEnumerateRowsByKeyDate hi
ON lo.RowNum+1 = hi.RowNum
;





--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545944
Posted Thursday, February 27, 2014 8:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 1:47 PM
Points: 57, Visits: 214
thank you very much...
with a minor change to get the order I wanted, it worked perfectly:

SELECT a.keyDate AS DateFrom
,ISNULL(b.keyDate ,GETDATE()) AS DateTo
,a.ValueField
,a.RowNo
FROM [SomeTable] a
LEFT OUTER JOIN [SomeTable] b
ON a.RowNo = b.RowNo -1
ORDER BY
a.keyDate

Post #1545945
Posted Thursday, February 27, 2014 9:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 1:47 PM
Points: 57, Visits: 214
Jeff thank you for the advice and the solution. Using John's suggestion I managed to come up with the code that works doing a little brainwork myself.

I will keep in mind though for any future posts to post readily consumable data because I admit, I never read the forum etiquette... sorry!
Post #1545954
Posted Thursday, February 27, 2014 11:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
d viz (2/27/2014)
thank you very much...
with a minor change to get the order I wanted, it worked perfectly:

SELECT a.keyDate AS DateFrom
,ISNULL(b.keyDate ,GETDATE()) AS DateTo
,a.ValueField
,a.RowNo
FROM [SomeTable] a
LEFT OUTER JOIN [SomeTable] b
ON a.RowNo = b.RowNo -1
ORDER BY
a.keyDate



In that code, is it partial code or do you have a column call "RowNo" in the table? I ask because if it's built into the table even as an IDENTITY column, there is no guarantee that it won't have gaps which will cause "silent" failures of the code above in the form of missing data in the result set.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1546031
Posted Thursday, February 27, 2014 2:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 1:47 PM
Points: 57, Visits: 214
Yes, I am aware of the gaps that may present themselves when using identity columns. In this case though, RowNo is a serial number kept in another table that manages number ranges so that no gaps can exist.

But the curiosity's sake, I also tested this by creating a view which assigns serial number to each using ROW_NUMBER and then querying this view instead of the table directly and it also worked.

Also, since this is a table that holds very few records (less than 50), I didn't consider any performance implications...
Post #1546141
Posted Thursday, February 27, 2014 3:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
d viz (2/27/2014)
Yes, I am aware of the gaps that may present themselves when using identity columns. In this case though, RowNo is a serial number kept in another table that manages number ranges so that no gaps can exist.

But the curiosity's sake, I also tested this by creating a view which assigns serial number to each using ROW_NUMBER and then querying this view instead of the table directly and it also worked.

Also, since this is a table that holds very few records (less than 50), I didn't consider any performance implications...


Sounds like you're all set.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1546155
Posted Wednesday, March 05, 2014 6:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
Hi All,

Maybe I'm too late to the party but I can add a bit of value here.

I recently did some testing of this in SQL 2012 (where there is a faster solution). However there is an option that's available in SQL 2005 that is faster than the JOIN on ROW_NUMBER() if you have a INDEX that includes the date.

--===== One way to post readily consumable test data
SELECT KeyDate = ISNULL(CAST(d.KeyDate AS DATETIME), 0)
,d.ValueField
INTO #TestTable
FROM (
SELECT '2012-01-01','A' UNION ALL
SELECT '2012-08-14','B' UNION ALL
SELECT '2013-04-12','C' UNION ALL
SELECT '2013-12-05','D'
) d (KeyDate,ValueField)
;
GO
ALTER TABLE #TestTable ADD PRIMARY KEY(KeyDate);
GO

SELECT FromDate=KeyDate, ToDate=ISNULL(EffectiveEndDT, GETDATE())
,ValueField
FROM #TestTable a
OUTER APPLY
(
SELECT TOP 1 KeyDate
FROM #TestTable b
WHERE b.KeyDate > a.KeyDate
ORDER BY a.KeyDate
) b (EffectiveEndDT)
ORDER BY KeyDate;

GO
DROP TABLE #TestTable;


I've got an article coming out on SSC (accepted but not yet scheduled) that compares four methods (Jeff's self-join, correlated sub-query, LEAD and OA) and this OUTER APPLY (OA) with either the date in the clustered index or in a non-clustered index seemed to be slightly faster than the other SQL 2005 alternatives. It was nearly as fast as LEAD (the overall winner) in the non-clustered index scenario.

I make no guarantees though that it is faster in SQL 2005 as I can't test it there.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1548060
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse