TSQL Challenge

  • Taken from TSQL Challenge

    The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The challenge is to find the Islands(gaps) in sequential dates. You need to write a query to identify continuous intervals from the start date and end date.

    For example,

    01/01/2012 - 01/17/2012

    01/18/2010 - 02/20/2012

    The above two intervals should be considered as 01/01/2012 - 02/20/2012

    Rules

    The output should be ordered by PatientID, AdmissionDate.

    For any patient there will be no overlapping date intervals.

    Sample data

    There is a table which maintains the Patient admission and discharge information. Each admission comes as one new record. But when there is a continuous internal, you should show them as a single row in output

    PatientID AdmissionDate DischargeDate Cost

    --------- ------------- ------------- -------

    709 2011-07-27 2011-07-31 450.00

    709 2011-08-01 2011-08-23 2070.00

    709 2011-08-31 2011-08-31 90.00

    709 2011-09-01 2011-09-14 1260.00

    709 2011-12-01 2011-12-31 2790.00

    1624 2011-06-07 2011-06-28 1980.00

    1624 2011-06-29 2011-07-31 2970.00

    1624 2011-08-01 2011-08-02 180.00

    Expected Results

    PatientID AdmissionDate DischargeDate Cost

    --------- ------------- ------------- -------

    709 2011-07-27 2011-08-23 2520.00

    709 2011-08-31 2011-09-14 1350.00

    709 2011-12-01 2011-12-31 2790.00

    1624 2011-06-07 2011-08-02 5130.00

    Sample Script

    IF OBJECT_ID('TC79','U') IS NOT NULL BEGIN

    DROP TABLE TC79

    END

    GO

    CREATE TABLE TC79(

    PatientID INT,

    AdmissionDate DATETIME,

    DischargeDate DATETIME,

    Cost MONEY

    )

    GO

    INSERT INTO TC79(PatientID,AdmissionDate,DischargeDate,Cost)

    SELECT 709,'2011-07-27','2011-07-31',450.00 UNION ALL

    SELECT 709,'2011-08-01','2011-08-23',2070.00 UNION ALL

    SELECT 709,'2011-08-31','2011-08-31',90.00 UNION ALL

    SELECT 709,'2011-09-01','2011-09-14',1260.00 UNION ALL

    SELECT 709,'2011-12-01','2011-12-31',2790.00 UNION ALL

    SELECT 1624,'2011-06-07','2011-06-28',1980.00 UNION ALL

    SELECT 1624,'2011-06-29','2011-07-31',2970.00 UNION ALL

    SELECT 1624,'2011-08-01','2011-08-02',180.00

    SELECT * FROM TC79

    GO

  • Here is the solution. http://www.sqlservercentral.com/articles/71550/[/url]

    _______________________________________________________________

    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/

  • One possible solution, probably lots of others...

    WITH Starts AS (

    SELECT a.PatientID,a.AdmissionDate,

    ROW_NUMBER() OVER(PARTITION BY a.PatientID ORDER BY a.AdmissionDate) AS rn

    FROM TC79 a

    WHERE NOT EXISTS(SELECT * FROM TC79 b

    WHERE b.PatientID=a.PatientID

    AND DATEADD(Day,1,b.DischargeDate) = a.AdmissionDate)),

    Ends AS (

    SELECT a.PatientID,a.DischargeDate,

    ROW_NUMBER() OVER(PARTITION BY a.PatientID ORDER BY a.DischargeDate) AS rn

    FROM TC79 a

    WHERE NOT EXISTS(SELECT * FROM TC79 b

    WHERE b.PatientID=a.PatientID

    AND DATEADD(Day,-1,b.AdmissionDate) = a.DischargeDate))

    SELECT s.PatientID,s.AdmissionDate,e.DischargeDate,SUM(t.Cost) AS Cost

    FROM Starts s

    INNER JOIN Ends e ON e.PatientID=s.PatientID

    AND e.rn=s.rn

    INNER JOIN TC79 t ON t.PatientID=s.PatientID

    AND t.AdmissionDate BETWEEN s.AdmissionDate AND e.DischargeDate

    GROUP BY s.PatientID,s.AdmissionDate,e.DischargeDate

    ORDER BY s.PatientID,s.AdmissionDate,e.DischargeDate;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thats Perfect Mark ..Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply