SQL - Group by count - using two columns-CodePudding

Currently I am trying to have a query which will run a headcount of nurses who are rostered to work grouped into their Cost Centre (basically their location, which ward etc.) and which segment of time they fall into(Early Shift, Late etc.)

However sometimes someone from another cost centre can be transferred to a different one and I need to count this into the group as well.

All employees have a home cost centre: HOMELABORLEVLENAME3 and then a column exists called ORGPATHTXT.

ORGPATHTXT is always null unless they are transferred in.

I need to group together both then segment that as per the shift times.

My current code is below:

select 
HOMELABORLEVELNAME2, HOMELABORLEVELNAME3,

sum(CASE WHEN SHIFTSTARTDATE between '2022-05-13 04:00' and '2022-05-13 11:00' THEN 1 ELSE 0 END) as 'Today_Early',
sum(CASE WHEN SHIFTSTARTDATE between '2022-05-13 11:00' and '2022-05-13 18:00' THEN 1 ELSE 0 END) as 'Today_Late',
sum(CASE WHEN SHIFTSTARTDATE between '2022-05-13 18:00' and '2022-05-14 04:00' THEN 1 ELSE 0 END) as 'Today_Night',

sum(CASE WHEN SHIFTSTARTDATE between '2022-05-14 04:00' and '2022-05-14 11:00' THEN 1 ELSE 0 END) as 'Tmrw_Early',
sum(CASE WHEN SHIFTSTARTDATE between '2022-05-14 11:00' and '2022-05-14 18:00' THEN 1 ELSE 0 END) as 'Tmrw_Late',
sum(CASE WHEN SHIFTSTARTDATE between '2022-05-14 18:00' and '2022-05-15 04:00' THEN 1 ELSE 0 END) as 'Tmrw_Night'

from VP_SCHEDULE s

where

PERSONNUM != 'UNASSIGNEDSHIFT'
and
HOMELABORLEVELNAME2 = '40'
AND (HOMELABORLEVELNAME3 = '1914' OR HOMELABORLEVELNAME3 = '1964' OR HOMELABORLEVELNAME3 = '1924' OR HOMELABORLEVELNAME3 = '1954'
OR HOMELABORLEVELNAME3 = '1944' OR HOMELABORLEVELNAME3 = '1934' OR HOMELABORLEVELNAME3 = '1554' OR HOMELABORLEVELNAME3 = '1564'
OR HOMELABORLEVELNAME3 = '1504' OR HOMELABORLEVELNAME3 = '5334' OR HOMELABORLEVELNAME3 = '2204' OR HOMELABORLEVELNAME3 = '3104'
OR HOMELABORLEVELNAME3 = '3004' OR HOMELABORLEVELNAME3 = '2004' OR HOMELABORLEVELNAME3 = '3304' OR HOMELABORLEVELNAME3 = '2104'
OR HOMELABORLEVELNAME3 = '7554' OR HOMELABORLEVELNAME3 = '1754')
and paycodename is null
-- AND SHIFTSTARTDATE between '2022-04-29 04:00' and '2022-04-29 11:00'
group by HOMELABORLEVELNAME2, HOMELABORLEVELNAME3

This then shows as: results

My issue, is that by grouping by HOMELABORLEVELNAME3, it doesn't count the people with ORGPATHTXT filled in, but if I group by ORGPATHTXT I am not grouping by cost centre number which is a requirement. Sorry if this is long winded! But any help would be appreciated.

CodePudding user response：

You need some way when ORGPATHTXT is set, to know which HOMELAGORLEVELNAME3 value it should be counted with.

select [...]
from vp_schedule s
left join ([... some things that maps ORGPATHTXT to LEVELNAME3 value]) magic 
on s.ORGPATHTXT = magic.ORGPATHTXT
where PERSONNUM != 'UNASSIGNEDSHIFT'
  and s.HOMELABORLEVELNAME2 = '40'
  AND (s.HOMELABORLEVELNAME3 in ('1914','1964','1924','1954', '1944','1934','1554','1564', '1504','5334','2204','3104', '3004','2004','3304','2104', '7554','1754'))
  and paycodename is null
-- AND SHIFTSTARTDATE between '2022-04-29 04:00' and '2022-04-29 11:0
group by 
  HOMELABORLEVELNAME2, 
  COALESCE( magic.HOMELABORLEVELNAME3, s.HOMELABORLEVELNAME3)