Calculating if date is within BST - SQL Server

Question

I'm building a date dimension, and the last part I need to do is populate a flag that states whether or not the date falls within British Summer Time. British Summer Time starts from the LAST Sunday in March and ends on the LAST Sunday in October. So far i've got this:

update [Dim_Date_Test_BST]
set IsBST = 1
where (CalenderMonth BETWEEN 03 and 10)

I've also got the following code that lists the last Sunday of each month:

dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,Date),-1))/7)*7,'17530107')

I'm struggling to combine the two pieces of code to produce the result I need. Any ideas? Thanks.

Fix, thanks to contributor below:

SELECT DISTINCT
        CalenderYear = D.CalenderYear,
        BritishSummerStartDate = 
        CASE 
            WHEN D.CalenderMonth = 3 
            THEN dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,D.Date),-1))/7)*7,'17530107') 
        END,
        BritishSummerEndDate = CASE 
            WHEN D.CalenderMonth = 10 
            THEN dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,D.Date),-1))/7)*7,'17530107') 
        END
INTO #BST
FROM [dbo].[Dim_Date_Test_BST] AS D
WHERE D.CalenderMonth IN (3, 10)


update D 
SET IsBST = 1
FROM[dbo].[Dim_Date_Test_BST] AS D
INNER JOIN #BST a ON d.CalenderYear = a.CalenderYear AND a.BritishSummerStartDate IS NOT NULL
INNER JOIN #BST b ON d.CalenderYear = b.CalenderYear AND b.BritishSummerEndDate IS NOT NULL
WHERE D.Date BETWEEN a.BritishSummerStartDate AND b.BritishSummerEndDate

Answer 1

You can try generating a set with the proper start/end by each year, then use this as reference with a BETWEEN.

;WITH BritishSummerPeriodsByYear AS
(
    SELECT DISTINCT
        CalenderYear = D.CalenderYear,
        BritishSummerStartDate = CASE 
            WHEN D.CalenderMonth = 3 
            THEN dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,D.Date),-1))/7)*7,'17530107') END,
        BritishSummerEndDate = CASE 
            WHEN D.CalenderMonth = 10 
            THEN dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,D.Date),-1))/7)*7,'17530107') END
    FROM
        [Dim_Date_Test_BST] AS D
    WHERE
        D.CalenderMonth IN (3, 10)
)
update D SET
    IsBST = 1
FROM
    Dim_Date_Test_BST AS D
    INNER JOIN BritishSummerPeriodsByYear AS BP ON D.CalenderYear = BP.CalenderYear
WHERE
    D.Date BETWEEN BP.BritishSummerStartDate AND BP.BritishSummerEndDate