locked
SQL Question on how to calculate a beginning and ending date RRS feed

  • Question

  • I have 20 years of daily inventory data, by date.  I need to create a query that determines the third Saturday of each month of the year.  I also need to enter a variable, number of days, to provide a date-range for the data to be analyzed.  This variable will change per each request 

    For example the ending date is 08/18/2012 (third Saturday of month) and I need to analyze 45 days of historical daily data.  So I would be performing an analysis on daily data between 07/04/12 and 08/18/12.  This 45 day period would be used on the entire 20 years of daily data.

    FYI I am just starting to learn SQL

    Thanks for the help

    Saturday, August 11, 2012 2:51 PM

Answers

  • You may want to think about creating a lookup table that contains the third Sunday of each month for the past 20 years instead of calculating it on the fly in your script/stored procedure if you'll be using this frequently.

    Ex:
    Year    Month  ThirdSaturday
    2012   6          06/16/2012
    2012   7          07/21/2012
    2012   8          08/18/2012

    Using the DATEADD function, you could calculate the "start date".  Here is a simple example of DATEADD where you subtract 5 days from the current date:

    DECLARE @d DATETIME = GETDATE();
    SELECT DATEADD(DAY,-5,@d)
    

    If you do not want to use a lookup table to store the dates, there are several good discussions online on how to find the Nth day/week of the month. 
    http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, August 11, 2012 3:44 PM
  • Declare @Year Int, @Month Int, @EndDate Date,@StartDate Date, @DayName Varchar(25), @NumberOfDays Int, @NthDay Int
    
    Select @Year = 2012	/*YEAR you want to perform*/
    Select @Month = 8 /*MONTH you want to perform*/
    Select @DayName = 'Saturday' /*WEEKDAY you want to perform*/
    Select @NumberOfDays = 45 /*DAYS you want to perform*/
    Select @NthDay = 3 /*Nth WEEKDAY you want to perform*/
    
    ;With CTEs
    As
    (
    Select 0 Slno
    Union All
    Select Slno + 1  From CTEs Where Slno <=30
    ),[Days]
    As
    (
    Select DateName(Weekday,DATEADD(Day,Slno,Cast(@Year as varchar)+ '-' + Cast(@Month as varchar) + '-01')) [Day], DATEADD(Day,Slno,Cast(@Year as varchar)+ '-' + Cast(@Month as varchar) + '-01') [Date] From CTEs
    ),[DayWithSeq]
    As
    (
    Select ROW_NUMBER() Over(Order By [Date]) Nth,* from [Days] Where Month([Date]) =@Month And [Day] = @DayName
    )
    
    Select @StartDate = [Date] - @NumberOfDays , @EndDate = [Date]  from [DayWithSeq] Where Nth = @NthDay
    
    /*Start and End Date to be performed*/
    Select @StartDate [StartDate], @EndDate [EndDate]

    Result:

    StartDate           EndDate
    ------------         -----------
    2012-07-04	  2012-08-18

    Now, you can use the StartDate and EndDate in your Query (i.e: BETWEEN or >= and <= operators to validate the daterange)

    Saturday, August 11, 2012 3:46 PM
  • Assuming the date provided always the third Saturday of the month, you can use DATEADD to calcuate the starting date.  For the date range, you might consider an inclusive start date and and exclusive end date so that the query will return the expected results even if the inventory date is a datetime or datetime2 column with a time component other than midnight.  Stored procedure example:

    CREATE PROC dbo.GetDailyInventory @EndDate datetime ,@NumberOfDays int AS DECLARE @StartDate datetime; --calculate inclusive start date and exclusive end date SELECT @StartDate = DATEADD(day, -@NumberOfDays, @EndDate) ,@EndDate = DATEADD(day, 1, @EndDate); SELECT InventoryDate, QuantityOnHand FROM dbo.Inventory WHERE InventoryDate >= @StartDate AND InventoryDate < @EndDate ORDER BY InventoryDate;

    RETURN @@ERROR; GO

    If the third Saturday date needs to be calcuated, how will the desired month be specified (year and month, first date of month, last date of month)?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Kalman Toth Friday, August 17, 2012 4:44 AM
    Saturday, August 11, 2012 3:48 PM
  • It sounds like you need a table of Numbers. You can read about this concept here:
    http://www.sommarskog.se/arrays-in-sql-2005.html#numbersasconcept
    (Only read down the header "An Inline Function".)

    Once you have that table, you can produces all the desired Saturdays this way:

    WITH CTE AS (
       SELECT convert(date,
                convert(varchar, y.Number) +
                substring(convert(varchar, m.Number), 2, 3) +
                convert(varchar, d.Number)) AS date
       FROM   Numbers y
       CROSS  JOIN Numbers m
       CROSS  JOIN Numbers d
       WHERE  y.Number BETWEEN 1992 AND 2012
         AND  m.Number BETWEEN 101 AND 112
         AND  d.Number BETWEEN 15 AND 21
    )
    SELECT date
    FROM   CTE
    WHERE  datename(weekday, date) = 'Saturday'
    ORDER BY date


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, August 17, 2012 4:44 AM
    Saturday, August 11, 2012 9:07 PM
  • A useful idiom is a report period calendar. It gives a name to a range of dates. 

    CREATE TABLE Report_Periods
    (report_name CHAR(10) NOT NULL PRIMARY KEY,
     report_start_date DATE NOT NULL,
     report_end_date DATE NOT NULL,
     CONSTRAINT date_ordering
     CHECK (report_start_date <= report_end_date),
     etc);

    These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created. 

    Also, start using the ISO-8601 date format (yyyy-mm-dd); it is the only format allowed in Standard SQ as  well as most ISO Standards and MS is moving to support it in the new temporal feaures.  


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by Kalman Toth Friday, August 17, 2012 4:44 AM
    Saturday, August 11, 2012 9:15 PM

All replies

  • You may want to think about creating a lookup table that contains the third Sunday of each month for the past 20 years instead of calculating it on the fly in your script/stored procedure if you'll be using this frequently.

    Ex:
    Year    Month  ThirdSaturday
    2012   6          06/16/2012
    2012   7          07/21/2012
    2012   8          08/18/2012

    Using the DATEADD function, you could calculate the "start date".  Here is a simple example of DATEADD where you subtract 5 days from the current date:

    DECLARE @d DATETIME = GETDATE();
    SELECT DATEADD(DAY,-5,@d)
    

    If you do not want to use a lookup table to store the dates, there are several good discussions online on how to find the Nth day/week of the month. 
    http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Saturday, August 11, 2012 3:44 PM
  • Declare @Year Int, @Month Int, @EndDate Date,@StartDate Date, @DayName Varchar(25), @NumberOfDays Int, @NthDay Int
    
    Select @Year = 2012	/*YEAR you want to perform*/
    Select @Month = 8 /*MONTH you want to perform*/
    Select @DayName = 'Saturday' /*WEEKDAY you want to perform*/
    Select @NumberOfDays = 45 /*DAYS you want to perform*/
    Select @NthDay = 3 /*Nth WEEKDAY you want to perform*/
    
    ;With CTEs
    As
    (
    Select 0 Slno
    Union All
    Select Slno + 1  From CTEs Where Slno <=30
    ),[Days]
    As
    (
    Select DateName(Weekday,DATEADD(Day,Slno,Cast(@Year as varchar)+ '-' + Cast(@Month as varchar) + '-01')) [Day], DATEADD(Day,Slno,Cast(@Year as varchar)+ '-' + Cast(@Month as varchar) + '-01') [Date] From CTEs
    ),[DayWithSeq]
    As
    (
    Select ROW_NUMBER() Over(Order By [Date]) Nth,* from [Days] Where Month([Date]) =@Month And [Day] = @DayName
    )
    
    Select @StartDate = [Date] - @NumberOfDays , @EndDate = [Date]  from [DayWithSeq] Where Nth = @NthDay
    
    /*Start and End Date to be performed*/
    Select @StartDate [StartDate], @EndDate [EndDate]

    Result:

    StartDate           EndDate
    ------------         -----------
    2012-07-04	  2012-08-18

    Now, you can use the StartDate and EndDate in your Query (i.e: BETWEEN or >= and <= operators to validate the daterange)

    Saturday, August 11, 2012 3:46 PM
  • Assuming the date provided always the third Saturday of the month, you can use DATEADD to calcuate the starting date.  For the date range, you might consider an inclusive start date and and exclusive end date so that the query will return the expected results even if the inventory date is a datetime or datetime2 column with a time component other than midnight.  Stored procedure example:

    CREATE PROC dbo.GetDailyInventory @EndDate datetime ,@NumberOfDays int AS DECLARE @StartDate datetime; --calculate inclusive start date and exclusive end date SELECT @StartDate = DATEADD(day, -@NumberOfDays, @EndDate) ,@EndDate = DATEADD(day, 1, @EndDate); SELECT InventoryDate, QuantityOnHand FROM dbo.Inventory WHERE InventoryDate >= @StartDate AND InventoryDate < @EndDate ORDER BY InventoryDate;

    RETURN @@ERROR; GO

    If the third Saturday date needs to be calcuated, how will the desired month be specified (year and month, first date of month, last date of month)?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Kalman Toth Friday, August 17, 2012 4:44 AM
    Saturday, August 11, 2012 3:48 PM
  • It sounds like you need a table of Numbers. You can read about this concept here:
    http://www.sommarskog.se/arrays-in-sql-2005.html#numbersasconcept
    (Only read down the header "An Inline Function".)

    Once you have that table, you can produces all the desired Saturdays this way:

    WITH CTE AS (
       SELECT convert(date,
                convert(varchar, y.Number) +
                substring(convert(varchar, m.Number), 2, 3) +
                convert(varchar, d.Number)) AS date
       FROM   Numbers y
       CROSS  JOIN Numbers m
       CROSS  JOIN Numbers d
       WHERE  y.Number BETWEEN 1992 AND 2012
         AND  m.Number BETWEEN 101 AND 112
         AND  d.Number BETWEEN 15 AND 21
    )
    SELECT date
    FROM   CTE
    WHERE  datename(weekday, date) = 'Saturday'
    ORDER BY date


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, August 17, 2012 4:44 AM
    Saturday, August 11, 2012 9:07 PM
  • A useful idiom is a report period calendar. It gives a name to a range of dates. 

    CREATE TABLE Report_Periods
    (report_name CHAR(10) NOT NULL PRIMARY KEY,
     report_start_date DATE NOT NULL,
     report_end_date DATE NOT NULL,
     CONSTRAINT date_ordering
     CHECK (report_start_date <= report_end_date),
     etc);

    These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created. 

    Also, start using the ISO-8601 date format (yyyy-mm-dd); it is the only format allowed in Standard SQ as  well as most ISO Standards and MS is moving to support it in the new temporal feaures.  


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by Kalman Toth Friday, August 17, 2012 4:44 AM
    Saturday, August 11, 2012 9:15 PM