Hi Berkay,
I like to solve this kind of problem with a smart database, like PostgreSQL, where I can define a pattern and repeat it with table generating functions. I tweaked one of my implementations to serve as an example:
create table shift_schedules (shift_id serial,
shift_name varchar(255),
refdate date,
repeat int,
tzname varchar(255),
primary key (shift_id));
insert into shift_schedules (shift_name, refdate, repeat, tzname)
values ('Berkay Cinar 3wk', '2021-07-26', 21, 'America/New_York');
create table shift_schedule_pattern (shift_id int,
seq int,
pseq int,
start time,
shift varchar(16),
primary key (shift_id, seq, start));
insert into shift_schedule_pattern (shift_id, seq, pseq, start, shift)
values
(1, 0, 0, '07:30:00', 'B')
, (1, 0, 0, '15:30:00', 'A')
, (1, 0, 0, '23:30:00', 'C')
, (1, 1, 1, '07:30:00', 'B')
, (1, 1, 1, '15:30:00', 'A')
, (1, 1, 1, '23:30:00', 'C')
, (1, 2, 2, '07:30:00', 'B')
, (1, 2, 2, '15:30:00', 'A')
, (1, 2, 2, '23:30:00', 'C')
, (1, 3, 3, '07:30:00', 'B')
, (1, 3, 3, '15:30:00', 'A')
, (1, 3, 3, '23:30:00', 'C')
, (1, 4, 4, '07:30:00', 'B')
, (1, 4, 4, '15:30:00', 'A')
, (1, 4, 4, '23:30:00', 'C')
, (1, 5, 5, '07:30:00', NULL)
, (1, 6, 6, '07:30:00', NULL)
, (1, 7, 7, '07:30:00', 'A')
, (1, 7, 7, '15:30:00', 'C')
, (1, 7, 7, '23:30:00', 'B')
, (1, 8, 8, '07:30:00', 'A')
, (1, 8, 8, '15:30:00', 'C')
, (1, 8, 8, '23:30:00', 'B')
, (1, 9, 9, '07:30:00', 'A')
, (1, 9, 9, '15:30:00', 'C')
, (1, 9, 9, '23:30:00', 'B')
, (1, 10, 10, '07:30:00', 'A')
, (1, 10, 10, '15:30:00', 'C')
, (1, 10, 10, '23:30:00', 'B')
, (1, 11, 11, '07:30:00', 'A')
, (1, 11, 11, '15:30:00', 'C')
, (1, 11, 11, '23:30:00', 'B')
, (1, 12, 12, '07:30:00', NULL)
, (1, 13, 13, '07:30:00', NULL)
, (1, 14, 14, '07:30:00', 'C')
, (1, 14, 14, '15:30:00', 'B')
, (1, 14, 14, '23:30:00', 'A')
, (1, 15, 15, '07:30:00', 'C')
, (1, 15, 15, '15:30:00', 'B')
, (1, 15, 15, '23:30:00', 'A')
, (1, 16, 16, '07:30:00', 'C')
, (1, 16, 16, '15:30:00', 'B')
, (1, 16, 16, '23:30:00', 'A')
, (1, 17, 17, '07:30:00', 'C')
, (1, 17, 17, '15:30:00', 'B')
, (1, 17, 17, '23:30:00', 'A')
, (1, 18, 18, '07:30:00', 'C')
, (1, 18, 18, '15:30:00', 'B')
, (1, 18, 18, '23:30:00', 'A')
, (1, 19, 19, '07:30:00', NULL)
, (1, 20, 20, '07:30:00', NULL);
CREATE OR REPLACE FUNCTION production_shifts_for_range(
sid int,
begints timestamptz,
endts timestamptz)
RETURNS TABLE(shift_name varchar,
tzname varchar,
proddate date,
shift varchar,
beginat timestamptz,
endat timestamptz)
AS $$
WITH mult_estimates AS (
SELECT ((begints::timestamptz at time zone tzname)::date-refdate)/repeat-1 AS beginmult,
((endts::timestamptz at time zone tzname)::date-refdate)/repeat+1 AS endmult
FROM shift_schedules sscte
WHERE sscte.shift_id = sid
),
gross_filter AS (
SELECT shift_name, tzname, proddate, shift, boundary as beginat,
lead(boundary) over (ORDER BY boundary) as endat
FROM (
SELECT ss.shift_name,
ss.tzname,
ss.refdate + (mult * ss.repeat) + ssp.pseq as proddate,
ssp.shift,
(ss.refdate + (mult * ss.repeat) + ssp.seq + ssp.start) at time zone ss.tzname as boundary
FROM (SELECT * FROM shift_schedules WHERE shift_id = sid) ss
INNER JOIN shift_schedule_pattern ssp ON ss.shift_id = ssp.shift_id,
generate_series((SELECT beginmult FROM mult_estimates)::int, (SELECT endmult FROM mult_estimates)::int) as mult
) subq
)
SELECT *
FROM gross_filter gf
WHERE gf.proddate >= (SELECT gfb.proddate FROM gross_filter gfb WHERE begints >= gfb.beginat AND begints < gfb.endat)
AND gf.proddate <= (SELECT gfe.proddate FROM gross_filter gfe WHERE endts >= gfe.beginat AND endts < gfe.endat)
$$ LANGUAGE SQL;
Select * From production_shifts_for_range(1, current_timestamp, current_timestamp)
Where current_timestamp between beginat and endat
ORDER BY beginat;
Select * From production_shifts_for_range(1, current_timestamp, current_timestamp)
ORDER BY beginat;
For that last query, you get results like this:
Note that this custom table-generating function can be use with wider timespans to give you every shift within a given month or year, joinable to your production records. You can group such data on shift and variations of the production date for all the stats you need to satisfy the bean counters.
