Link to home
Start Free TrialLog in
Avatar of vjensen
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!
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Vjensen,

These things needs to handled in queries.  Please change your query like below-

--

CREATE TABLE Pat
(
	 [Patient #] INT     
	,[UID]  INT          
	,[Admission Date] DATETIME
	,[Dismissal Date] DATETIME
)
GO

INSERT INTO Pat VALUES
(1234     ,       44,                 '08/01/2015',                 '08/31/2015'),
(1234     ,       104,                '09/24/2015',                 '09/29/2015')

SELECT k.[Patient #],k.UID,k.[Admission Date],k.[Dismissal Date] , CASE WHEN ndate IS NOT NULL THEN DATEDIFF(d,[Admission 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

--

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
Patient #   UID         Admission Date          Dismissal Date          DateDifference
----------- ----------- ----------------------- ----------------------- --------------
1234        44          2015-08-01 00:00:00.000 2015-08-31 00:00:00.000 54
1234        104         2015-09-24 00:00:00.000 2015-09-29 00:00:00.000 0

(2 row(s) affected)

Open in new window


Hope it helps!
Avatar of vjensen
vjensen

ASKER

Thank you.  I'll give that a try.  That's lot more than I expected.
Avatar of vjensen

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?

/*------------------------
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)

Open in new window


Hope it helps!
Avatar of Mike McCracken
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
Avatar of vjensen

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})
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vjensen

ASKER

That's awesome.  I appreciate your time and experience!!  Thank you.
Avatar of vjensen

ASKER

I appreciate the experience and expertise you bring to this forum.  Thanks so much!