Here's another way. Comments in the code tell you the right way to do it.

CREATE TABLE #Persons

(Name VARCHAR(20), Birth_Date DATE, Death_Date DATE

...

For anyone that's interested in these rCTEs and because there's been a recent flurry of possible uses appear in the forums, I took another look trying to improve the speed.

I...

Jeff Moden (10/3/2012)

---

dwain.c (10/3/2012)

---

Cadavre (10/3/2012)

---

dwain.c (10/3/2012)

---

Jason-299789 (10/3/2012)

---

I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number...

Cadavre (10/3/2012)

---

dwain.c (10/3/2012)

---

Jason-299789 (10/3/2012)

---

I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data...

GSquared (10/3/2012)

---

The solution Joe provides doesn't actually prevent gaps. The PreviousEndDate column...

Jason-299789 (10/3/2012)

---

I think its the fact that the Row_number() requires an order by statement thus a sort, rather than an Identity which assigns the number as the data is returned....

Jeff Moden (10/3/2012)

---

Jason-299789 (10/3/2012)

---

Karthick,

Jeff was making valid point about about how to create the Tally table in the first place, most people will write a while loop, or a recursive...

MM,

I don't know if you're familiar with the "Sweden Redistricting" problem, but it is mentioned at the bottom of the page in this thread: http://www.sqlservercentral.com/Forums/Topic1318149-392-5.aspx.

I spent two weeks working on...

How about:

SELECT 50*101

instead?

Seriously, I don't think that...

Lowell - Mister Magoo has pointed you in the right direction and his analysis to reduce the initial rowset based on the cents was really good.

His approach is similar to...

Thanks Phil. An important clarification.

WayneS (10/2/2012)

---

Good question Dwain... thanks for submitting it. (I wonder how many that got this correct ran the code first...)

One minor nit-picking comment. You initialize the dates like this:

DECLARE @Date1...

I thought it might be interesting to approach this with a recursive CTE, such as the following. Try this setup data:

declare @Person table

(Id int IDENTITY, Name varchar(20), MartialStatus...

ScottPletcher (10/2/2012)

---

dwain.c (9/26/2012)

---

Nice set up data Sean!

Using it, I'd like to offer a slightly less verbose solution:

SELECT *, WaitTime=DATEDIFF(minute

,CAST(check_in AS TIME)

,CAST(STUFF(appt_time, 3,...

:w00t::hehe::w00t:Didn't look at the setup data.:hehe::w00t::hehe: