Data between two date and time

[sudhir0538]

Hello Community,

I've data of vehicle movement which includes gate in & gate out date and time in different fields and requirement is to generate a report how many for vehicle movement how many in and how many out day wise.In my company the day is calculated as from previous day 6AM to next day 6AM.

Can any one guide how to do it? Sample data attached.

[Shubham_Deshmukh]

Which count do you want? GPQTY?

[Shubham_Deshmukh]

Try this :

=INTERVAL(DATE(GATEOUTDATE+ GATEOUTTIME)-DATE(GEDATE + [GETIME]))

OR if you want it to show with minutes, you can add parameter like : 

=INTERVAL(DATE(GATEOUTDATE+ GATEOUTTIME)-DATE(GEDATE + [GETIME]),'mm')

- Shubham

[Vegar]

To get the date right you will need to create an master calendar, but your will need to substract six hours from the timestamp when generating your transaction table date key. 

Example below: 

Transactions:
LOAD 
	floor(Timestamp - interval#(6,'h')) as %date , //skew 6 hours
	Timestamp, 
	Animal
INLINE [
Timestamp,Animal
2019-03-06 01:18:29,Common brushtail possum
2019-03-06 03:55:31,Indian leopard
2019-03-06 05:44:54,Netted rock dragon
2019-03-06 06:22:29,Grey mouse lemur
2019-03-06 07:25:58,Javanese cormorant
2019-03-05 10:59:51,"Sandgrouse, yellow-throated"
2019-03-06 05:31:29,"Reedbuck, bohor"
2019-03-06 06:56:49,,Pied crow
2019-03-06 07:26:09,"Booby, blue-footed"
2019-03-05 00:07:22,Komodo dragon
2019-03-05 07:12:26,Common boubou shrike
2019-03-06 06:55:49,"Siskin, pine"
2019-03-06 23:52:16,Thomson's gazelle
];

Calendar:
LOAD 
  num(Date) as %date,
  Date,
  year(Date) as Year,
  Week(Date) as Week,
  Weekday(Date) as Weekday
INLINE [
  Date
  2019-03-06
  2019-03-05
  2019-03-04
];

[HirisH_V7]

Hi Check out this below code,

Data:
Load *,
If(Floor(INTIME)=Floor(OUTTIME) and Hour(INTIME)<6,'1 D  ' &  Interval(OUTTIME-INTIME,'hh:mm:ss'),
If(Floor(INTIME)<>Floor(OUTTIME) and Hour(INTIME)<6,Floor(OUTTIME)-Floor(INTIME-1)  &' D ' &  Interval(OUTTIME-INTIME,'hh:mm:ss'),
Floor(OUTTIME)-Floor(INTIME)  &' D ' &  Interval(OUTTIME-INTIME,'hh:mm:ss')))
as Interval_Diff;

LOAD 
Timestamp(GEDATE + GETIME,'DD-MM-YYYY hh:mm:ss') as INTIME,
If(Isnull(Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss')),TimeStamp(Now(),'DD-MM-YYYY hh:mm:ss'),
Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss'))as OUTTIME,
GEDATE, GETIME, GATEENTRYNO, GATENO, GPQTY, GATEOUTDATE, GATEOUTTIME
FROM [Gate In Out.xlsx] (ooxml, embedded labels, table is Sheet1);

From the sample data provided, created above. It will give the Interval in time as well as no of days in between. Based on your below 6 and above 6 logic.

If required as calendar means,let me know all occurrences of this. we can use daystart function for such cases.

Anyway, above doesn't work means. let me know your desired output for all the occurrences.

PFA QVW For ref.

HTH,

Hirish

[sudhir0538]

Hi Subham, I want to count how many vehicles are In and How many vehicles are out. Suppose for this day 19.03.2019 we have to count frim 18.03.2019 morning 6AM to 19.03.2019 6AM.

Thank You for suggestions above !!

[sudhir0538]

Hi Hirish, I want to count how many vehicles are In and How many vehicles are out. Suppose for this day 19.03.2019 we have to count frim 18.03.2019 morning 6AM to 19.03.2019 6AM.

Please help to get that !! Thank you for all the suggestions !!

[HirisH_V7 · Accepted solution]

Hi Check this revised code and output as desired.

Data:
LOAD 
Timestamp(GEDATE + GETIME,'DD-MM-YYYY hh:mm:ss') as INTIME,
If(Isnull(Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss')),TimeStamp(Now(),'DD-MM-YYYY hh:mm:ss'),
Timestamp(GATEOUTDATE + GATEOUTTIME,'DD-MM-YYYY hh:mm:ss'))as OUTTIME,
GEDATE, GETIME, GATEENTRYNO, GATENO, GPQTY, GATEOUTDATE, GATEOUTTIME
FROM [Gate In Out.xlsx] (ooxml, embedded labels, table is Sheet1);

Min_Max:
Load Min(INTIME) as vINMin,
     Max(INTIME) as vINMax,
     Min(OUTTIME) as vOUTMin,
     Max(OUTTIME) as vOUTMax
     Resident Data;
     

Let vINMax = Timestamp(Date(Floor(Peek('vINMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vINMin = Timestamp(Date(Floor(Peek('vINMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMax = Timestamp(Date(Floor(Peek('vOUTMax')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Let vOUTMin = Timestamp(Date(Floor(Peek('vOUTMin')))+ '06:00:00','DD-MM-YYYY hh:mm:ss');

Drop table Min_Max;

Out put in UI: