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 «««123

Is this a "gaps and islands" problem? Finding gaps in overlapping times. Expand / Collapse
Author
Message
Posted Thursday, August 15, 2013 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 7,108, Visits: 13,463
Jeff Moden (8/15/2013)
ChrisM@Work (8/15/2013)
Dwain's quite capable of providing an American English description of how his code works. Here's an English description of mine


BWAAAA-HAAAA!!!! I'm not sure that's so true anymore. He's developing an accent from all the traveling he's been doing.

With the accent he had, he was afraid of being headhunted. Not so good in PNG


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1484790
Posted Thursday, August 15, 2013 7:23 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: Yesterday @ 6:12 PM
Points: 3,609, Visits: 5,220
Jeff Moden (8/15/2013)
Thank you for the kind words and I know you meant no slight but, just to be sure, Chris and Dwain are both "heavy hitters" in my book. If they weren't, I wouldn't have learned the things from them that I've learned.


Consider myself but a Padawan-learner I do. From a SQL Jedi Master such as yourself, coming such high praise humbles me it does.

ChrisM@Work (8/15/2013)
Jeff Moden (8/15/2013)
ChrisM@Work (8/15/2013)
Dwain's quite capable of providing an American English description of how his code works. Here's an English description of mine


BWAAAA-HAAAA!!!! I'm not sure that's so true anymore. He's developing an accent from all the traveling he's been doing.

With the accent he had, he was afraid of being headhunted. Not so good in PNG


Not only can I do Yoda-speak, I have picked up a fair amount of Aussie slang too. G'day to ya mates!



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 #1484959
Posted Tuesday, September 3, 2013 9:12 AM
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: Saturday, July 19, 2014 8:45 AM
Points: 531, Visits: 2,078
Some time ago two gap problems where published at

http://beyondrelational.com/puzzles/tsql/default.aspx

under puzzles 79 and 80.

I recently also read about 'Static Relational Interval Tree' at

http://www.solidq.com/sqj/Pages/Home.aspx

which at some point talks about how to deal with gaps in big data.
Post #1491001
Posted Tuesday, September 3, 2013 7:14 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: Yesterday @ 6:12 PM
Points: 3,609, Visits: 5,220
I was going to look up this thread but Michael saved me the trouble. Thanks Michael!

What I wanted to say was, what happened to the OP? He promised to let us know what worked for him.




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 #1491121
Posted Tuesday, September 3, 2013 11:32 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:23 PM
Points: 795, Visits: 1,548
Hi Dwain

Sorry for not getting back to you. I've been diverted to other projects for a while. I provided a rather simplified version of the problem as an example, but based on what you posted, I did get it working on my production data. I don't have any concrete metrics to report yet.

Cheers




One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1491137
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse