Load date based on predefined Times

[anuradhaa]

Hi,

My script loads every one hour. here i want to load the data with the check on event time and reload time.

ex - I have three predefined hours   

06:30 , 14:30 and 22:30

if my script loads 10:00 AM i want to load all events happen before 06:30 AM

if my script loads 06:00 PM i want to load all events happen before 02:30 PM

if my script loads 11:00 PM i want to load all events happen before 10:30 PM

I take hours as cut off limits.

Please help me to do this

[Gysbert_Wassenaar]

Maybe something like this:

LET vTime = num(pick(match(Hour(Now()),'10','18','23'), MakeTime(6,30), MakeTime(14,30), MakeTime(22,30)));

MyData:

LOAD *

FROM MySource

WHERE MyTime < $(vTime);

[anuradhaa]

Thanks.

But here script load times may very and i want to check what is the closed time to script load time.

Then select that close time from 06.30,14.30,22.30 and load date grater than that time

[Gysbert_Wassenaar]

LET vTime = num(If(Hour(Now())<10,MakeTime(6,30),If(Hour(Now())<18,MakeTime(14,30),If(Hour(Now())<23,MakeTime(22,30)))));

[anuradhaa]

when loading script it says

Field not found - <<>

[HRM]:

  LOAD

  MOV_ID as hmp_MOV_ID,

hmp_num,

HMP_time

RESIDENT  HISTORY where HMP <

[Gysbert_Wassenaar · Accepted solution]

Yeah, there were some typos in my last post. Try again with this one:

LET vTime = num(If(Hour(Now())<10,MakeTime(6,30),If(Hour(Now())<18,MakeTime(14,30),If(Hour(Now())<23,MakeTime(22,30)))));

[anuradhaa]

another one more thing ,

if i want to categorize basd on shift times how can i do that,

I have tried

if(mydate<= $(vTime) - Hour(8) , 'S1',if(mydate> $(vTime) -Hour(8) and mydate <= $(vTime) - Hour(16) , 'S2',if(mydate > $(vTime) - Hour(16) and mydate<= $(vTime) - Hour(24) , 'S3'))) as shifts

in script but not work

[Gysbert_Wassenaar]

Maybe like this: if(frac(mydate)< 1/3, 'S1', if(frac(mydate)<2/3,'S2', 'S3')) as shift. 8 hours is one third of a day, 8 divided by 24 or 1/3.

[anuradhaa]

But my shift times are different 06:30 , 14:30 and 22:30

[Gysbert_Wassenaar]

Then you'll need to adapt the expression so it fits your needs. If you know how to perform divisions then this shouldn't be any problem. what is 6.5 divided by 24?