Join 3 table that have date value in one table view

Question

I have 3 table , two table have date column , and one just display day ,

1st table - first table is use to set the status delivered for that day..

code    status  date
------  ------  ------
JHB        v    2017-12-26
JHB        v    2017-12-27
JHB        v    2017-12-28
JHB        v    2017-12-31
PEN        v    2017-12-26
PEN        v    2017-12-27
PEN        v    2017-12-28
PEN        v    2017-12-29

second table is for every code place public holiday

code    P.H         date
------  ------      ------
JHB     Chrismas    2017-12-25
PEN     Chrismas    2017-12-25

and the third table is every code weekend holiday

code    Holiday         
------  ------  
JHB     Friday
JHB     Saturday    
PEN     Saturday
PEN     Sunday

So how to make the table like below is generated..

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

day is arrange of date from 25 december to 31 december day

v - delivered status H- holiday P.H - Public Holiday

what i try is just join 2 table and just display the status and not display the holiday and public holiday @IanKenney — Tahi, Mar 21 '17 at 04:51
Read about - unions ( to join first two tables ) http://stackoverflow.com/documentation/sql/349/union-union-all#t=201703210524568504559 — Ian Kenney, Mar 21 '17 at 05:20
Read about http://stackoverflow.com/questions/3946121/t-sql-get-all-dates-between-2-dates to get all dates in a range — Ian Kenney, Mar 21 '17 at 05:21
Read about Pivots to turn row data into columns (Code to JHB or PEN) http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Ian Kenney, Mar 21 '17 at 05:22
Read about the `DATEPART` function to get weekday from a date https://msdn.microsoft.com/en-us/library/ms174420.aspx — Ian Kenney, Mar 21 '17 at 05:24
it is possible if i want to get date from the day for example i said saturday and automatically the all date of saturday displayed from sql statement?.. — Tahi, Mar 21 '17 at 06:33
start with the "get all dates in a range" above and add a where clause and use the DATEPART function (or DATENAME - https://msdn.microsoft.com/en-au/library/ms174395.aspx) — Ian Kenney, Mar 22 '17 at 10:32
Thank you @IanKenney for providing brilliant references. It helped a lot! — vCillusion, Jun 06 '18 at 22:34

score 0 · Answer 1 · answered Jun 06 '18 at 22:33

I have used recursive CTE to create the dynamic SQL query for columns. Also, I am using recursive CTE to calculate the date range. For the final result, I have joined public holiday and status table and grouped by date. Hope it helps !!

Demo

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;'')
');

Output

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

Join 3 table that have date value in one table view

1 Answers1