Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:-
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:-
Thanks a lot. This worked perfectly.