How join the calendar date in a proper way

[jarokall]

Hello,

I have generated a calendar in a DB... All dates with weekend and holiday flags.

I try to figure out the proper way to join my Calendar date with the date from the clocking system. The clocking table has only working dates, so when I join it together, I lost the weekend and holiday dates. 

I need to table all dates for each ID from the clocking table with data or nulls and then do some scripts/calculations on top of that like attendance (was or was not at work), etc.

Have you please idea how to do it in a proper and fast way?

Thanks

[Calendar]:

LOAD

    `calendar_date`,

    date_stamp,

    `calendar_day`,

    `calendar_work`,

    `calendar_holiday`,

    `calendar_name`;

SQL SELECT 

    `calendar_date`,

    `calendar_date` as date_stamp,

    `calendar_day`,

    `calendar_work`,

    `calendar_holiday`,

    `calendar_name`

FROM calendar.calendar as calendar;

join(Calendar)

LOAD 

date_stamp,

    user_id,

    total_time as working_shift,

    ;

SQL SELECT 

    rsch.date_stamp,

    rsch.user_id,

    rsch.total_time / 3600 as total_time

FROM clocking.recurring_schedule as rsch

;

[Vinay_B]

Hi @jarokall Can you try the below and let me know how it goes?

[Calendar]:
LOAD
`calendar_date`,
date_stamp,
`calendar_day`,
`calendar_work`,
`calendar_holiday`,
`calendar_name`;
SQL SELECT
`calendar_date`,
`calendar_date` as date_stamp,
`calendar_day`,
`calendar_work`,
`calendar_holiday`,
`calendar_name`
FROM calendar.calendar as calendar;

ClockingData:
LOAD
date_stamp,
user_id,
total_time as working_shift
RESIDENT Calendar;

LEFT JOIN(Calendar)
LOAD
date_stamp,
user_id,
total_time as working_shift
FROM clocking.recurring_schedule;

Note: Make sure the field names, such as calendar_date, date_stamp, user_id, and total_time, match the field names in your actual data schema.

[jarokall]

Hi, @Vinay_B 

thanks for the response. I tried your solution but I get this error.

The following error occurred:

Field 'user_id' not found

The error occurred here:

ClockingData: LOAD date_stamp, user_id, total_time as working_shift RESIDENT Calendar

I am sure that the field names match the names in the data schema, but the issue here is probably that I don't have the user_id in the calendar?

Have you please another idea of how I get a table with all dates from the calendar for all user_id?

I tried some concat, and cross joins but everything was heavy or didn't work. Need some easy and lightweight solution. 

Maybe I didn't need to use the whole calendar because I need only a work week without the weekends but when I used only the date stamp from clocking, there are no public holiday dates so when I calculate the workload on a week where is a public holiday, I missed some days and workload percentages are wrong.

Thanks for your help.

[SunilChauhan]

recurring_schedule:

LOAD 

Date(date_stamp,'DD/MM/YYYY') as calendar_date,

date_stamp,

    user_id,

    total_time as working_shift,

    ;

SQL SELECT 

    rsch.date_stamp,

    rsch.user_id,

    rsch.total_time / 3600 as total_time

FROM clocking.recurring_schedule as rsch

;

[Calendar]:

Left Join

LOAD

    `calendar_date`,

    date_stamp,

    `calendar_day`,

    `calendar_work`,

    `calendar_holiday`,

    `calendar_name`;

SQL SELECT 

    `calendar_date`,

    `calendar_date` as date_stamp,

    `calendar_day`,

    `calendar_work`,

    `calendar_holiday`,

    `calendar_name`

FROM calendar.calendar as calendar;

hope this helps