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 ««12345»»»

Group Islands of Contiguous Dates (SQL Spackle) Expand / Collapse
Author
Message
Posted Tuesday, December 07, 2010 9:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 5:19 PM
Points: 265, Visits: 2,538
This was new to me and you did an excellent job of explaining how it works. I was with you every step of the way. 5 stars.
Post #1031288
Posted Tuesday, December 07, 2010 9:25 AM


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: Tuesday, April 15, 2014 12:05 PM
Points: 3,566, Visits: 72,411
As always when I read an article by Jeff two things happen.

1. I find a very useful tip that I'm sure I'll need within 1 month of reading the article

2. I am amazed at how Jeff thinks. That's such an elegant solution to that sort of problem.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1031306
Posted Tuesday, December 07, 2010 9:43 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
dadam (12/7/2010)
When Jeff said, "even though the DateGroup looks like a date, it really doesn't mean anything," he was fibbing a bit, probably to keep unnecessary detail out of the article.


Agh... Thanks for the correction... I should have said,

"...even though the DateGroup looks like a date, it simply being a date doesn't really mean anything,"

You are correct about the significance of the date and, for important a concept as contiguous dates all have the same date in the column, I did gloss over it a bit to keep the "spackle" short and mostly left that realization up to the reader by reading the results and the comments from that one query.

And the addition of DaysSkipped is a clever bit of code. Thanks for posting it.


--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 #1031330
Posted Tuesday, December 07, 2010 9:46 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
Craig Farrell (12/7/2010)
Alright, let's make the data a little more real world scenario.

You have a contractor who comes by and does work as needed. You're looking for a per problem average of how long it takes for them to fix whatever issue they were brought in for. You've got a log of said person's hours:...


Very cool explanation on that post. Thanks a bunch for covering for me, Craig. I was busy at work at just couldn't get the time to respond.


--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 #1031336
Posted Tuesday, December 07, 2010 9:53 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
autoexcrement (12/6/2010)
But how can you tell which dates are "start", "end" or "start and end" without any additional information (like the comments)? I don't see any rhyme or reason here, aside from the dates being in chronological order.


I know Craig covered this, but you actually got it right there. The dates are simply in chronolgical order and the StartDate and EndDates identify the "islands" of dates where there are no missing dates.

I think a lot of folks were looking for a much more complicated answer to this simple problem.


--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 #1031349
Posted Tuesday, December 07, 2010 9:56 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
tommyh (12/6/2010)
Nice article as a whole... a bit weak/confusing intro though. But thx to Craigs little comment it became much more clear what/why was being done.

/T


The folks that know me know where the detail of my articles usually is... in the comments in the code. I should probably get into the habit of stating that, though. Thanks for the constructive feedback, Tommy.


--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 #1031351
Posted Tuesday, December 07, 2010 9: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 @ 10:03 PM
Points: 35,956, Visits: 30,244
autoexcrement (12/6/2010)
Okay, I think this is starting to make sense now. Contiguous is defined here as "at least one date entry per day during a range". Okay. And now the rest of the article makes sense too. Cool.

Wow, now that I understand what's going on, this article is freaking awesome. Jeff rocks. (Craig, too.)


Now there's a huge compliment. Nah... I don't mean the "Jeff rocks" thing. I mean the huge lightbulb that just turned on. Thanks for the awesome feedback.

Craig... it was your code that clarified it all here. Thanks again for the wonderful "cover" while I was busy.


--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 #1031359
Posted Tuesday, December 07, 2010 10:00 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
nigel. (12/7/2010)
Nice Jeff, very nice indeed.
Now, all I need is to find somewhere I can use this


Thanks for the feed. With a bit of extrapolation, you can also do a similar thing to find "islands" of just about any numeric sequence. Think the inverse of "find missing identies".


--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 #1031362
Posted Tuesday, December 07, 2010 10:02 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
JJ B (12/7/2010)
This was new to me and you did an excellent job of explaining how it works. I was with you every step of the way. 5 stars.


Thanks for the great feedback, JJ. Good to "see" you again.


--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 #1031367
Posted Tuesday, December 07, 2010 10:05 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
mtassin (12/7/2010)
As always when I read an article by Jeff two things happen.

1. I find a very useful tip that I'm sure I'll need within 1 month of reading the article

2. I am amazed at how Jeff thinks. That's such an elegant solution to that sort of problem.


You're too kind, Mark. Thanks for the awesome compliment. Like I said at the end of the article, it appears that someone else thought of the same thing and published it in a book so I'm certainly not alone in that way of thinking.

What most people don't understand is that I learned a huge amount of what I know and the way I think from this very site and I'm just trying to return the favor.

Glad to see that "no RBAR" flag is still flying high.


--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 #1031369
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse