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