Difference between dates - problem!

[jlampard40]

Hi guys.  I have two dates I want to calculate the days difference between.  

The first date field is called [WBR_Event_Date] and the second field is called [WBR_Clock_Reset_Date].  I need to find out the days between the two.  This seems easy enough.  However, within the WBR_Event_Date values is a date value of 01/01/2099 (this represents non booked event date).  I'd like to somehow replace this value with current date (today's date every time we load the daily data).  I can then work out the difference between the two dates and, where event date = 01/01/2099 it's replaced with current date so we can derive 'Total days waiting to date' as an additional field to add into my table.

How do I go about firstly replacing all of the 01/01/2099 dates with current (today's date) and then calculating the difference between this and the other field I've mentioned above?  Having problems...!  Really appreciate your help.

[jaibau1993]

Hi!

On the Script side you may use RangeMin when loading  WBR_Event_Date:

LOAD

...

RangeMin(Today(),  WBR_Event_Date) as  WBR_Event_Date, 

...

Where I have supposed that WBR_Event_Date is lower or equal than today except when it is 01/01/2099. If WBR_Event_Date can be greater than today then use a simple if statement in the LOAD: if(WBR_Event_Date = MakeDate(2099), Today(), WBR_Event_Date) or similar

Regards,

Jaime.

[jlampard40]

Thanks!  I can't seem to get it to work.  This is my load script at the moment.  I've had to convert the two date fields (Clock Reset and Event Date) into DD/MM/YYYY as the original data came in the format YYYY-MM-DD and the end user is not happy about viewing dates this way.

Can you advise how I modify this script to find and replace all of the 01/01/2099's as today's date (current date as of day script is run).  Many thanks!

LOAD [Event key] as WBR_Event_Key,
Target as WBR_Target,
[42d_Breach] as WBR_42d_Breach,
[14d_Breach] as WBR_14d_Breach,
[CRIS No.] as WBR_CRIS_No,
Surname as WBR_Surname,
Forenames as WBR_Forenames,
DOB as WBR_DOB,
Mod as WBR_Mod,
Examination as WBR_Examination,
Room as WBR_Room,
date(floor(timestamp([Clock Reset Date])),'DD/MM/YYYY') as WBR_Clock_Reset_Date,
date(floor(timestamp([Event Date])),'DD/MM/YYYY') as WBR_Event_Date,
Days_Waiting as WBR_Days_Waiting,
Appointment as WBR_Appointment,
[Patient Type Des] as WBR_Patient_Type_Des,
Urgency as WBR_Urgency,
GA as WBR_GA,
Cardiac as WBR_Cardiac,
Vascular as WBR_Vascular,
AgeGrp as WBR_AgeGrp,
[42d_Breach_Status] as WBR_42_Breach_Status,
Target_Month as WBR_Target_Month

[jaibau1993 · Accepted solution]

Hi! In red my modifications:

LOAD [Event key] as WBR_Event_Key,
Target as WBR_Target,
[42d_Breach] as WBR_42d_Breach,
[14d_Breach] as WBR_14d_Breach,
[CRIS No.] as WBR_CRIS_No,
Surname as WBR_Surname,
Forenames as WBR_Forenames,
DOB as WBR_DOB,
Mod as WBR_Mod,
Examination as WBR_Examination,
Room as WBR_Room,
date(floor(timestamp([Clock Reset Date])),'DD/MM/YYYY') as WBR_Clock_Reset_Date,
if(Year([Event Date]) = 2099, Today(), date(floor(timestamp([Event Date])),'DD/MM/YYYY')) as WBR_Event_Date,
Days_Waiting as WBR_Days_Waiting,
Appointment as WBR_Appointment,
[Patient Type Des] as WBR_Patient_Type_Des,
Urgency as WBR_Urgency,
GA as WBR_GA,
Cardiac as WBR_Cardiac,
Vascular as WBR_Vascular,
AgeGrp as WBR_AgeGrp,
[42d_Breach_Status] as WBR_42_Breach_Status,
Target_Month as WBR_Target_Month

When a LOAD statement is executed, the table is read row by row. Thus, you only have to check if the content of [Event Date] is 01/01/2099 and modify it if so (you don't need to "find and replace" after the load statement).

Regards,

Jaime.

[jlampard40]

Works a treat!  Thanks so much.  Many thanks.

[jlampard40]

Cheeky addition...if I then wanted to highlight in red font, those dates which are 01/01/2099 (now changed to today's date) is there any way of doing this too?  I just want users to realise that even though there is a date in this field, it's only today's date and NOT an actual event date.  Thanks

[jaibau1993]

You just need a flag that marks whether a date is modified or not

LOAD [Event key] as WBR_Event_Key,
Target as WBR_Target,
[42d_Breach] as WBR_42d_Breach,
[14d_Breach] as WBR_14d_Breach,
[CRIS No.] as WBR_CRIS_No,
Surname as WBR_Surname,
Forenames as WBR_Forenames,
DOB as WBR_DOB,
Mod as WBR_Mod,
Examination as WBR_Examination,
Room as WBR_Room,
date(floor(timestamp([Clock Reset Date])),'DD/MM/YYYY') as WBR_Clock_Reset_Date,
if(Year([Event Date]) = 2099, Today(), date(floor(timestamp([Event Date])),'DD/MM/YYYY')) as WBR_Event_Date,

if(Year([Event Date]) = 2099, 1, 0) as Flag_ModifiedDate
Days_Waiting as WBR_Days_Waiting,
Appointment as WBR_Appointment,
[Patient Type Des] as WBR_Patient_Type_Des,
Urgency as WBR_Urgency,
GA as WBR_GA,
Cardiac as WBR_Cardiac,
Vascular as WBR_Vascular,
AgeGrp as WBR_AgeGrp,
[42d_Breach_Status] as WBR_42_Breach_Status,
Target_Month as WBR_Target_Month

Now use that flag in your charts to build a colouring condition.

Regards,

Jaime.