Group Islands of Contiguous Dates (SQL Spackle)

Question

Group Islands of Contiguous Dates (SQL Spackle)

Viewing 15 posts - 46 through 60 (of 62 total)

You must be logged in to reply to this topic. Login to reply

Naomi N Say Hey Kid Points: 695 More actions · Answer 1

Thank you both. The expanding with Numbers table was my idea as well and I see on the second page of pointed discussion that this was the approach taken by Jeff. I am now looking at Itzik's article.

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 2

nice article Jeff!

thanks!!!!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

rfr.ferrari (2/18/2012)
nice article Jeff!
thanks!!!!

You bet. Thank you for the feedback.

--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)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

OMG! :w00t:

I must have read this article 5 times at least and I never could quite get a grip on it.

Finally, I've been able to apply it to a real problem! http://www.sqlservercentral.com/Forums/Topic1364849-392-1.aspx?Update=1 Not that I doubted its applicability, just couldn't quite achieve that nirvana of understanding.

Not sure that I have yet, but at least this is a start. 😎

As always, thanks Jeff!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

Jeff Moden (1/16/2011)
Sachin Nandanwar (1/16/2011)
Well I just stumbled upon this article.I tried to do it using quirky update method and seems to be working but haven't tested it on a huge no of rows though.
--=============================================================================
-- Create the test data. This is NOT a part of the solution.
-- This is virually instantaneous.
--=============================================================================
--===== Conditionally drop the test table to make reruns easier.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
GO
--===== Create the test table
CREATE TABLE #MyHead
(SomeDate DATETIME, id int DEFAULT(0))
;
--===== Populate the test table with test data
INSERT INTO #MyHead
(SomeDate)
SELECT '2010-01-01' UNION ALL --1st "Group" of dates (StartDate and EndDate)
SELECT '2010-01-01' UNION ALL --Duplicate date
SELECT '2010-01-03' UNION ALL --2nd "Group" of dates (StartDate and EndDate)
SELECT '2010-01-05' UNION ALL --3rd "Group" of dates (StartDate)
SELECT '2010-01-06' UNION ALL --3rd "Group" of dates (EndDate)
SELECT '2010-01-10' UNION ALL --4th "Group" of dates (StartDate)
SELECT '2010-01-10' UNION ALL --Duplicate date
SELECT '2010-01-11' UNION ALL --4th "Group" of dates
SELECT '2010-01-11' UNION ALL --Duplicate date
SELECT '2010-01-11' UNION ALL --Duplicate date
SELECT '2010-01-12' --4th "Group" of dates (EndDate)
;
declare @ordse int=0
declare @somedate datetime=''
update #MyHead set @ordse=ID=case when somedate=@somedate+1 or @somedate=somedate then @ordse+1 else @ordse-1 end,@somedate=somedate
select min(somedate)min,max(somedate)max,DATEDIFF(dd,min(SomeDate)-1,max(SomeDate))Diff from
(
select *,id-ROW_NUMBER()over(order by (select 1))id1 from #MyHead
)t group by id1 order by min(SomeDate)
drop table #MyHead
I realize the intentions are good here and thank you for that but there are a couple of problems with the code there. For one, it breaks several of the rules for doing a Quirky Update. It's tough enough for me to defend the use of the Quirky Update as it is. If you're going to use it and post such solutions, please follow the rules for its use. Thanks.
Second, although the Quirky Update does the job, isn't a panacea and there's simply no need no need for it here. It requires the use of an extra column and would necessarily require the copying of data from a permanent table to a Temp Table if the column couldn't be added to the permanent table.
Last but not least, since you still do a SELECT with aggregates, I believe you'll find that the Quirky Update method is actually a bit slower than conventional methods, in this case.

I can confirm the last statement, namely that QU performs slower than Jeff's method.

Because I was so conceptually challenged to understand what Jeff had done at first, I tried to see if I could apply QU to this case. While I did get it to work (QU I understand, including the rules :-)), it was definitely slower.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 6

Sorry for the late reply, Dwain. Thanks for the research and the confirmation.

--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)

nelson4183 SSC Rookie Points: 39 More actions · Answer 7

Very nice, thanks for the article. CTE's can be really confusing.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 8

Thank you for the feedback, Nelson, and welcome aboard!

--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)

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 9

how a simply clever trick just blows your mind.

I wonder how many people only read this Spackle once and really got it.

I just copied it to ssms and walked it through reading it, sat back and sipped my tea ( as if I were Sherlok Holmes handling a one pipe problem ) .

Not the first time Jeff succeeds in astonishing me with a simple solution for a quite commom problem.

Thanks again for sharing it all, Jeff

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 10

Nice article!

I immediately applied the code (with minor changes) on another database, and it worked!

Thanks Jeff.

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 11

Thanks for the feedback, Johan. It's always a pleasure to hear from you.

--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 SSC Guru Points: 1003861 More actions · Answer 12

IgorMi (10/4/2013)
Nice article!
I immediately applied the code (with minor changes) on another database, and it worked!
Thanks Jeff.
Regards,
IgorMi

Very cool! Thanks for the feedback. I love success stories.

--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)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 13

I skimmed the article but I couldn't really identify what's new in this re-publish.

I don't suppose you could give us a quick synopsis Jeff?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 14

dwain.c (10/6/2013)
I skimmed the article but I couldn't really identify what's new in this re-publish.
I don't suppose you could give us a quick synopsis Jeff?

Apologies for the VERY late reply, Dwain. No doubt that by now you've figured out that SQL Server Central frequently republishes articles that have had no changes at all. To wit, most articles that show up on Fridays were previously published.

--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)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 15

Jeff Moden (6/28/2014)
dwain.c (10/6/2013)
I skimmed the article but I couldn't really identify what's new in this re-publish.
I don't suppose you could give us a quick synopsis Jeff?
Apologies for the VERY late reply, Dwain. No doubt that by now you've figured out that SQL Server Central frequently republishes articles that have had no changes at all. To wit, most articles that show up on Fridays were previously published.

Better late than never.

Had that happen to one of mine, so I know the drill.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St