WITH C AS (select
[day1],[day2],[day3],[day4],[day5],[day6],[day7],[day8],[day9],[day10],[day11],[day12],[day13],[day14],[day15],[day16]
,[day17],[day18],[day19],[day20],[day21],[day22],[day23],[day24],[day25],[day26],[day27],[day28],[day29],[day30],[day31]
from [MMS_WorkdayDefinition]
where companyid='b0ba1259-54cc-4122-b66e-41988ac531ef'
and yeardate='2017-11-01'
),
C2 AS (
SELECT d=attribute, v=value
FROM (select * from C)a
UNPIVOT
(
value FOR attribute IN([day1]
,[day2],[day3],[day4],[day5],[day6],[day7],[day8],[day9],[day10],[day11],[day12],[day13],[day14],[day15],[day16],[day17]
,[day18],[day19],[day20],[day21],[day22],[day23],[day24],[day25],[day26],[day27],[day28],[day29],[day30],[day31])
) AS UPV
WHERE value IS NOT NULL
)
select d,bc from (
select * from C2 cross join (select id as bc,ISZB from [MMS_ShiftSettings]
where companyid='119419e3-c0d2-426d-9287-7572822c2d2e') t where C2.v<>1 or t.ISZB<>1
) k
Except
select CONVERT(VARCHAR(100), 'day',112)+CONVERT(VARCHAR(100), DATepart(dd, SetDate),112) AS d ,setting as bc
from [MMS_SchedulingAdjustment] sd
where CONVERT(VARCHAR(6), SetDate, 112)='201711' and sd.ISZB=1
and companyid='b0ba1259-54cc-4122-b66e-41988ac531ef'
group by setting, SetDate
