topic Re: How join the calendar date in a proper way in App Development

How join the calendar date in a proper way

jarokall — Fri, 15 Nov 2024 21:40:41 GMT

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

;

Re: How join the calendar date in a proper way

Vinay_B — Thu, 15 Jun 2023 14:09:36 GMT

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.

Re: How join the calendar date in a proper way

jarokall — Fri, 16 Jun 2023 05:42:06 GMT

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.

Re: How join the calendar date in a proper way

SunilChauhan — Fri, 16 Jun 2023 06:33:14 GMT

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