March 5, 2013 at 12:55 pm
Hi all - I was presented with a request today which I'm completely stumped on.
Here it is; Given an employees table with the columns ID, FirstName, LastName, HireDate, and TerminationDate how would you calculate the longest period in days that the company has gone without hiring or firing anyone.
Any help would be greatly appreciated. Thanks, RJ
--------------------------------------------
Laughing in the face of contention...
March 5, 2013 at 1:02 pm
This is commonly referred to as "Gaps and Islands". Do a site search on SSC and you will find lots of information about how to go about this type of thing. Just don't use the cursor or loop based methods as they will be a lot slower than their set-based alternative.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2013 at 1:49 pm
Google for 'Gaps and Islands' Itzik Ben-Gan
He's written a huge about on several ways to get this kind of result.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply