Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a table that has the following fields:
- ID,
- EmployeeId,
- ProposedStartDate,
- ProposedEndDate,
- ProposedStartTime,
- ProposedEndTime,
- ActualStartDate,
- ActualEndDate,
- ActualStartTime,
- ActualEndTime.
Example Data:
ID [1], (recordid)
EmployeeId [20154]
ProposedStartDate [05/11/2012]
ProposedEndDate [06/11/2012]
ProposedStartTime [18:30:00]
ProposedEndTime [03:30:00]
ActualStartDate [05/11/2012]
ActualEndDate [06/11/2012]
ActualStartTime [18:45:00]
ActualEndTime [03:40:00]
The load statement is simply:
LOAD *
SELECT * FROM SCHEDULE;
What I am after is two new fields to be loaded that calculate:
1) If the employee started late.
2) If the employee left early.
The logic for starting late (1) is probably like:
if [ActualStartDate & ActualStartTime] > [ProposedStartDate & ProposedStartTime] they started late else they started on time or early.
The logic for leaving early (2) is probably like:
if [ActualEndDate& ActualEndTime] < [ProposedEndDate & ProposedEndTime] they left early else they left on time or late.
The two new fields should only contain Yes/No if they started late/left early.
I have tried:
if(TIME(ActualStartTime,'hh:mm:ss') > TIME(ProposedStartTime,'hh:ss:ss'),'YES','NO')
But this has given me no luck and does not take into account date.
Any help would be appreciated.
Regards,
Jim
try to make timestamp for start and end time and do condition like this:
if(timestamp(timestamp#(ProposedStartDate&' '&ProposedStartTime,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss') < timestamp(timestamp#(ActualStartDate&' '&ActualStartTime,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss'), 'started late', 'start on time') as status
try to make timestamp for start and end time and do condition like this:
if(timestamp(timestamp#(ProposedStartDate&' '&ProposedStartTime,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss') < timestamp(timestamp#(ActualStartDate&' '&ActualStartTime,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss'), 'started late', 'start on time') as status
Hello Pari Pari,
Have added the code and modified to fit with document:
if(timestamp(timestamp#(SCHEDULE.ACTSTARTDATE&' '&SCHEDULE.ACTSTARTTIME,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss') < timestamp(timestamp#(SWIPES.STARTDATE&' '&SWIPES.STARTTIME,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy hh:mm:ss'), 'started late', 'start on time') as LATEOUT2,
However, all results come back as 'start on time' even if they did not.
Any other further thoughts?
Regards,
Jim
Check the formats you used it should be DD/MM/YYYY hh:mm:ss which is case sensitive
Hello Celambarasan
Yes was doing that just as you posted actually. Also, I added Time() to the attributes also. This now works which is fantastic!
Final code below (but can probably lose the TIME() surrounding the attributes).
if(timestamp(timestamp#(TIME(SCHEDULE.ACTSTARTDATE,'DD/MM/YYYY')&' '&TIME(SCHEDULE.ACTSTARTTIME,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') < timestamp(timestamp#(TIME(SWIPES.STARTDATE,'DD/MM/YYYY')&' '&TIME(SWIPES.STARTTIME,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'), 'LATE', 'ON TIME/EARLY') as LATEIN
Thank you both.
I will be giving correct answer to Pari Pari as 95% of the code was in that post and all that was missing was the upper case and possibly the TIME() on the attributes.
Regards,
Jim
Hello Pari Pari,
I have modified the code that you sent to include additional TIME() surrounding the variables and uppercase on the DDMMYYYY which has now made this work.
Code below:
if(timestamp(timestamp#(TIME(SCHEDULE.ACTSTARTDATE,'DD/MM/YYYY')&' '&TIME(SCHEDULE.ACTSTARTTIME,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') < timestamp(timestamp#(TIME(SWIPES.STARTDATE,'DD/MM/YYYY')&' '&TIME(SWIPES.STARTTIME,'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'), 'LATE', 'ON TIME/EARLY') as LATEIN
Thank you so much,
Jim
I think this could be simplified:
if(SCHEDULE.ACTSTARTDATE+SCHEDULE.ACTSTARTTIME < SWIPES.STARTDATE+ SWIPES.STARTTIME, 'LATE', 'ON TIME/EARLY') as LATEIN
or as working sample:
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
ExampleData:
LOAD *,
if(ProposedStartDate + ProposedStartTime < ActualStartDate+ActualStartTime, 'LateStart','OnTime') as LateStart,
if(ProposedEndDate + ProposedEndTime < ActualEndDate+ActualEndTime, 'OnTime','EarlyLeave') as EarlyLeave
INLINE [
ID, EmployeeId, ProposedStartDate, ProposedEndDate, ProposedStartTime, ProposedEndTime, ActualStartDate, ActualEndDate, ActualStartTime, ActualEndTime
1, 20154, 05/11/2012, 06/11/2012,18:30:00,03:30:00,05/11/2012,06/11/2012,18:45:00,03:40:00
];
Hello Swuehl
I will give this a go tomorrow morning, but I am just happy this is working currently (even though not as efficient as yours).
Thank you and I will give yours a go.
Best regards,
Jim