Close the Library Every Sunday

Clearinghouse Repository Polaris Repository
,
1

Close the Library Every Sunday

We have a library that wants to be closed every Sunday via the Dates Closed table. This query is useful when you need to close a library every Sunday (or some other day of the week) but you don’t want to add each, date, by, hand. (Which is what you need to do in SA.) This query will pull a list of Sundays, throw those into a temporary table, then use that table to populate Polaris.Polaris.DatesClosed and Polaris.Polaris.NSDatesNotToCall.

-- Set up a temporary table as a variable
DECLARE @TempDates TABLE
(
    OrganizationID INT,
    ClosedDate DATETIME
)

-- Set up our organization ID as a variable
DECLARE @OrgID INT;
SET @OrgID = '1'; -- Drop your organization ID here

-- Get a full year of Sundays as a list, and get it with the organizational ID too
WITH ClosedSundays AS ( 
    SELECT
        @OrgID as OrganizationID,
        DATEADD(DAY, 0, '20220102') AS Sunday -- Set this to the first Sunday of the year  
    UNION ALL  
    SELECT
        @OrgID AS OrganizationID,
        DATEADD(DAY, 7,Sunday)   
    FROM ClosedSundays  
    WHERE
        Sunday <= DATEADD(DAY, -7, '20221225')  -- Set this to the last Sunday of the year
    )

-- Drop that list into our temporary table
INSERT INTO @TempDates
SELECT * FROM ClosedSundays

-- Use the temp table to update the Dates Closed for an organization
INSERT INTO Polaris.Polaris.DatesClosed
SELECT
    OrganizationID,
    ClosedDate
FROM
    @TempDates

-- Use the temp table to update the Dates Not to Call for an organization
INSERT INTO Polaris.Polaris.NSDatesNotToCall
SELECT
    OrganizationID,
    ClosedDate
FROM
    @TempDates