Create shift based on next date

[firoz24]

Hi all,

I am trying to create three shifts 

1st shift starts - 7:0:0 to 15:29

2nd shift - 15:30 to 23:59:59

3rd shifts - 24:00:00 to 06:59:59 but the 3rd shift starts from midnight to morning 06:59:59 from next day

this is my SYSDATE Format - 2020-11-07 16:52:00.000 (SET TimestampFormat='YYYY-MM-DD hh:mm:ss.[fff]';)

For eg:- If i click 19/8/2024 should show 1st, 2nd shift and third shift from 20/8/2024 00:00:00 to 06:59:59

// If(
// // Shift 1: From 07:00:00 to 15:29:59 on the same day
// Time(Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss.sss')) >= Time#('07:00:00', 'hh:mm:ss') and
// Time(Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss.sss')) <= Time#('15:29:59', 'hh:mm:ss'),
// '1',

// If(
// // Shift 2: From 15:30:00 to 23:59:59 on the same day
// Time(Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss.sss')) >= Time#('15:30:00', 'hh:mm:ss') and
// Time(Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss.sss')) <= Time#('23:59:59', 'hh:mm:ss'),
// '2',

// If(
// // Shift 3: From 00:00:00 to 06:59:59 on the next day
// Time(Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss.sss')) >= Time#('00:00:00', 'hh:mm:ss') and
// Time(Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss.sss')) <= Time#('06:59:59', 'hh:mm:ss') and
// (Date(Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss.sss')) = Date(Today()) + 1 or
// Date(Timestamp(SYSDATE, 'YYYY-MM-DD hh:mm:ss.sss')) = Date(Today())),
// '3',

// // Handle cases where no shifts match
// Null()
// )
// )
// ) as SYS_Shift,

Check the whole script and see what is the mistake

[marcus_sommer]

I think the approach could be simplified, with something like:

if(frac(SYSDATE) < 1/24*7, 3, if(frac(SYSDATE) >= 1/24*15.5, 2, 1))

[firoz24]

No marcus still couldnt get the third shift properly 

I think there was a issue in understanding let me explain again:- 

as per my data SYSDATE field is in this format 

2020-11-07 16:52:00.000 (SET TimestampFormat='YYYY-MM-DD hh:mm:ss.[fff]';)

For eg:- If i click 19/8/2024 should show 1st, 2nd shift and third shift from 20/8/2024 00:00:00 to 06:59:59

1st shift - 19/8/2024 from morning 07:00:00 to 15:29:59

2nd shift - 19/8/2024 from afternoon 15:30:00 to 23:59:59

3rd shift - 20/8/2024 from midnight 00:00:00 to 06:59:59

so basically the third shift should take from next day +7 hours

[marcus_sommer]

It's not a matter of the shifts else of the date and means you need a second date-field covering your shift-requirements. Personally I think I would create the shifts like above and those three fields:

date(floor(SYSDATE)) as DateKey,
time(frac(SYSDATE)) as TimeKey,
floor(SYSDATE) * 100 + hour(SYSDATE) as ShiftDateKey

and connecting them with a TimeTable and two Calendars whereby the ShiftCalendar contains the date-overlapping dates, maybe like:

ShiftCalendar:
load *, if(H < 7, date(BaseDate + 1), BaseDate as ShiftDate;
load *, BaseDate * 100 + iterno() - 1 as ShiftDateKey, iterno() as H while iterno() < 24;
load date(StartDate + recno()) as BaseDate autogenerate EndDate - StartDate + 1;

[firoz24]

So you mean I should create two calendar and link it with the fact table right ? 

but can you explain in clear how to create separate calendar for shift 

taking next date plus 7 hrs as the 3rd shift 

[marcus_sommer]

The above example of a ShiftCalendar should already serve the purpose of providing the overlapping date and the shift-information itself is within the facts.

Maybe it needs some technically and logically adjustments as it is just a starting-point of an idea without testing if everything is working at once. Further you may also include the shifts within the ShiftCalendar but then you would need to include the minutes there as well as within the key to the facts.