-- File: doc_plan_code.sql -- Version: 2.0 -- Last Changed: 2020-04-19 -- by: Damir; https://www.damirsystems.com -- Project: Doctor Planning -- Description: Fun challenge from Decision Management Community -- https://dmcommunity.org/challenge/challenge-apr-2020/ -- -- DB: PostgreSQL -- ----------------- -- ### Domains ### -- ----------------- -- Days CREATE TYPE day_name AS ENUM ('SUN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT'); CREATE DOMAIN day_no AS integer CHECK (VALUE between 1 and 7); -- Daily Shifts CREATE TYPE shift_name AS ENUM ('Early', 'Late', 'Night'); CREATE DOMAIN shift_no AS integer CHECK (VALUE between 1 and 3); -- Weekly shifts CREATE TYPE week_shift_name AS ENUM ( 'MON-1', 'MON-2', 'MON-3' , 'TUE-1', 'TUE-2', 'TUE-3' , 'WED-1', 'WED-2', 'WED-3' , 'THU-1', 'THU-2', 'THU-3' , 'FRI-1', 'FRI-2', 'FRI-3' , 'SAT-1', 'SAT-2', 'SAT-3' , 'SUN-1', 'SUN-2', 'SUN-3' ); CREATE DOMAIN week_shift_no AS integer CHECK (VALUE between 1 and 21); -- Doctors CREATE DOMAIN person_name AS text CHECK (length(VALUE) >= 1 AND length(VALUE) <= 20); CREATE TYPE doc_initials AS ENUM ('AF', 'SF', 'HH', 'BE', 'CG'); -- ---------------------------------- -- ### Predicates and Relations ### -- ---------------------------------- -- [p 1] -- Day of week DY number DYNO exists. -- CREATE TABLE day_wk ( DY day_name NOT NULL , DYNO day_no NOT NULL , CONSTRAINT pk_day_wk PRIMARY KEY (DY) , CONSTRAINT ak_day_wk UNIQUE (DYNO) ); INSERT INTO day_wk (DY, DYNO) VALUES ('MON', 1) , ('TUE', 2) , ('WED', 3) , ('THU', 4) , ('FRI', 5) , ('SAT', 6) , ('SUN', 7) ; -- [p 2] -- Shift SFT with number SNO exists. -- CREATE TABLE shift ( SFT shift_name NOT NULL , SNO shift_no NOT NULL , CONSTRAINT pk_shift PRIMARY KEY (SFT) , CONSTRAINT ak_shift UNIQUE (SNO) ); INSERT INTO shift (SFT, SNO) VALUES -- (R03) ('Early', 1) , ('Late', 2) , ('Night', 3) ; -- [p 3] -- Week shift WSFT, with week shift number WSNO, -- of day DY shift SFT exists. -- CREATE TABLE week_shift ( WSFT week_shift_name NOT NULL , WSNO week_shift_no NOT NULL , DY day_name NOT NULL , SFT shift_name NOT NULL , CONSTRAINT pk_week_shift PRIMARY KEY (WSFT) , CONSTRAINT ak1_week_shift UNIQUE (WSNO) , CONSTRAINT ak2_week_shift UNIQUE (DY, SFT) , CONSTRAINT fk1_week_shift FOREIGN KEY (DY) REFERENCES day_wk (DY) , CONSTRAINT fk2_week_shift FOREIGN KEY (SFT) REFERENCES shift (SFT) ); INSERT INTO week_shift (WSFT, WSNO, DY, SFT) VALUES ('MON-1', 1, 'MON', 'Early') , ('MON-2', 2, 'MON', 'Late' ) , ('MON-3', 3, 'MON', 'Night') , ('TUE-1', 4, 'TUE', 'Early') , ('TUE-2', 5, 'TUE', 'Late' ) , ('TUE-3', 6, 'TUE', 'Night') , ('WED-1', 7, 'WED', 'Early') , ('WED-2', 8, 'WED', 'Late' ) , ('WED-3', 9, 'WED', 'Night') , ('THU-1', 10, 'THU', 'Early') , ('THU-2', 11, 'THU', 'Late' ) , ('THU-3', 12, 'THU', 'Night') , ('FRI-1', 13, 'FRI', 'Early') , ('FRI-2', 14, 'FRI', 'Late' ) , ('FRI-3', 15, 'FRI', 'Night') , ('SAT-1', 16, 'SAT', 'Early') , ('SAT-2', 17, 'SAT', 'Late' ) , ('SAT-3', 18, 'SAT', 'Night') , ('SUN-1', 19, 'SUN', 'Early') , ('SUN-2', 20, 'SUN', 'Late' ) , ('SUN-3', 21, 'SUN', 'Night') ; -- [p 4] -- Doctor DOC_NAME with initials DOC -- works for the hospital. -- CREATE TABLE doctor ( DOC_NAME person_name NOT NULL , DOC doc_initials NOT NULL , CONSTRAINT pk_doc PRIMARY KEY (DOC) , CONSTRAINT ak_doc UNIQUE (DOC_NAME) ); INSERT INTO doctor (DOC_NAME, DOC) VALUES -- (R05) ('Fleming', 'AF') , ('Freud', 'SF') , ('Heimlich', 'HH') , ('Eustachi', 'BE') , ('Golgi', 'CG') ; -- [p 5] -- Doctor DOC is available for week shift WSFT. -- CREATE TABLE doctor_available ( DOC doc_initials NOT NULL , WSFT week_shift_name NOT NULL , CONSTRAINT pk_doctor_available PRIMARY KEY (DOC, WSFT) , CONSTRAINT fk1_doctor_available FOREIGN KEY (DOC) REFERENCES doctor (DOC) , CONSTRAINT fk2_doctor_available FOREIGN KEY (WSFT) REFERENCES week_shift (WSFT) ); INSERT INTO doctor_available (DOC, WSFT) SELECT DOC, WSFT FROM doctor CROSS JOIN week_shift WHERE (1=0) -- (R10) Fleming: Friday, Saturday, Sunday. OR (DOC = 'AF' AND DY IN ('FRI','SAT','SUN')) -- (R11) Freud: Every day early or late, never night. OR (DOC = 'SF' AND SFT IN ('Early','Late')) -- (R12) Heimlich: Every day, but never the night shift on weekends. OR (DOC = 'HH' AND NOT (SFT = 'Night' AND DY IN ('SAT','SUN'))) -- (R13) Eustachi: Every day, every shift. OR (DOC = 'BE') -- (R14) Golgi: Every day, every shift. OR (DOC = 'CG') ; -- [p 6] -- According to the schedule (plan) number PLN: -- doctor "MON-1" is scheduled for Monday early shift, -- doctor "MON-2" is scheduled for Monday late shift, -- doctor "MON-3" is scheduled for Monday night shift, -- doctor "TUE-1" is scheduled for Tuesday early shift, -- ... -- doctor "SUN-1" is scheduled for Sunday early shift. -- doctor "SUN-2" is scheduled for Sunday late shift. -- doctor "SUN-3" is scheduled for Sunday night shift. -- CREATE MATERIALIZED VIEW schedule AS SELECT row_number() OVER () AS pln , mon1.DOC AS "MON-1" , mon2.DOC AS "MON-2" , mon3.DOC AS "MON-3" , tue1.DOC AS "TUE-1" , tue2.DOC AS "TUE-2" , tue3.DOC AS "TUE-3" , wed1.DOC AS "WED-1" , wed2.DOC AS "WED-2" , wed3.DOC AS "WED-3" , thu1.DOC AS "THU-1" , thu2.DOC AS "THU-2" , thu3.DOC AS "THU-3" , fri1.DOC AS "FRI-1" , fri2.DOC AS "FRI-2" , fri3.DOC AS "FRI-3" , sat1.DOC AS "SAT-1" , sat2.DOC AS "SAT-2" , sat3.DOC AS "SAT-3" , sun1.DOC AS "SUN-1" , sun2.DOC AS "SUN-2" , sun3.DOC AS "SUN-3" FROM doctor_available AS mon1 JOIN doctor_available AS mon2 ON mon2.WSFT = 'MON-2' AND mon2.DOC != mon1.DOC -- (R06) JOIN doctor_available AS mon3 ON mon3.WSFT = 'MON-3' AND mon3.DOC NOT IN (mon1.DOC, mon2.DOC) -- (R06) -- Tuesday JOIN doctor_available AS tue1 ON tue1.WSFT = 'TUE-1' AND tue1.DOC != mon3.DOC -- (R08) JOIN doctor_available AS tue2 ON tue2.WSFT = 'TUE-2' AND tue2.DOC NOT IN (tue1.DOC, mon3.doc) -- (R06),(R08) JOIN doctor_available AS tue3 ON tue3.WSFT = 'TUE-3' AND tue3.DOC NOT IN (tue1.DOC, tue2.DOC) -- (R06) -- Wednesday JOIN doctor_available AS wed1 ON wed1.WSFT = 'WED-1' AND wed1.DOC != tue3.DOC -- (R08) JOIN doctor_available AS wed2 ON wed2.WSFT = 'WED-2' AND wed2.DOC NOT IN (wed1.DOC, tue3.DOC) -- (R06),(R08) JOIN doctor_available AS wed3 ON wed3.WSFT = 'WED-3' AND wed3.DOC NOT IN (wed1.DOC, wed2.DOC) -- (R06) -- Thursday JOIN doctor_available AS thu1 ON thu1.WSFT = 'THU-1' AND thu1.DOC != wed3.DOC -- (R08) JOIN doctor_available AS thu2 ON thu2.WSFT = 'THU-2' AND thu2.DOC NOT IN (thu1.DOC, wed3.DOC) -- (R06),(R08) JOIN doctor_available AS thu3 ON thu3.WSFT = 'THU-3' AND thu3.DOC NOT IN (thu1.DOC, thu2.DOC) -- (R06) -- Friday JOIN doctor_available AS fri1 ON fri1.WSFT = 'FRI-1' AND fri1.DOC != thu3.DOC -- (R08) JOIN doctor_available AS fri2 ON fri2.WSFT = 'FRI-2' AND fri2.DOC NOT IN (fri1.DOC, thu3.DOC) -- (R06),(R08) JOIN doctor_available AS fri3 ON fri3.WSFT = 'FRI-3' AND fri3.DOC NOT IN (fri1.DOC, fri2.DOC) -- (R06) -- Saturday JOIN doctor_available AS sat1 ON sat1.WSFT = 'SAT-1' AND sat1.DOC != fri3.DOC -- (R08) JOIN doctor_available AS sat2 ON sat2.WSFT = 'SAT-2' AND sat2.DOC NOT IN (sat1.DOC, fri3.DOC) -- (R06),(R08) JOIN doctor_available AS sat3 ON sat3.WSFT = 'SAT-3' AND sat3.DOC NOT IN (sat1.DOC, sat2.DOC) -- (R06) -- Sunday JOIN doctor_available AS sun1 ON sun1.WSFT = 'SUN-1' AND sun1.DOC != sat3.DOC -- (R08) AND sun1.DOC IN (sat1.DOC, sat2.DOC) -- (R09) JOIN doctor_available AS sun2 ON sun2.WSFT = 'SUN-2' AND sun2.DOC NOT IN (sun1.DOC, sat3.DOC) -- (R06),(R08) AND sun2.DOC IN (sat1.DOC, sat2.DOC) -- (R09) JOIN doctor_available AS sun3 ON sun3.WSFT = 'SUN-3' AND sun3.DOC NOT IN (sun1.DOC, sun2.DOC) -- (R06) AND sun3.DOC IN (sat1.DOC, sat2.DOC, sat3.DOC) -- (R09) WHERE mon1.WSFT = 'MON-1' AND mon1.DOC != sun3.DOC -- (R08) AND mon2.doc != sun3.DOC -- (R08) -- (R15) Golgi max two night shifts in a week AND ( (CASE WHEN mon3.DOC = 'CG' THEN 1 ELSE 0 END) + (CASE WHEN tue3.DOC = 'CG' THEN 1 ELSE 0 END) + (CASE WHEN wed3.DOC = 'CG' THEN 1 ELSE 0 END) + (CASE WHEN thu3.DOC = 'CG' THEN 1 ELSE 0 END) + (CASE WHEN fri3.DOC = 'CG' THEN 1 ELSE 0 END) + (CASE WHEN sat3.DOC = 'CG' THEN 1 ELSE 0 END) + (CASE WHEN sun3.DOC = 'CG' THEN 1 ELSE 0 END) ) <= 2 WITH NO DATA ; -- ---------------------------------- -- ### How to Choose a Solution ### -- ---------------------------------- -- Helper function for calculating solution stats. -- Quick and dirty, but OK for the example. -- CREATE OR REPLACE FUNCTION f_stats (rw doc_initials[]) RETURNS TABLE ( mns integer -- min shifts per doctor , mxs integer -- max shifts per doctor , drq integer -- required doctors , af integer -- Fleming shifts , sf integer -- Freud shifts , hh integer -- Heimlich shifts , be integer -- Eustachi shifts , cg integer -- Golgi shifts ) AS $BODY$ BEGIN RETURN QUERY WITH q_0 AS ( -- assigned doctors SELECT DOC FROM unnest(rw) AS x(DOC) ), q_1 AS ( -- number of required doctors SELECT count(distinct DOC) AS req FROM q_0 ), q_2 AS ( -- pivot shift count for each possible doctor SELECT sum(CASE DOC WHEN 'AF'::doc_initials THEN 1 ELSE 0 END) as AF , sum(CASE DOC WHEN 'SF'::doc_initials THEN 1 ELSE 0 END) as SF , sum(CASE DOC WHEN 'HH'::doc_initials THEN 1 ELSE 0 END) as HH , sum(CASE DOC WHEN 'BE'::doc_initials THEN 1 ELSE 0 END) as BE , sum(CASE DOC WHEN 'CG'::doc_initials THEN 1 ELSE 0 END) as CG FROM q_0 ) SELECT least(a.af,a.sf,a.hh,a.be,a.cg)::integer AS mns , greatest(a.af,a.sf,a.hh,a.be,a.cg)::integer AS mxs , b.req::integer AS drq , a.af::integer , a.sf::integer , a.hh::integer , a.be::integer , a.cg::integer FROM q_2 as a CROSS JOIN q_1 as b ; END; $BODY$ LANGUAGE plpgsql VOLATILE; -- [p 7] -- According to the schedule (plan) number PLN: -- min number of shifts assigned to a doctor is MNS; -- max number of shifts assigned to a doctor is MXS; -- number of doctors required for that plan is DRQ; -- doctor Fleming is assigned AF shifts; -- doctor Freud is assigned SF shifts; -- doctor Heimlich is assigned HH shifts; -- doctor Eustachi is assigned BE shifts; -- doctor Golgi is assigned CG shifts. -- CREATE MATERIALIZED VIEW stats AS SELECT a.pln , b.mns , b.mxs , b.drq , b.af , b.sf , b.hh , b.be , b.cg FROM schedule AS a LEFT JOIN lateral f_stats( ARRAY[ "MON-1", "MON-2", "MON-3" , "TUE-1", "TUE-2", "TUE-3" , "WED-1", "WED-2", "WED-3" , "THU-1", "THU-2", "THU-3" , "FRI-1", "FRI-2", "FRI-3" , "SAT-1", "SAT-2", "SAT-3" , "SUN-1", "SUN-2", "SUN-3" ]::doc_initials[] ) AS b ON true WITH NO DATA ; -- ----------------------------- -- ### Testing ### -- ----------------------------- -- [p 8] -- There are VIOLATIONS of rule RULE. -- CREATE OR REPLACE VIEW test_rules AS WITH t_01 AS ( -- (R04) (R01) Every shift has to be assigned to a doctor. SELECT count(*) AS "VIOLATIONS" ,'(R04) Every shift has to be assigned to a doctor.' AS "RULE" FROM schedule WHERE "MON-1" IS NULL OR "MON-2" IS NULL OR "MON-3" IS NULL OR "TUE-1" IS NULL OR "TUE-2" IS NULL OR "TUE-3" IS NULL OR "WED-1" IS NULL OR "WED-2" IS NULL OR "WED-3" IS NULL OR "THU-1" IS NULL OR "THU-2" IS NULL OR "THU-3" IS NULL OR "FRI-1" IS NULL OR "FRI-2" IS NULL OR "FRI-3" IS NULL OR "SAT-1" IS NULL OR "SAT-2" IS NULL OR "SAT-3" IS NULL OR "SUN-1" IS NULL OR "SUN-2" IS NULL OR "SUN-3" IS NULL ), t_02 AS ( -- (R06) A doctor can only work one shift per day. SELECT count(*) AS "VIOLATIONS" ,'(R06) A doctor can only work one shift per day.' AS "RULE" FROM schedule WHERE "MON-1" IN ("MON-2", "MON-3") OR "MON-2" IN ("MON-1", "MON-3") OR "MON-3" IN ("MON-1", "MON-2") OR "TUE-1" IN ("TUE-2", "TUE-3") OR "TUE-2" IN ("TUE-1", "TUE-3") OR "TUE-3" IN ("TUE-1", "TUE-2") OR "WED-1" IN ("WED-2", "WED-3") OR "WED-2" IN ("WED-1", "WED-3") OR "WED-3" IN ("WED-1", "WED-2") OR "THU-1" IN ("THU-2", "THU-3") OR "THU-2" IN ("THU-1", "THU-3") OR "THU-3" IN ("THU-1", "THU-2") OR "FRI-1" IN ("FRI-2", "FRI-3") OR "FRI-2" IN ("FRI-1", "FRI-3") OR "FRI-3" IN ("FRI-1", "FRI-2") OR "SAT-1" IN ("SAT-2", "SAT-3") OR "SAT-2" IN ("SAT-1", "SAT-3") OR "SAT-3" IN ("SAT-1", "SAT-2") OR "SUN-1" IN ("SUN-2", "SUN-3") OR "SUN-2" IN ("SUN-1", "SUN-3") OR "SUN-3" IN ("SUN-1", "SUN-2") ), t_03 AS ( -- (R08) If a doctor has a night shift, -- they either get the next day off, or the night shift again. SELECT count(*) AS "VIOLATIONS" ,'(R08) Night shift rule.' AS "RULE" FROM schedule WHERE "SUN-3" IN ("MON-1", "MON-2") OR "MON-3" IN ("TUE-1", "TUE-2") OR "TUE-3" IN ("WED-1", "WED-2") OR "WED-3" IN ("THU-1", "THU-2") OR "THU-3" IN ("FRI-1", "FRI-2") OR "FRI-3" IN ("SAT-1", "SAT-2") OR "SAT-3" IN ("SUN-1", "SUN-2") ), t_04 AS ( -- (R09) A doctor either works both days -- of the weekend, or none of the days. SELECT count(*) AS "VIOLATIONS" ,'(R09) Weekend rule.' AS "RULE" FROM schedule WHERE "SAT-1" NOT IN ("SUN-1", "SUN-2", "SUN-3") OR "SAT-2" NOT IN ("SUN-1", "SUN-2", "SUN-3") OR "SAT-3" NOT IN ("SUN-1", "SUN-2", "SUN-3") OR "SUN-1" NOT IN ("SAT-1", "SAT-2", "SAT-3") OR "SUN-2" NOT IN ("SAT-1", "SAT-2", "SAT-3") OR "SUN-3" NOT IN ("SAT-1", "SAT-2", "SAT-3") ), t_05 AS ( -- (R10) Fleming (AF) only Friday, Saturday, Sunday. SELECT count(*) AS "VIOLATIONS" ,'(R10) Fleming only Friday, Saturday, Sunday.' AS "RULE" FROM schedule WHERE "MON-1" = 'AF'::doc_initials OR "MON-2" = 'AF'::doc_initials OR "MON-3" = 'AF'::doc_initials OR "TUE-1" = 'AF'::doc_initials OR "TUE-2" = 'AF'::doc_initials OR "TUE-3" = 'AF'::doc_initials OR "WED-1" = 'AF'::doc_initials OR "WED-2" = 'AF'::doc_initials OR "WED-3" = 'AF'::doc_initials OR "THU-1" = 'AF'::doc_initials OR "THU-2" = 'AF'::doc_initials OR "THU-3" = 'AF'::doc_initials ), t_06 AS ( -- (R11) Freud (SF) Every day early or late, never night. SELECT count(*) AS "VIOLATIONS" ,'(R11) Freud every day early or late, never night.' AS "RULE" FROM schedule WHERE "MON-3" = 'SF'::doc_initials OR "TUE-3" = 'SF'::doc_initials OR "WED-3" = 'SF'::doc_initials OR "THU-3" = 'SF'::doc_initials OR "FRI-3" = 'SF'::doc_initials OR "SAT-3" = 'SF'::doc_initials OR "SUN-3" = 'SF'::doc_initials ), t_07 AS ( -- (R12) Heimlich (HH) Every day, -- but never the night shift on weekends. SELECT count(*) AS "VIOLATIONS" ,'(R12) Heimlich every day, never the night shift on weekends.' AS "RULE" FROM schedule WHERE "SAT-3" = 'HH'::doc_initials OR "SUN-3" = 'HH'::doc_initials ), t_08 AS ( -- (R15) Golgi max 2 nightshifts SELECT count(*) AS "VIOLATIONS" ,'(R15) Golgi max 2 night shifts.' AS "RULE" FROM schedule WHERE ( (case when "MON-3" = 'CG'::doc_initials THEN 1 ELSE 0 END) + (case when "TUE-3" = 'CG'::doc_initials THEN 1 ELSE 0 END) + (case when "WED-3" = 'CG'::doc_initials THEN 1 ELSE 0 END) + (case when "THU-3" = 'CG'::doc_initials THEN 1 ELSE 0 END) + (case when "FRI-3" = 'CG'::doc_initials THEN 1 ELSE 0 END) + (case when "SAT-3" = 'CG'::doc_initials THEN 1 ELSE 0 END) + (case when "SUN-3" = 'CG'::doc_initials THEN 1 ELSE 0 END) ) > 2 ) SELECT "VIOLATIONS", "RULE" FROM t_01 UNION SELECT "VIOLATIONS", "RULE" FROM t_02 UNION SELECT "VIOLATIONS", "RULE" FROM t_03 UNION SELECT "VIOLATIONS", "RULE" FROM t_04 UNION SELECT "VIOLATIONS", "RULE" FROM t_05 UNION SELECT "VIOLATIONS", "RULE" FROM t_06 UNION SELECT "VIOLATIONS", "RULE" FROM t_07 UNION SELECT "VIOLATIONS", "RULE" FROM t_08 ; -- ----------------------------- -- ### IMPORTANT ### -- ----------------------------- -- Run the two REFRESH MATERIALIZED VIEW lines manually to populate -- both views with data. It may take ~15 minutes -- to finish, the stats view is slow to generate. -- Once schedule (plans) and stats are generated, -- it is easy to test and explore. -- REFRESH MATERIALIZED VIEW schedule; -- REFRESH MATERIALIZED VIEW stats;