September 28, 2010 at 9:45 pm
Please help me with below sql query...
I have 2 tables with below structures....
Create Table Patient
(PatientID int,
PatientAddress nvarchar(100),
Patientphone nvarchar(10))
Create Table Treatment
(
PatientID int,
TreatmentID int,
StartDt datetime,
Enddt datetime)
Insert into Patient Values(1,'abc','1234567890')
Insert into Patient Values(2,'xyz','9999999999')
Insert into Patient Values(3,'jjj','678912345469')
-- PatientID 1 rows in treatment table
Insert into Treatment Values(1,11,'01/01/2007','12/12/2007')
Insert into Treatment Values(1,22,'01/20/2008','12/12/2008')
Insert into Treatment Values(1,33,'01/25/2009','12/12/2009')
Insert into Treatment Values(1,44,'01/22/2010',Null)
-- PatientID 2 rows in treatment table
Insert into Treatment Values(2,55,'01/01/2007','10/12/2007')
Insert into Treatment Values(2,66,'01/20/2008','10/12/2008')
Insert into Treatment Values(2,77,'01/25/2009','10/12/2009')
Insert into Treatment Values(2,88,'01/22/2010','02/02/2010')
-- PatientID 3 rows in treatment table
Insert into Treatment Values(3,99,'01/01/2007',NUll)
Could someone please help me with the query , I have to get all the patients who are having treatments from past 3 yrs continously..
and for each patient there might treatment started and ended ......but if the treatment is started within 45 days after the previous treatment is ended..then it is considered as continous treatment..and if the treatment as started and it has end date as null...then it is considered as continous treatment as wellll....
if the Patient First Treatment started and ended and after 45 days of the first treatment the second treatment is started then it is not considered as continous treatment.
reason the second treatment is started after 45 days of the first treatment as ended...
in the above example... i should get the patient id 1 and patient id 3 and i should not get patient id 2
reasons
1. patient ID 1 has first treatment ended in 12/12/2007 and then the second treatment started at 01/20/2008 so the second treatment started within 45 days....
and the third treatment started within 45 days of the second treatment ended and fourth treatment started within 45 days of the 3rd treatment was ended....
2 Patient ID 2 should not get displayed reasons-
a. First treatment ended in 10/12/2007 and the second treatment started in 01/20/2008 so the second treatment started after the 45 days of the first treatment
was ended...
3. patient id 3 should display - reason treatment was started in 01/01/2007 and it has end date as Null so the first treatment never ended from past 3 years...
so i should get the patient id 3 as welll
Please i it's a urgent request and i have to solve by tomorrow....
September 28, 2010 at 10:45 pm
Lucky9 (9/28/2010)
Insert into Patient Values(3,'jjj','678912345469')
This insert fails because the data is longer than you have allowed in your table...
Could someone please help me with the query , I have to get all the patients who are having treatments from past 3 yrs continously..
and for each patient there might treatment started and ended ......but if the treatment is started within 45 days after the previous treatment is ended..then it is considered as continous treatment..and if the treatment as started and it has end date as null...then it is considered as continous treatment as wellll....
What about the case where a patient had continuous treatment, but the last treatment ended more than 45 days ago? Should they be included or excluded?
Please i it's a urgent request and i have to solve by tomorrow....
That certainly doesn't make me want to help, nothing like being asked to do someone else's work for free.
September 28, 2010 at 11:18 pm
Well since you included sample/test data that almost worked, and had a almost complete explanation of what you wanted I think I was able to make a query to do what you want.
It was fairly easy to do with CTEs and ROW_NUMBER:
WITH CTE AS (SELECT
PatientID,
StartDt,
Enddt,
ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY StartDt) AS RN
FROM Treatment),
PatNumTreatments AS (SELECT
PatientID,
COUNT(*) AS NumTreatments
FROM Treatment
GROUP BY
PatientID),
PatNumContTreatments AS (SELECT
CTE1.PatientID,
COUNT(*) AS NumContTreatments
FROM CTE CTE1
LEFT JOIN CTE CTE2
ON CTE1.PatientID = CTE2.PatientID
AND CTE1.RN = (CTE2.RN - 1)
WHERE
CTE1.Enddt IS NULL
-- Comment/Remove the next line if you want to exclude patients were the last treatment was completed more than 45 days ago
OR (CTE2.PatientID IS NULL OR DATEDIFF(Day, CTE1.Enddt, CTE2.StartDt) <= 45)
-- Uncomment the next line if you want to exclude patients were the last treatment was completed more than 45 days ago
--OR DATEDIFF(Day, CTE1.Enddt, COALESCE(CTE2.StartDt, GETDATE())) <= 45
GROUP BY
CTE1.PatientID)
SELECT
Pat.PatientID,
Pat.PatientAddress,
Pat.Patientphone
FROM Patient Pat
INNER JOIN PatNumTreatments PNT
ON Pat.PatientID = PNT.PatientID
INNER JOIN PatNumContTreatments PNCT
ON PNT.PatientID = PNCT.PatientID
WHERE
PNT.NumTreatments = PNCT.NumContTreatments;
A couple notes:
* I put a couple comments in where you can swap a couple lines in the WHERE clause to change the rule once you know how a patient should be handled when their last treatment ended more than 45 days ago.
* It doesn't do any validation of things like overlapping treatments, reversed start/end dates, or multiple treatments with a NULL end date, hopefully the data is clean.
* Depending on your table size, and the indexes that are available it might not run really fast, but it should get the correct answers. Let me know how it works out for you.
* You weren't specific on what you wanted for results, so I just assumed all the columns from the Patient table.
September 28, 2010 at 11:35 pm
Here is an alternate version that doesn't use as many CTEs, opting for correlated sub-queries instead, and from the execution plan should run faster while still returning the same results:
I was surprised at the difference that the execution plan showed. This query was 33% of the batch, while the other one was 67%. (I expected them to be almost identical.)
But on the other hand the IO statistics show that the first version that uses multiple CTEs has fewer scans and logical reads. (Do CTEs mess with the IO statistics?)
WITH CTE AS (SELECT
PatientID,
StartDt,
Enddt,
ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY StartDt) AS RN
FROM Treatment)
SELECT
Pat.PatientID,
Pat.PatientAddress,
Pat.Patientphone
FROM Patient Pat
WHERE
(SELECT
COUNT(*) AS NumContTreatments
FROM CTE CTE1
LEFT JOIN CTE CTE2
ON CTE1.PatientID = CTE2.PatientID
AND CTE1.RN = (CTE2.RN - 1)
WHERE
CTE1.PatientID = Pat.PatientID
AND (CTE1.Enddt IS NULL
-- Comment/Remove the next line if you want to exclude patients were the last treatment was completed more than 45 days ago
OR (CTE2.PatientID IS NULL OR DATEDIFF(Day, CTE1.Enddt, CTE2.StartDt) <= 45)
-- Uncomment the next line if you want to exclude patients were the last treatment was completed more than 45 days ago
--OR DATEDIFF(Day, CTE1.Enddt, COALESCE(CTE2.StartDt, GETDATE())) <= 45
)
) = (SELECT
COUNT(*) AS NumTreatments
FROM Treatment Treatment
WHERE
Treatment.PatientID = Pat.PatientID);
I don't think it is as readable, but sometimes performance is more of a priority.
Can you test both queries on your, I assume large, data set and let us know how long each takes? I'm really curious as to which will run faster, or if they are both about the same.
September 29, 2010 at 12:36 am
Hi,
This is another option, if u feel ur Treatment id increments by 11 for each new record.
SELECT *
FROM Patient P
JOIN
(
SELECT T1.PatientID,[TreatmentCount]=COUNT(1) from Treatment T1
LEFT OUTER join Treatment T2 on T1.PatientID = T2.PatientID
AND T1.TreatmentID+11 = T2.TreatmentID
WHERE (DATEDIFF(day,T1.Enddt,T2.StartDt) < 45 OR T1.Enddt IS NULL)
GROUP BY T1.PatientID
)T ON P.PatientID = T.PatientID
JOIN
(
SELECT T3.PatientID,[TreatmentCount]=COUNT(1) FROM Treatment T3
GROUP BY PatientID
) T3 ON T.TreatmentCount = T3.TreatmentCount AND T.PatientID = T3.PatientID
September 29, 2010 at 7:17 am
Thanks all for the quick response....
September 29, 2010 at 10:11 pm
Lucky9 (9/29/2010)
Thanks all for the quick response....
Have you had a chance to test them?
If so, do they return the correct results? Was one of them faster than the others? Which did you end up choosing to use?
October 1, 2010 at 10:52 am
What is/are CTEs?:ermm:
Thanks!
pydss517
[font="Comic Sans MS"]pydss517[/font]
October 1, 2010 at 11:13 am
PYDSS517 (10/1/2010)
What is/are CTEs?:ermm:
http://www.google.com/search?q=CTE+%22SQL+Server%22
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2010 at 11:19 am
Wow! Way cool. Thanks so much, Gail. I did the majority of my work with SQL Server 2000 and am now using 2008 R2, so I have a bit of catch-up to do.
[font="Comic Sans MS"]pydss517[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply