What I'm doing is based on Shift table as interval match is looking in CAQ table and taking distinct count of serial number:
My Night Shift numbers are 100% matching with data source with below logic
IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp
But number are not matching for Day shift.
Shift Table :
num(Start_Hour) as Start,
num(End_Hour - Time#('00:00:01'),'0,0000000') as End,
where End > Start;
where Start > End;
1 as End,
0 as Start,
drop tables Shift, Shift_Night;
date(floor(Date.tmp)) as Date,
num(frac(time(Date.tmp)),'0,0000000')-0.0000001 as Time, // without substraction of 0.0000001 the time 21:59:59 was not matched?!
year(Date.tmp) as Year,
month(Date.tmp) as Month,
week(Date.tmp) as Week,
Day(Date.tmp) as Day,
num(Date.tmp) as NumDate,
//dual(year(Date.tmp)&'-'&'Q'&ceil(month(Date.tmp)/3),year(Date.tmp)&ceil(month(Date.tmp)/3)) as Year_Quarter,
// dual(year(Date.tmp)&'-'&month(Date.tmp),year(Date.tmp)&num(month(Date.tmp),'00')) as Year_Month,
// dual(year(Date.tmp)&'-'&week(Date.tmp),year(Date.tmp)&num(week(Date.tmp),'00')) as Year_Week,
hour(Date.tmp2) as Time.tmp,
IF(hour(Date.tmp2) < 8.45, Date.tmp2-1,Date.tmp2) as Date.tmp // Logic for night shift
num(Date) as Date.tmp2,
timestamp(Date) as Date_Org,
left join (CAQ)
drop table Shifts;
You requirement is not clear.
>>What shall I should minus from below to get 20:45:00????
>> num(frac(time(Date.tmp)),'0,0000000') - XXXXXXX ?
Well it depends on the value of Date,tmp. I am not sure what you are trying to do. To get the value of time 20:45, use
Not quite sure if I'm getting the question right but it seems to me that you can use a FLOOR statement in your script
When you use time or datetime it only displays the number in another format it doesn't drop the decimal values after the seconds.
DATETIME(FLOOR(Date.tmp *24*60*60)/ (24*60*60))
Floor is a function which round the values to the first lower integers, so 5.7 = 5.
A date is an integer, while the hours, minutes and seconds are decimal values. Multiplying with 24 makes the hours integers, when adding a multiply of 60 the minutes are integers etc.
So floor(date.tmp*24*60*60) / (24*60*60) rounds your date to seconds