How to add holidays to DimTime table?-CodePudding

I require to add the a holiday indicator in my DimTime table. I have already added a HolidayInd column which I need to update with the specific information. I also have a Holidays table which includes the holidays.

My current DimTime table (10 row sample). Full table goes from 1900 to 2050.

idDimTime FullTime      HolidayInd
20000101  2000-01-01    NULL
20000102  2000-01-02    NULL
20000103  2000-01-03    NULL
20000104  2000-01-04    NULL
20000105  2000-01-05    NULL
20000106  2000-01-06    NULL
20000107  2000-01-07    NULL
20000108  2000-01-08    NULL
20000109  2000-01-09    NULL
200001010 2000-01-10    NULL

My Holidays table (5row sample). I contains all the holidays between years 2000 and 2050

id idDimTime
1  20000101
2  20000110
3  20000320
4  20000420
5  20000421

My DimTime desired table

idDimTime FullTime    HolidayInd
20000101  2000-01-01    1
20000102  2000-01-02    0
20000103  2000-01-03    0
20000104  2000-01-04    0
20000105  2000-01-05    0
20000106  2000-01-06    0
20000107  2000-01-07    0
20000108  2000-01-08    0
20000109  2000-01-09    0
200001010 2000-01-10    1

I have joined both tables but unsure on how to update DimTime based on it

SELECT  
DT.IdDimTime
,CASE WHEN H.IdDimTime IS NULL THEN 0
        ELSE 1
        END AS HolidayInd
FROM DimTime DT

LEFT JOIN Holidays H
ON H.IdDimTime = DT.IdDimTime
WHERE DT.IdDimTime BETWEEN 20000101 AND 20501231
ORDER BY DT.IdDimTime

CodePudding user response：

This should be fairly straightforward and I'll show you how to do it in two steps.

Add the column to the table
Update only the holidays

For adding the column to the table, I'm going to add a non-nullable column with a default constraint. Whether you want to keep the constraint around after adding the column or not is up to you, but is a quick way to fill the entire column with one value (in this case, 0).

alter table DimTime
   add HolidayInd bit not null
      constraint DF_DimTime_HolidayInd default 0;

For updating just the holidays, I'll take the query you've already written and turn it into an update.

UPDATE DT
   SET HolidayInd = 1
FROM DimTime DT
JOIN Holidays H
ON H.IdDimTime = DT.IdDimTime
WHERE DT.IdDimTime BETWEEN 20000101 AND 20501231;

Note - I replaced the left join with an inner join. I'm able to do this as your original query needed rows for all dates whereas the query that drives the update only needs holidays.