Length of Stay Calculation Using T-SQL (Not Cursors)

# Length of Stay Calculation Using T-SQL (Not Cursors)

• Wednesday, February 24, 2010 2:22 AM

I remember a number of years ago coming across an article that used T-SQL queries and many sub-queries to calculate a LOS value for an individual across multiple input records.  I can easily accomplish this in a Stored procedure etc by using a cursor, however I was looking for another solution.

For those who do not know what LOS is:

ID NAME Begin Date End Date

1 Bob 12/31/2009 1/6/2010
1 Bob 1/7/2010 1/15/2010
1 Bob 1/16/2010 1/31/2010
2 Bill 1/1/2009 1/4/2009
2 Bill 2/5/2009 2/6 2009

In this simple example the result set would be (assumming the need for concurrent dates)

1 Bob 12/31/2009 1/31/2010
2 Bill 1/1/2009 1/4/2009
2 Bill 2/5/2009 2/6 2009

### All Replies

• Wednesday, February 24, 2010 3:32 AM
Moderator

Here is one solution:

```SELECT name, startdate,
(SELECT MIN(enddate)
FROM Foo AS C
WHERE NOT EXISTS
(SELECT *
FROM Foo AS D
WHERE C.name = D.name
AND C.enddate >= DATEADD(DAY, -1, D.startdate)
AND C.enddate < D.enddate)
AND C.enddate >= A.startdate
AND C.name = A.name) AS enddate
FROM Foo AS A
WHERE NOT EXISTS
(SELECT *
FROM Foo AS B
WHERE A.name = B.name
AND A.startdate > B.startdate
AND A.startdate <= DATEADD(DAY, 1, B.enddate));```

Plamen Ratchev
• Proposed As Answer by Wednesday, February 24, 2010 4:11 AM
• Unproposed As Answer by Wednesday, February 24, 2010 5:24 PM
•
• Wednesday, February 24, 2010 3:48 AM

Thanks.  I am going to have to look at this in more detail.  At first glance, it appears to do the job.

I also found http://www.sqlservercentral.com/Forums/Topic868147-392-1.aspx
Still trying to understand that also.
• Wednesday, February 24, 2010 5:26 PM

Unfortunately, not quite.

With overlapping dates, etc the code produces duplicate records.
Yes, they can be removed by group by, but I am not sure that is the way to go.
• Wednesday, February 24, 2010 5:39 PM
Moderator

Your sample does not have any overlaps and since I am not really sure what your DDL and real data looks like I cannot propose any solution. If you only get duplicates then DISTINCT will eliminate that. For better answer you can post your CREATE TABLE statements and INSERT statements for sufficient data to illustrate the problem, as well as desired result set.
Plamen Ratchev
• Marked As Answer by Wednesday, February 24, 2010 6:03 PM
•
• Wednesday, February 24, 2010 6:03 PM

Look I do appreciate the help.  Yes distinct will do it.  I will have to see how this performs on large data sets.  So far it seems to do the job fairly quickly.