none
Get the number of week days

    Question

  • Hi, I have the following script that can be run in any database, and it has sample data on it

    Basically what I want to find is

    I have a store with a certain number of missing sales days

    I want to find out how many of those days are in this month and how many in the last month

    and store them by day of the week

     

    Please run the script, it will make more sense

     

    TSQL SCRIPT:

    /**************************************************************************************************************/

     

    /*
    Expected [#__Result]

     

    Assuming today is October 8

    store 651 is missing the last 3 days

    store 890 is missing the last 15 days

     

    store_id     day_of_the_week        last_month         current_month
    --------------------------------------------------------------------------------------------------
    651             Sunday                     0                    1
    651             Monday                    0                    1
    651             Tuesday                    0                    1

     

    890             Sunday                     1                    1
    890             Monday                     1                    1
    890             Tuesday                    2                    1
    890             Wednesday               1                    1
    890             Thursday                   1                    1
    890             Friday                       1                    1
    890             Saturday                   1                    1

    */

     

    -- Assuming today is October 8

    DECLARE @date AS DATETIME
    SET @date = '10-8-2008'

     

    -- Table with dates

    CREATE TABLE [#__Dates] (
     [DateKey] [int] NOT NULL ,
     [Date] [smalldatetime] NOT NULL ,
    )

     

    -- Table with stores and number of missing days

    CREATE TABLE [#__Days] (
     [store_id] [int] NOT NULL ,
     [missing_days] [int] NOT NULL DEFAULT (0)
    )

     

    -- This is where I want to output the result

    CREATE TABLE [#__Result]
    (
     [store_id] [int] NOT NULL ,
     [day_of_the_week] [varchar](20) NOT NULL ,
     [last_month] [int] NOT NULL DEFAULT(0) ,
     [current_month] [int] NOT NULL DEFAULT(0) ,
    )

     

    -- Sample dates

    INSERT INTO [#__Dates] VALUES (1728, '9-23-2008')
    INSERT INTO [#__Dates] VALUES (1729, '9-24-2008')
    INSERT INTO [#__Dates] VALUES (1730, '9-25-2008')
    INSERT INTO [#__Dates] VALUES (1731, '9-26-2008')
    INSERT INTO [#__Dates] VALUES (1732, '9-27-2008')
    INSERT INTO [#__Dates] VALUES (1733, '9-28-2008')
    INSERT INTO [#__Dates] VALUES (1734, '9-29-2008')
    INSERT INTO [#__Dates] VALUES (1735, '9-30-2008')
    INSERT INTO [#__Dates] VALUES (1736, '10-1-2008')
    INSERT INTO [#__Dates] VALUES (1737, '10-2-2008')
    INSERT INTO [#__Dates] VALUES (1738, '10-3-2008')
    INSERT INTO [#__Dates] VALUES (1739, '10-4-2008')
    INSERT INTO [#__Dates] VALUES (1740, '10-5-2008')
    INSERT INTO [#__Dates] VALUES (1741, '10-6-2008')
    INSERT INTO [#__Dates] VALUES (1742, '10-7-2008')
    INSERT INTO [#__Dates] VALUES (1742, '10-8-2008')

     

    -- Sample stores with missing days

    -- Missing October 5-7, last 3 days
    INSERT INTO [#__Days] VALUES (651, 3)

    -- Missing September 23-October 7, last 15 days 
    INSERT INTO [#__Days] VALUES (890, 15)

     

    DROP TABLE [#__Dates]
    DROP TABLE [#__Days]

    DROP TABLE [#__Result]

     

    /*

    Any idea how can I fill [#__Result] to get the expected result????

    Thanks in Advance

    */

    Friday, October 24, 2008 5:50 PM