Seasonal Address Activation

DECLARE @AddressTypeHome INT = 6;

WITH Address_With_INT_Dates

AS(
SELECT *,
    today_month_day_INT = CONVERT(INT, REPLACE(CONVERT(VARCHAR (5), GETDATE(), 1),'/', '')),
    from_month_day_INT = CONVERT(INT, PA.from_month_day),
    to_month_day_INT  = CONVERT(INT, PA.to_month_day)
FROM core_person_address PA
)

UPDATE CPA -- Removing primary address flag to allow setting seasonal address as primary
SET CPA.primary_address = 0
FROM Address_With_INT_Dates PA
JOIN core_person_address CPA ON CPA.person_id = PA.person_id
    AND CPA.primary_address = 1
WHERE PA.from_month_day != 0000
AND PA.primary_address = 0
AND
(
   (to_month_day_INT > from_month_day_INT
      AND today_month_day_INT >= from_month_day_INT
      AND today_month_day_INT <= to_month_day_INT
   )
OR
   (to_month_day_INT < from_month_day_INT
      AND (today_month_day_INT >= from_month_day_INT
            OR today_month_day_INT <= to_month_day_INT
          )
   )
);

WITH Address_With_INT_Dates

AS(
SELECT *,
    today_month_day_INT = CONVERT(INT, REPLACE(CONVERT(VARCHAR (5), GETDATE(), 1),'/', '')),
    from_month_day_INT = CONVERT(INT, PA.from_month_day),
    to_month_day_INT  = CONVERT(INT, PA.to_month_day)
FROM core_person_address PA
)


UPDATE PA -- Set seasonal address as primary
SET PA.primary_address = 1
FROM Address_With_INT_Dates PA
WHERE PA.from_month_day != 0000
AND PA.primary_address = 0
AND
(
   (to_month_day_INT > from_month_day_INT
      AND today_month_day_INT >= from_month_day_INT
      AND today_month_day_INT <= to_month_day_INT
   )
OR
   (to_month_day_INT < from_month_day_INT
      AND (today_month_day_INT >= from_month_day_INT
            OR today_month_day_INT <= to_month_day_INT
          )
   )
);

WITH Address_With_INT_Dates

AS(
SELECT *,
    today_month_day_INT = CONVERT(INT, REPLACE(CONVERT(VARCHAR (5), GETDATE(), 1),'/', '')),
    from_month_day_INT = CONVERT(INT, PA.from_month_day),
    to_month_day_INT  = CONVERT(INT, PA.to_month_day)
FROM core_person_address PA
)

-- Removing primary address tag from no longer active seasonal address
UPDATE PA
SET PA.primary_address = 0
FROM Address_With_INT_Dates PA
WHERE PA.from_month_day != 0000
AND PA.primary_address = 1
AND
(
   (to_month_day_INT > from_month_day_INT
      AND (today_month_day_INT < from_month_day_INT
            OR today_month_day_INT > to_month_day_INT
          )
   )
OR
   (to_month_day_INT < from_month_day_INT
      AND (today_month_day_INT < from_month_day_INT
            AND today_month_day_INT > to_month_day_INT
          )
   )
);



WITH Address_With_INT_Dates

AS(
SELECT *,
    today_month_day_INT = CONVERT(INT, REPLACE(CONVERT(VARCHAR (5), GETDATE(), 1),'/', '')),
    from_month_day_INT = CONVERT(INT, PA.from_month_day),
    to_month_day_INT  = CONVERT(INT, PA.to_month_day)
FROM core_person_address PA
)

-- Finding Person's Main Home Address and setting it Primary
UPDATE CPA
SET CPA.primary_address = 1
FROM Address_With_INT_Dates PA
JOIN core_person_address CPA ON CPA.person_id = PA.person_id
AND CPA.primary_address = 0
AND CPA.address_type_luid = @AddressTypeHome
WHERE PA.primary_address = 0
AND (
   (to_month_day_INT > from_month_day_INT
      AND (today_month_day_INT < from_month_day_INT
            OR today_month_day_INT > to_month_day_INT
          )
   )
OR
   (to_month_day_INT < from_month_day_INT
      AND (today_month_day_INT < from_month_day_INT
            AND today_month_day_INT > to_month_day_INT
          )
   )
)