Finding Difference in Date across rows

[mellerbeck]

So say there are 5 Patients and one of the Patients came in to the office today (12/02/2014) and they also came in previously less than 30 days ago. How would I display this one patient that matches the criteria in a chart?

LOAD PatientID, date(AppDate,'MM/DD/YYYY') as AppDate INLINE [

    PatientID, AppDate

    1, 12/02/2014

    1, 11/22/2014

    2, 01/01/2014

    3, 01/01/2014

    4, 01/01/2014

    5, 01/01/2014

];

Thanks for any thoughts!

-Michael

[saurabh5]

hi Michael,

you can use a floor function to get the difference between the dates and use today() for latest date below is the script:

TEST:LOAD PatientID, date(AppDate,'MM/DD/YYYY') as AppDate INLINE [
    PatientID, AppDate
    1, 12/02/2014
    1, 11/22/2014
    2, 01/01/2014
    3, 01/01/2014
    4, 01/01/2014
    5, 01/01/2014
];

NEW:LOAD PatientID,AppDate,FLOOR(TODAY()-AppDate) AS DIFF_daYSRESIDENT TEST; 

and then use =count(distinct {<DIFF_daYS={'<30'}>}PatientID) for any chart to build as htis will give patient which visited less than 30 days.

[mellerbeck]

hmm maybe the max date minus the max date of rank 2

[MK_QSL · Accepted solution]

Test:

LOAD PatientID, date(AppDate,'MM/DD/YYYY') as AppDate INLINE [

    PatientID, AppDate

    1, 12/02/2014

    1, 11/22/2014

    2, 01/01/2014

    3, 01/01/2014

    4, 01/01/2014

    5, 01/01/2014

];

Final:

Load

  *,

  If(PatientID = Previous(PatientID) and Interval(AppDate - Previous(AppDate))<=30,'Yes','No') as ThirtyDaysVisitFlag

Resident Test

Order By PatientID, AppDate;

Drop Table Test;

[MarcoWedel]

Hi,

one solution to calculate the days since previous visit in the script could be:

// Test Data Generation
tabPatAppDat:
LOAD Ceil(Rand()*100) as PatientID,
    DayName(YearStart(Today())+Rand()*(DayNumberOfYear(Today()))) as AppDate
AutoGenerate 400;

// adding DaysSinceLastVisit field
Left Join (tabPatAppDat)
LOAD PatientID,
    AppDate,
    If(PatientID=Previous(PatientID),AppDate-Previous(AppDate)) as DaysSinceLastVisit
Resident tabPatAppDat
Order By PatientID, AppDate;

hope this helps

regards

Marco