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

    Question

  • 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
    Wednesday, February 24, 2010 2:22 AM

Answers

  • 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 LG1815 Wednesday, February 24, 2010 6:03 PM
    Wednesday, February 24, 2010 5:39 PM

All replies

  • 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 Melissa Suciadi Wednesday, February 24, 2010 4:11 AM
    • Unproposed as answer by LG1815 Wednesday, February 24, 2010 5:24 PM
    Wednesday, February 24, 2010 3:32 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 3:48 AM
  • 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:26 PM
  • 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 LG1815 Wednesday, February 24, 2010 6:03 PM
    Wednesday, February 24, 2010 5:39 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.

    Wednesday, February 24, 2010 6:03 PM