Grouping based on contiguous dates
-
Eugene Elutin (3/27/2012)
...
Try adding this into the original sample data
SELECT 'Jun 1 2011 12:00AM','1','10','20' UNION ALL
The results of the two queries are different.
My query will only work if status never returns to something it was before (as per OP sample data).
If it does, I would try to use "quirky update" method.
There's a way to to even that without a Quirky Update by using the difference between two ROW_NUMBERs that use different partitions.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (3/27/2012)
Eugene Elutin (3/27/2012)
...
Try adding this into the original sample data
SELECT 'Jun 1 2011 12:00AM','1','10','20' UNION ALL
The results of the two queries are different.
My query will only work if status never returns to something it was before (as per OP sample data).
If it does, I would try to use "quirky update" method.
There's a way to to even that without a Quirky Update by using the difference between two ROW_NUMBERs that use different partitions.
Which is exactly what my query does.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Jeff Moden (3/27/2012)
Eugene Elutin (3/27/2012)
...
Try adding this into the original sample data
SELECT 'Jun 1 2011 12:00AM','1','10','20' UNION ALL
The results of the two queries are different.
My query will only work if status never returns to something it was before (as per OP sample data).
If it does, I would try to use "quirky update" method.
There's a way to to even that without a Quirky Update by using the difference between two ROW_NUMBERs that use different partitions.
Over 25 sec for just over 1 million records, Quirky Update may run faster...
may not.
I guess it's really depends on real situation, if state cannot return back, then grouping is enough, otherwise - row_number or QU, whatever faster for his case.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply