问在一个表视图中具有日期值联接3表

EN

EXEC(
'
-- Dynamic SQL to dynamically add the columns based on code
DECLARE @DynamicSQL VARCHAR(MAX);
DECLARE @FORMAT VARCHAR(100)= ''MAX(CASE WHEN src.code = ''''?'''' THEN COALESCE(src.status, ''''H'''') ELSE ''''H'''' END) AS ''''?'''''';
-- Populate Dynamic SQL
;WITH SDTable AS (
    SELECT ''JHB'' AS code, ''v'' AS status, CAST(''2017-12-26'' AS DATE) AS date UNION ALL
    SELECT ''JHB'', ''v'', CAST(''2017-12-27'' AS DATE) UNION ALL
    SELECT ''JHB'', ''v'', CAST(''2017-12-28'' AS DATE) UNION ALL
    SELECT ''JHB'', ''v'', CAST(''2017-12-31'' AS DATE) UNION ALL
    SELECT ''PEN'', ''v'', CAST(''2017-12-26'' AS DATE) UNION ALL
    SELECT ''PEN'', ''v'', CAST(''2017-12-27'' AS DATE) UNION ALL
    SELECT ''PEN'', ''v'', CAST(''2017-12-28'' AS DATE) UNION ALL
    SELECT ''PEN'', ''v'', CAST(''2017-12-29'' AS DATE)
),
PHTable AS (
    SELECT ''JHB'' AS code, ''Chrismas'' AS ''P.H'', CAST(''2017-12-25'' AS DATE) AS date UNION ALL
    SELECT ''PEN'', ''Chrismas'', CAST(''2017-12-25'' AS DATE)
),
WeekendTable AS (
    SELECT ''JHB'' AS code, ''Friday'' AS Holiday UNION ALL
    SELECT ''JHB'', ''Saturday'' UNION ALL
    SELECT ''PEN'', ''Saturday'' UNION ALL
    SELECT ''PEN'', ''Sunday''
),
DistinctCodeTable AS(
SELECT DISTINCT code FROM (SELECT code AS code FROM WeekendTable UNION ALL SELECT code AS code FROM PHTable UNION ALL SELECT code AS code FROM SDTable) codeTable
)
SELECT @DynamicSQL = 
    STUFF(( SELECT '','' + REPLACE(@FORMAT, ''?'', code) 
              FROM DistinctCodeTable
              FOR XML PATH('''')),1,1,'''');

-- Execute Main query with dynamic columns
EXEC(
-- Define Range
''DECLARE @D1 DATE = ''''2017-12-25'''';
DECLARE @D2 DATE = ''''2017-12-31'''';

;WITH SDTable AS (
    SELECT ''''JHB'''' AS code, ''''v'''' AS status, CAST(''''2017-12-26'''' AS DATE) AS date UNION ALL
    SELECT ''''JHB'''', ''''v'''', CAST(''''2017-12-27'''' AS DATE) UNION ALL
    SELECT ''''JHB'''', ''''v'''', CAST(''''2017-12-28'''' AS DATE) UNION ALL
    SELECT ''''JHB'''', ''''v'''', CAST(''''2017-12-31'''' AS DATE) UNION ALL
    SELECT ''''PEN'''', ''''v'''', CAST(''''2017-12-26'''' AS DATE) UNION ALL
    SELECT ''''PEN'''', ''''v'''', CAST(''''2017-12-27'''' AS DATE) UNION ALL
    SELECT ''''PEN'''', ''''v'''', CAST(''''2017-12-28'''' AS DATE) UNION ALL
    SELECT ''''PEN'''', ''''v'''', CAST(''''2017-12-29'''' AS DATE)
),
PHTable AS (
    SELECT ''''JHB'''' AS code, ''''Chrismas'''' AS ''''P.H'''', CAST(''''2017-12-25'''' AS DATE) AS date UNION ALL
    SELECT ''''PEN'''', ''''Chrismas'''', CAST(''''2017-12-25'''' AS DATE)
),
WeekendTable AS (
    SELECT ''''JHB'''' AS code, ''''Friday'''' AS Holiday UNION ALL
    SELECT ''''JHB'''', ''''Saturday'''' UNION ALL
    SELECT ''''PEN'''', ''''Saturday'''' UNION ALL
    SELECT ''''PEN'''', ''''Sunday''''
),
DistinctCodeTable AS(
SELECT DISTINCT code FROM (SELECT code AS code FROM WeekendTable UNION ALL SELECT code AS code FROM PHTable UNION ALL SELECT code AS code FROM SDTable) codeTable
),
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4)
SELECT 
src.day AS day,'' 
+ @DynamicSQL +
''
FROM 
(
-- Select Public holiday as well as status table
SELECT
 CASE WHEN ph.[P.H] IS NOT NULL THEN ph.code ELSE sd.code END AS code,
 CASE WHEN ph.[P.H] IS NOT NULL THEN ''''P.H'''' ELSE CASE WHEN sd.status IS NOT NULL THEN sd.status ELSE ''''H'''' END
 END AS status,
 DATEPART(DAY, DATEADD(day,i-1,@D1)) AS day
 FROM Nums n
 LEFT JOIN SDTable sd ON sd.date = DATEADD(day,n.i-1,@D1)
 LEFT JOIN PHTable ph ON ph.date = DATEADD(day,n.i-1,@D1)
WHERE i <= 1+DATEDIFF(day,@D1,@D2)
) src
GROUP BY src.day;'')
');

day      JHB    PEN
------  ------  ------
25       P.H    P.H
26        v      v
27        v      v
28        v      v
29        H      v
30        H      H
31        v      H