elapsed time

[cbushey1]

Looking for some guidance on how best to create the elapsed time here. 

Let's assume I have two tables, Case and Event. 

What I would like to do, is calculate the elapsed time between each case and all the events. Multiple cases can be linked to multiple events. 

Case:
LOAD * INLINE[
PatientID, CaseID, CaseDate,Type
39655, 1, '2/5/19', 'Case'
39655, 2, '3/1'19', 'Case'
];

Event:
LOAD * INLINE[
PatientID, EventID, EventDate, EventType
39655, 3, '10/1/18', 'Claim'
39655, 4, '11/11/18', 'Claim'
39655, 5, '12/19/18', 'Claim'
];

What I am trying to identify is the number of days between each case and event for a single patientID. In my head it looks like this (see combined table), however I don't want to outer join the data together because the events table is going to be large, millions of rows, and when I think about looking at each case the combined table would be way to cumbersome.

Combined:
LOAD * INLINE[
PatientID, CaseID,EventID, DaysBetween
39655, 1, 3, 127
39655, 1, 4, 86
39655, 1, 5, 48
39655, 2, 3, 151
39655, 2, 4, 110
39655, 2, 5, 72
[;

Any suggestions for better approaches?

[patilamay]

If you don't want to join these two tables, you can create the same view in front end.  "[CaseDate] - EventDate"

Hope this helps.

Thanks,

[cbushey1]

Thanks for the reply but that doesnt really address the root of the problem. The problem is that its not a 1-1 between event date and Case Date. The reason I wanted to calculate the days between on the back end was to save on processing time on the front end. The end result wont just be the number of days between, but I am planning to report on the aggregate rather than at the individual claim level. 

Anybody else think of something over the weekend, I know I didnt?

[patilamay]

I think you could calculate your expressions in back-end using variable. You could do simple difference using a variable and use that variable for aggregates in front-end or back-end which should save on processing time. Let me know your thoughts on this approach.

Thanks,

[cbushey1]

Thanks but I am not sure I see how using a simple variable will help here.

Perhaps providing an example would help me understand.