Creating bins by dates comparison

[kaustubh]

Hello Brothers/Sisters.

I am trying to create bins by comparing dates "Appt date" & "Appt. Scheduled date".

I wish to count them later in set analysis to create a visual say bar graph. My query is as below:

if("Appt Date" = "Appt Date Entered", 'current',
if(("Appt Date" - "Appt Date Entered") > 0 and ("Appt Date" - "Appt Date Entered") <= 30, 'Within 30 Days',
if(("Appt Date" - "Appt Date Entered") > 30 and ("Appt Date" - "Appt Date Entered") <= 90, 'Within 90 Days',
if(("Appt Date" - "Appt Date Entered") > 90, 'Greater than 90 Days')))) as "Scheduling Intervals"

Can someone please correct above query of me written in data load editor? The "Appt Date" and "Appt Date Entered" are seperate columns and I have to group this first for every patient account number. This would ensure that same appointment was cancelled on the date it was scheduled for the same patient.

Thanks.

[swallace104]

I'm not fully understanding the question. What's your original data look like? Can you show a sample with fake information so we can better understand where you're starting from? 

Also, that if statement looks correct at first glance. Is there a specific error your getting or are you having trouble getting your data into a single row so that if statement will work?

[kaustubh]

This is sample of data:

My primary key is formed with first four columns. AcctNo,PatNo,ChgSlipNo,Sequence. Hence I have to group by all of them. I wish to check if :

appt date = stat_change then put label as 'current'

stat_change - appt date >0 and <= 30 then 'within 30 days'

stat_change - appt date > 30 and <= 90 then 'within 90 days'

stat_change - appt date > 90 then 'greater than 90 days'

Thus I am suppose to create bins according to time difference but I will have to group by to ensure that patients remain the same.

How should I achieve it?

[swallace104]

I'm guessing the format of your date is what's screwing you up since it has a timestamp with it. 

Try the if statement you originally provided but wrap the dates like this   Date(Date#(Subfield("appt date",' ',1))) that will  remove the timestamp part and format the results properly as Date fields. 

[swallace104]

Oh, additionally to do the grouping use a preceding load in your script. So something like

Appointments_Table:
load *,
if("Appt Date" = "Appt Date Entered", 'current',
if(("Appt Date" - "Appt Date Entered") > 0 and ("Appt Date" - "Appt Date Entered") <= 30, 'Within 30 Days',
if(("Appt Date" - "Appt Date Entered") > 30 and ("Appt Date" - "Appt Date Entered") <= 90, 'Within 90 Days',
if(("Appt Date" - "Appt Date Entered") > 90, 'Greater than 90 Days')))) as "Scheduling Intervals"
;
load
AcctNo&PatNo&ChgSlipNo&Sequence as _PatientKey
,Date(Date#(Subfield(stat_change, ' ', 1))) as "Appt Date Entered"
,Date(Date#(Subfield(appt_date, ' ', 1))) as "Appt Date"
from DATATABLE (or SQL wherever you're loading from)
group by AcctNo&PatNo&ChgSlipNo&Sequence ;

[kaustubh]

if(Date(Date#(SubField(appt_date,'',1))) = Date(Date#(SubField(stat_change,'',1)))
and appt_status = 'CAN', 'current',

if((Date(Date#(SubField(appt_date,'',1))) - Date(Date#(SubField(stat_change,'',1)))) > 0
and (Date(Date#(SubField(appt_date,'',1))) - Date(Date#(SubField(stat_change,'',1)))) <= 30
and appt_status = 'CAN', 'Within 30 Days',

if((Date(Date#(SubField(appt_date,'',1))) - Date(Date#(SubField(stat_change,'',1)))) > 30
and (Date(Date#(SubField(appt_date,'',1))) - Date(Date#(SubField(stat_change,'',1)))) <= 90
and appt_status = 'CAN', 'Within 90 Days',

if((Date(Date#(SubField(appt_date,'',1))) - Date(Date#(SubField(stat_change,'',1)))) > 90
and appt_status = 'CAN',
'Greater than 90 Days' )))) as "Scheduling Intervals"

 I need date in format YYYY-MM-DD

SAYS: Invalid Expression. What I might be doing wrong?

[kaustubh]

Yes I have grouped it the way you asked. THANK YOU for that very much. 🙂

[swallace104]

You're missing the else parameter after the 'Greater than 90 Days'. Could be that, change it to something like
if((Date(Date#(SubField(appt_date,'',1))) - Date(Date#(SubField(stat_change,'',1)))) > 90
and appt_status = 'CAN',
'Greater than 90 Days', '')

[kaustubh · Accepted solution]

Hello. That did work for me but it worked while comparing the dates. To find the difference between two dates as per the days. The thing that did the trick was below as:

if(interval(appt_date - "date_entered",'d') > 90,
'Greater than 90 Days')) as "Scheduling Intervals"

the interval function. Thanks. I shall get back if there is any error in verification of data. take care !!.