Date difference between rows in same column (1 Viewer)

[xthai]

Dear members,
I have the below query (A). How do I calculate the Day of Therapy as shown in B

A
MRN Last Name DOA Service startDate INR Dose WarfID VisitID
12001 Smith 6/28/2013 Warfarin 6/29/2013 1.20 6 mg 10 138
12001 Smith 6/28/2013 Warfarin 6/30/2013 1.20 6 mg 11 138
12001 Smith 6/28/2013 Warfarin 7/1/2013 1.3 6 mg 12 138
12001 Smith 6/28/2013 Warfarin 7/2/2013 1.40 6 mg 13 138
12001 Smith 6/28/2013 Warfarin 7/3/2013 1.40 7.5 mg 14 138
12001 Smith 6/28/2013 Warfarin 7/4/2013 1.70 7.5 mg 15 138
12001 Smith 6/28/2013 Warfarin 7/5/2013 2.00 7.5 mg 16 138
11002 Brown 7/1/2013 Warfarin 7/1/2013 1.00 2 mg 17 139
11002 Brown 7/1/2013 Warfarin 7/2/2013 1.3 4 mg 18 139
11002 Brown 7/1/2013 Warfarin 7/3/2013 1.50 2 mg 19 139
11002 Brown 7/1/2013 Warfarin 7/4/2013 1.8 2 mg 20 139
11002 Brown 7/1/2013 Warfarin 7/5/2013 2.20 2 mg 21 139
1205972 Jones 6/28/2013 Warfarin 7/6/2013 1.0 6.5 mg 29 138
1205972 Jones 6/28/2013 Warfarin 7/7/2013 1.20 6.5 mg 30 138
1205972 Jones 6/28/2013 Warfarin 7/8/2013 1.60 6.5 mg 31 138
1205972 Jones 6/28/2013 Warfarin 7/9/2013 1.7 6.5 mg 32 138
1205972 Jones 6/28/2013 Warfarin 7/10/2013 1.80 6.5 mg 33 138
1205972 Jones 6/28/2013 Warfarin 7/11/2013 2.1 6.5 mg 34 138
1205972 Jones 6/28/2013 Warfarin 7/12/2013 2.50 6 mg 35 138

B








MRN Last Name DOA Service startDate INR Dose WarfID VisitID Day of Therapy 12001 Smith 6/28/2013 Warfarin 6/29/2013 1.20 6 mg 10 138 1 12001 Smith 6/28/2013 Warfarin 6/30/2013 1.20 6 mg 11 138 2 12001 Smith 6/28/2013 Warfarin 7/1/2013
6 mg 12 138 3 12001 Smith 6/28/2013 Warfarin 7/2/2013 1.40 6 mg 13 138 4 12001 Smith 6/28/2013 Warfarin 7/3/2013 1.40 7.5 mg 14 138 5 12001 Smith 6/28/2013 Warfarin 7/4/2013 1.70 7.5 mg 15 138 5 12001 Smith 6/28/2013 Warfarin 7/5/2013 2.00 7.5 mg 16 138 7 11002 Brown 7/1/2013 Warfarin 7/1/2013 1.00 2 mg 17 139 1 11002 Brown 7/1/2013 Warfarin 7/2/2013 1.3 4 mg 18 139 2 11002 Brown 7/1/2013 Warfarin 7/3/2013 1.50 2 mg 19 139 3 11002 Brown 7/1/2013 Warfarin 7/4/2013 1.8 2 mg 20 139 4 11002 Brown 7/1/2013 Warfarin 7/5/2013 2.20 2 mg 21 139 5 1205972 Jones 6/28/2013 Warfarin 7/6/2013 1 6.5 mg 29 138 1 1205972 Jones 6/28/2013 Warfarin 7/7/2013 1.2 6.5 mg 30 138 2 1205972 Jones 6/28/2013 Warfarin 7/8/2013 1.60 6.5 mg 31 138 3 1205972 Jones 6/28/2013 Warfarin 7/9/2013 1.7 6.5 mg 32 138 4 1205972 Jones 6/28/2013 Warfarin 7/10/2013 1.80 6.5 mg 33 138 5 1205972 Jones 6/28/2013 Warfarin 7/11/2013 2.1 6.5 mg 34 138 6 1205972 Jones 6/28/2013 Warfarin 7/12/2013 2.50 6 mg 35 138 7

What I really want is to calculate the time (# day) it takes to reach a target goal INR (2-3)
I have tried DateDiff between DOA (date of admission) and Start date but this obvious won’t work because some patient won’t start on warfarin until later.
Another possibility is to calculate date difference between two rows in same column but I don’t know how
For example Pt 1 = It take 7 days to reach an INR of 2, Patient #2 = it takes 5 days to reach an INR 2.2 and Patient #3 takes 6 days to reach an INR of 2.1. Average = 6 days
any suggestion is appreciated
Thank you very much
xthai

 

[CJ_London]

struggling to understand your data but what you need to use is a subquery which woulld be incorporated into a query along these lines

SELECT DISTINCT *,(SELECT MAX(StartDate) FROM myTable as Tmp WHERE MRN=myTable.MRN and StartDate<myTable.StartDate) AS PreviousStartDate 
FROM myTable

 

[xthai]

Thank you very much CJ for suggestions.
I managed to modify your codes to calculate Minimum date for each patient by this code:
MinDate: (SELECT Min(SDate) FROM QryWarfarinDueRpt as Tmp WHERE VisitID=QryWarfarinDueRpt.VisitID)
and use DateDiff("d",[MinDate],[sdate])+1 to calculate Day of therapy . Please see Excel attachment section A
As I mentioned before my goal is to calculate # of day it takes for INR level to reach therapeutic range of 2 and 3.
I can limit the INR column to show only records with INR between 2-3. Since some patients may have multiple INR between 2-3 (2, 2.3, 2.5 etc). I only want to pick minimum INR within this range.
How do I write a query to obtain the information as in the attachment section B
So that I can calculate the average day it takes for INR to reach the target goal of 2 and 3.
Thank you again
Xthai

 

[CJ_London]

rather than a subquery to calculate a value you need to use it as a criteria - so to get the appropriate record would be something like

SELECT *, datediff('d',SDate,(SELECT Min(SDate) FROM QryWarfarinDueRpt as Tmp WHERE VisitID=QryWarfarinDueRpt.VisitID)) AS TherapyDays 
FROM QryWarfarinDueRpt 
WHERE INR=(SELECT Min(INR) FROM QryWarfarinDueRpt as Tmp WHERE VisitID=QryWarfarinDueRpt.VisitID AND INR>=2)

 

[xthai]

Hi CJ, thank you for your quick response. Your codes work beautifully. I made minor changes to TherapyDays to make them positive values and change the INR>=2 to the target goal which is 2 to 3.
SELECT DateDiff('d',SDate,(SELECT Min(SDate) FROM QryWarfarinDueRpt as Tmp WHERE VisitID=QryWarfarinDueRpt.VisitID)) AS TherapyDays, ([TherapyDays])*-1 AS DOT, *
FROM QryWarfarinDueRpt
WHERE (((QryWarfarinDueRpt.[INR])=(SELECT Min(INR) FROM QryWarfarinDueRpt as Tmp WHERE VisitID=QryWarfarinDueRpt.VisitID AND INR Between 2 And 3)))
ORDER by PatientID;
Attached are the results from your codes. Some patients might have the same INR levels or different values but between the range of 2 to 3. Is there a way to select only 1 value per patient within this range?
Thank you again
Xthai

 

[CJ_London]

you can simplify your code slightly by swapping the sdate and subquey around in the datediff function.

regards changing the >=2 to a between - what happens if the INR goes to 3.1 and misses any values between 2 and 3?

Regards your last question, you need to be a bit clearer - in the highlighted records in your attachment which therapy day do you want - the minimum or maximium or the first or last therapy day? (first or last can be affected by ordering the data).

 

[xthai]

as always thank you for your quick response. I actually figured it out today.
XThai