vjensen
asked on
Crystal Report formula to calculate difference between 2 dates on 2 different lines of detail
I need to calculate the dates a patient is "out" of the facility. Each line of detail has a UID that is unique to that line. Here's the data:
Patient # UID Admission Date Dismissal Date
1234 44 08/01/2015 08/31/2015
1234 104 09/24/2015 09/29/2015
I need to calculate the difference between 08/31/2015 & 09/24/2015. Thank you in advance for your help!
Patient # UID Admission Date Dismissal Date
1234 44 08/01/2015 08/31/2015
1234 104 09/24/2015 09/29/2015
I need to calculate the difference between 08/31/2015 & 09/24/2015. Thank you in advance for your help!
ASKER
Thank you. I'll give that a try. That's lot more than I expected.
ASKER
The answer of 54 is not correct. I need the difference between 08/31/2015 & 09/24/2015 which should be 24 days.
Please try this. I used another column. By the way which DB are you using?
Hope it helps!
/*------------------------
SELECT k.[Patient #],k.UID,k.[Admission Date],k.[Dismissal Date] , CASE WHEN ndate IS NOT NULL
THEN DATEDIFF(DAY,[Dismissal Date],ndate) ELSE 0 END DateDifference FROM
(
SELECT * , (SELECT TOP 1 b.[Admission Date] FROM Pat b WHERE a.[Patient #] = b.[Patient #] AND b.UID > a.UID ) ndate
FROM Pat a
)k
------------------------*/
Patient # UID Admission Date Dismissal Date DateDifference
----------- ----------- ----------------------- ----------------------- --------------
1234 44 2015-08-01 00:00:00.000 2015-08-31 00:00:00.000 24
1234 104 2015-09-24 00:00:00.000 2015-09-29 00:00:00.000 0
(2 row(s) affected)
Hope it helps!
Which line do you need to show it on?
Does a patient ever have more than 2 records?
Does a patient ever have only 1 record?
mlmcc
Does a patient ever have more than 2 records?
Does a patient ever have only 1 record?
mlmcc
ASKER
Yes on both. A patient can have 1 or many records.
I'd like the answer to be on the "2nd" or next line.
Here's a formula that works, but it may have pitfalls.
if {patient#}= previous({patient#})then
{ADMISSIONDATE} - previous({DISCHARGEDATE})
I'd like the answer to be on the "2nd" or next line.
Here's a formula that works, but it may have pitfalls.
if {patient#}= previous({patient#})then
{ADMISSIONDATE} - previous({DISCHARGEDATE})
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's awesome. I appreciate your time and experience!! Thank you.
ASKER
I appreciate the experience and expertise you bring to this forum. Thanks so much!
These things needs to handled in queries. Please change your query like below-
Open in new window
OUTPUT
Open in new window
Hope it helps!