Hello,
I have a need to be able to show the planned work roster for consultants in the hospital that I work at. The only data I have in the database is a series of tables that store their job plans. For a particular consultant these could be a single pattern that repeats every week or they could have multi week patterns that repeat the cycle again after that number of weeks has passed. So for example, one consultant could have a pattern based on three weeks that repeats every three weeks, another consultant could have a seven week pattern that repeats every seven weeks.
What I need to be able to show in the QlikView model would be a pivot table with all of the consultants listed and their rota across the page showing their planned work for any range os selected dates from 1st April 2013 to 31st March 2014.
The SQL code below brings in the job plans for the consultants, I have also attached an Excel file showing the data for one consultant (the identifying fields have been scrambled).
JobPlans:
SQL SELECT PERSON.ID_NUMBER,
PERSON.SURNAME,
PERSON.EXTRA_CODE_1 as EmployeeNumber,
PERSON.EXTRA_CODE_2 as Username,
PERSON.GRADE_SHT_TITLE as EmployeeType,
POSTING.POSTING_ID,
RST_SHARED_PTTN_ASSIGN.RST_S_PTTN_ASSIGN_ID,
RST_SHARED_PATTERN_ORDER.RST_S_PTTN_ID,
RST_SHARED_PATTERN_ORDER.RST_S_PTTN_ORDER_ID,
RST_SHARED_PATTERN_ORDER.RST_S_PTTN_POSITION_NUM,
RST_SHARED_PATTERN_ORDER.ORDER_NUMBER,
RST_SHARED_PATTERN_ORDER.REVISED_START_TIME,
RST_SHARED_PATTERN_ORDER.REVISED_END_TIME,
RST_SHARED_PATTERN_ORDER.REVISED_ESTIMATED_MINUTES,
RST_ROSTER_LOCATION.RST_LOCATION_ID,
RST_ROSTER_LOCATION.RST_LOCATION_NAME,
POSTING.POSTING_EDATE,
RST_SHARED_PTTN_ASSIGN.RST_S_PTTN_LAST_START_DATE,
RST_SHARED_PTTN_ASSIGN.RST_S_PTTN_LAST_START_POSITION
FROM (((MAPSMSTR1.dbo.RST_SHARED_PTTN_ASSIGN RST_SHARED_PTTN_ASSIGN
INNER JOIN MAPSMSTR1.dbo.RST_SHARED_PATTERN_ORDER RST_SHARED_PATTERN_ORDER
ON (RST_SHARED_PTTN_ASSIGN.RST_S_PTTN_ID = RST_SHARED_PATTERN_ORDER.RST_S_PTTN_ID))
INNER JOIN MAPSMSTR1.dbo.POSTING POSTING ON (POSTING.POSTING_ID =
RST_SHARED_PTTN_ASSIGN.POSTING_ID)) INNER JOIN MAPSMSTR1.dbo.PERSON PERSON ON (PERSON.ID_NUMBER =
POSTING.ID_NUMBER))
INNER JOIN
MAPSMSTR1.dbo.RST_ROSTER_LOCATION RST_ROSTER_LOCATION
ON (RST_SHARED_PATTERN_ORDER.RST_LOCATION_ID =
RST_ROSTER_LOCATION.RST_LOCATION_ID)
WHERE PERSON.GRADE_SHT_TITLE = 'Cons'
ORDER BY PERSON.SURNAME ASC,
POSTING.POSTING_ID ASC,
RST_SHARED_PATTERN_ORDER.RST_S_PTTN_POSITION_NUM ASC,
RST_SHARED_PATTERN_ORDER.ORDER_NUMBER ASC;
What I am having trouble with is using this data to build the roster for all consultants. I think that I need to do the tasks below but I am not sure how to implement it.
In addition to being able to find the RST_S_PTTN_ID for each assigned pattern you will see per row the RST_S_PTTN_LAST_START_DATE and the RST_S_PTTN_LAST_START_POSITION – these are important as they tell you (a) from which date the pattern was assigned (make sure you look at RST_S_PTTN_LAST_START_DATE and not RST_S_ORIG_LAST_START_DATE) and (b) which week in the pattern is used as the starting point of the pattern once assigned (make sure you look at RST_S_PTTN_LAST_START_POSITION and not RST_S_PTTN_ORIG_START_POSITION) – i.e. if this value is 2 it means that the person started on week 2 of the pattern on the date at which the pattern was assigned.
So to get the pattern for week beginning DD/MM/YYYY I think that I need to:
- a. Count how many weeks there are in the pattern details - should be the maximum value in the RST_S_PTTN_POSITION_NUM field in dbo.RST_SHARED_PATTERN_ORDER.
- b. Count how many weeks there are between DD/MM/YYYY and the date described in RST_S_PTTN_LAST_START_DATE (see comment below).
- c. Take the modulus of the division of: value from (b) / value from (a).
- d. Add the value from (c) to the value in RST_S_PTTN_LAST_START_POSITION.
- e. Take the modulus of the division of: value from (d) / value from (a) .
- f. The value from (e) is then the week pattern number for DD/MM/YYYY – it corresponds to the RST_S_PTTN_POSITION_NUM field in dbo.RST_SHARED_PATTERN_ORDER.
- g. I will also need to do a bit of calculation to figure out which day of the week your are referring to – but that is straightforward based on the date of interest (DD/MM/YYYY) and this can then be mapped to the value in the ORDER_NUMBER field in dbo.RST_SHARED_PATTERN_ORDER – with an allowed value of 0-6 with 0 being Sunday.
- h. So then I will have both the pattern week number and the pattern day number that applies to DD/MM/YYYY and you can say “Dr Bloggs should have been doing XXX from YYY to ZZZ that day in location QQQ” – location comes from linking the RST_LOCATION_ID in dbo.RST_SHARED_PATTERN_ORDER to dbo.ROSTER_LOCATION.
When I do the calculations above I would base them around the Sunday immediately preceding the RST_S_PTTN_LAST_START_DATE (if that is not a Sunday – if it is then use that date itself) and the Sunday immediately preceding DD/MM/YYYY (if that is not a Sunday – again if it is then use that date itself) – as this will make the calculations more reliable in terms of the modulus divisions.
Thanks in advance for any help,
Gethyn.
