count - Flag if date is between start date and end date

[sjhussain]

Hello,

Hope all is well with everyone.

We have two tables - master calendar and vehicle activity table

The vehicle activity table has vehicle number, start and end date

VEHICLE:

LOAD * INLINE

[VEHICLE_NO, STARTDATE, ENDDATE

VIN1, 01/01/2017, 01/05,2017

VIN2, 01/10/2017, 01/15/2017

VIN3, 01/01/2017, 01/15/2017

VIN4, 01/01/2017, 01/20/2017

VIN5, 01/01/2017, 01/25/2017

VIN6, 01/01/2017, 01/30/2017

We want to find the count of how many vehicles are available on certain date depending on the selection (year, month, day)

for example if we select 01/08/2017 then it should give the following count

VIN1, 01/01/2017, 01/05,2017 - NOT AVAILABLE because 01/08/2017 is after the ENDDATE of 01/05/2017

VIN2, 01/10/2017, 01/15/2017 - NOT AVAILABLE as 01/08/2017 is before the STARTDATE of 01/10/2017

VIN3, 01/01/2017, 01/15/2017 - AVAILABLE as 01/08/2017 is between START and END DATE

VIN4, 01/01/2017, 01/20/2017 - AVAILABLE as 01/08/2017 is between START and END DATE

VIN5, 01/01/2017, 01/25/2017 - AVAILABLE as 01/08/2017 is between START and END DATE

VIN6, 01/01/2017, 01/30/2017 - AVAILABLE as 01/08/2017 is between START and END DATE

So count of vehicles on 01/08/2017 is going to be 4

Will appreciate if someone can assist us.

Thanks.

[its_anandrjs · Accepted solution]

Try this way and generate missing rows in between Start Date and end Date

VEHICLE:

LOAD

Date( num(STARTDATE) +IterNo() -1) as Date,

VEHICLE_NO,Date(Date#( STARTDATE,'MM/DD/YYYY'),'MM/DD/YYYY') AS STARTDATE,Date( Date#( ENDDATE,'MM/DD/YYYY'),'MM/DD/YYYY') as ENDDATE INLINE

[

VEHICLE_NO, STARTDATE, ENDDATE

VIN1, 01/01/2017, 01/05/2017

VIN2, 01/10/2017, 01/15/2017

VIN3, 01/01/2017, 01/15/2017

VIN4, 01/01/2017, 01/20/2017

VIN5, 01/01/2017, 01/25/2017

VIN6, 01/01/2017, 01/30/2017

] While Date( Num(STARTDATE) +IterNo() -1) <= ENDDATE;

And on the UI just select the date you want and on any text object show the count of the Vehicle.

Output:-

[sjhussain]

‌Thanks a lot. This worked perfectly.