Need help with SQL to calculate if store is open or not

Question

I have a list of storecodes in table "Stores" and another table "StoreClosedDates" which tells me if the store is closed on a Saturday or Sunday. My StoreOpenDates table looks like this:

CREATE TABLE [dbo].[StoreClosedDates](
    [StoreCode] [varchar](50) NOT NULL,
    [ClosedOnSunday] [bit] NOT NULL,
    [ClosedOnSaturday] [bit] NOT NULL
) ON [PRIMARY]

This table needs to be changed later to include holiday dates as well, so that those can be covered as well. I am not entirely surely how I can change this table to cover both options (holidays and weekends). Now I need to write a query which returns me a list of stores that are open for the current date. I am not sure how to compare for the weekend in the where clause - I know I should be using: DATEPART ( dw , getdate() ), but I cant seem to see the entire picture to solve it.

The StoreClosedDates contains only stores that closed. If a store is not present in that table, then the store is open for the current date.

Answer 1

Does it need to be in that table? Store the Holidays in a separate table and you can join to get to get the functionality you need.

Answer 2

SELECT  *
FROM    StoreClosedDates
WHERE   NOT (DATEDIFF(day, GETDATE(), '2007/01/01') % 7 = 5 AND ClosedOnSaturday = 1)
        AND NOT (DATEDIFF(day, GETDATE(), '2007/01/01') % 7 = 6 AND ClosedOnSunday = 1)

Better avoid usage of DATEPART, because it's locale dependent.

To check for the fixed date holidays, create a table with two separate columns containing the month and the date:

CREATE TABLE holiday (
        hmon TINYINT, hday TINYINT,
        PRIMARY KEY (hmon, hday),
        CHECK(CAST('2008-' + CAST(hday AS VARCHAR) + '-' + CAST(hmon AS VARCHAR) AS DATETIME) > 0)
        )

and use it in a query:

SELECT  *
FROM    Stores
WHERE   id NOT IN
        (
        SELECT  StoreID
        FROM    StoreClosedDates
        WHERE   NOT (DATEDIFF(day, GETDATE(), '2007/01/01') % 7 = 5 AND ClosedOnSaturday = 1)
                AND NOT (DATEDIFF(day, GETDATE(), '2007/01/01') % 7 = 6 AND ClosedOnSunday = 1)
        )
        AND NOT EXISTS
        (
        SELECT  NULL
        FROM    Holidays
        WHERE   hday = DAY(GETDATE())
                AND hmon = MONTH(GETDATE())
        )

Answer 3

SELECT * FROM Stores WHERE StoreCode NOT IN
(
    SELECT StoreCode FROM StoreClosedDates
    WHERE 
    (
         DATEPART ( dw , getdate() ) = 1 AND ClosedOnSunday = 1
    ) OR
    (
         DATEPART ( dw , getdate() ) = 7 AND ClosedOnSaturday = 1
    )
)

This assumes your DatePart call returns 1 for sunday (first day of week) and 7 for saturday. I could be wrong on that, perhaps its 0 and 6, and it also depends on what you have set as your "First Day of week"