Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL finding second tuesday of every month for next 60 months

Posted on 2014-03-12
8
Medium Priority
?
1,126 Views
Last Modified: 2014-03-12
I need a T-SQL query that will be calculate the 2nd tuesday of each month for the next 60 months.

I found Kevin Cross's artilce Date of the n-th Day in a Month and was able to alter the following script to give me the 2nd Tuesday of the particular month.  But how how can I alter the script to get all 2nd Tuesdays for the next 60 months?

declare @yr int, @mo tinyint
set @yr = 2014
set @mo = 6
;
with dates( dt )
as
(
   -- select 1st day of month constructed from year and month inputs
   select convert( datetime, 
            convert( char(8), 
               @yr * 10000 + @mo * 100 + 1 
            ) 
         ) 

   union all -- facilitates recursion

   -- add in remainder of days in month
   select dt + 1
   from dates
   -- keeps adding until the next day would be 1st of next month
   where day( dt + 1 ) <> 1 
)
, dates_tagged( dt, dw, occurrence, occurrence_reverse )
as
(
   select dt, datepart( weekday, dt )
        , row_number() 
             over( partition by datepart( weekday, dt ) 
                   order by dt )
        , row_number() 
             over( partition by datepart( weekday, dt ) 
                   order by dt desc )
   from dates
)
select dt, dw, occurrence, occurrence_reverse
from dates_tagged
where dw = 3 and occurrence = 2  --I added this
; 

Open in new window


Any help is appreciated!

Hank
0
Comment
Question by:Hankinater
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923629
I think I would put the above inside a function, and apply the function for the list of years/month number as needed...

but you can put the "recursion" stop condition also
declare @startdate datetime
set @startdate = convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120)
;
with dates( dt )
as
(
   -- select 1st day of month constructed from year and month inputs
   select @startdate 

   union all -- facilitates recursion

   -- add in remainder of days in month
   select dt + 1
   from dates
   -- keeps adding until the next day would in 60 months
   where dt <= dateadd( month, 60, @startdate )
)
, dates_tagged( dt, dw, dm, occurrence)
as
(
   select dt, datepart( weekday, dt ),  datepart( month, dt )
        , row_number() 
             over( partition by datepart( month, dt ), datepart( weekday, dt ) 
                   order by dt )
   from dates
)
select dt, dw, occurrence
from dates_tagged
where dw = 3  and occurrence = 2  --I added this
order by dt
option (maxrecursion 10000)
; 

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39923635
Yikes!  Far too much overhead and too messy.  And it has dependencies on datefirst and/or language settings because of "datepart( weekday, dt )".

Below is sample code that has no setting dependencies, no recursion and no sorting (ROW_NUMBER() requires sorting), just quick computations:



DECLARE @starting_date datetime
DECLARE @number_of_months_to_gen int

SET @starting_date = '20140601' --<-- chg starting date as needed
SET @number_of_months_to_gen = 60 --<-- chg number of months as needed

----------------------------------------------------------------------------------------------------

--force day of @starting_date to be the first
SET @starting_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @starting_date), 0)

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteMonthNumbers AS (
    --generated month numbers are 0-based, thus 0 thru (#months_needed - 1), so 40 months = monthNumbers 0 thru 39
    SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS monthNumber
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    WHERE
        [100s].digit * 100 + [10s].digit * 10 + [1s].digit <= (@number_of_months_to_gen - 1)
)
SELECT
    DATEADD(DAY, DATEDIFF(DAY, 1, day_7_of_month) / 7 * 7 + 7, 1) AS second_tues_of_month
FROM cteMonthNumbers
CROSS APPLY (
    SELECT DATEADD(DAY, 6, DATEADD(MONTH, monthNumber, @starting_date)) AS day_7_of_month
) AS cross_apply_1
ORDER BY
    second_tues_of_month
0
 
LVL 2

Author Closing Comment

by:Hankinater
ID: 39923673
Thanks Scott - That's perfect!!
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923688
not sure why my suggestion is "yikes!", as it's running sub-second, which is just fine for a "one-shot" stuff ... and even for daily operations ...
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39923733
Seriously?  Almost 11,000 logical I/Os vs 0 I/Os and you think there's no real difference?


declare @startdate datetime
set @startdate = convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120);

DECLARE @starting_date datetime
DECLARE @number_of_months_to_gen int

SET @starting_date = '20140601' --<-- chg starting date as needed
SET @number_of_months_to_gen = 60 --<-- chg number of months as needed


set statistics io on
set statistics time on


with dates( dt )
as
(
   -- select 1st day of month constructed from year and month inputs
   select @startdate

   union all -- facilitates recursion

   -- add in remainder of days in month
   select dt + 1
   from dates
   -- keeps adding until the next day would in 60 months
   where dt <= dateadd( month, 60, @startdate )
)
, dates_tagged( dt, dw, dm, occurrence)
as
(
   select dt, datepart( weekday, dt ),  datepart( month, dt )
        , row_number()
             over( partition by datepart( month, dt ), datepart( weekday, dt )
                   order by dt )
   from dates
)
select dt, dw, occurrence
from dates_tagged
where dw = 3  and occurrence = 2  --I added this
order by dt
option (maxrecursion 10000)
;

----------------------------------------------------------------------------------------------------

--force day of @starting_date to be the first
SET @starting_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @starting_date), 0)

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteMonthNumbers AS (
    --generated month numbers are 0-based, thus 0 thru (#months_needed - 1), so 40 months = monthNumbers 0 thru 39
    SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS monthNumber
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    WHERE
        [100s].digit * 100 + [10s].digit * 10 + [1s].digit <= (@number_of_months_to_gen - 1)
)
SELECT
    DATEADD(DAY, DATEDIFF(DAY, 1, day_7_of_month) / 7 * 7 + 7, 1) AS second_tues_of_month
FROM cteMonthNumbers
CROSS APPLY (
    SELECT DATEADD(DAY, 6, DATEADD(MONTH, monthNumber, @starting_date)) AS day_7_of_month
) AS cross_apply_1
ORDER BY
    second_tues_of_month
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39923757
Or add either of these statements to the top:

set language [British English]
set datefirst 1

and check the results.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923794
Well, I agree that setting datefirst or language will change, but that can be "fixed" by replacing the
where dw = 3
accordingly.

for the "performance" part, I repeat that for my version, which runs sub-second, and presumably for a one-shot (or once per day), my efforts to "tune" something will go somewhere else but not on this one.

if this code is really a performance killer on my db instance, for example because the query is used all the time, I will put that data into a table (and I have my calendar table ready ANYHOW), so we won't discuss that "code" anyhow.

I understand that there are differences, but simply not worth "optimizing" for this small set of data.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39923839
Actually I had written my comment just based on the initial q, I had not even seen your code yet.  Although "Yikes!" certainly applies to both.

Frankly, I don't even consider this "optimizing", rather simply writing decent code to begin with.  Generating thousands of rows' worth of data and searching through them to get 60 needed values is just extremely poor coding.

As to overall overhead, this is just a tiny code snippet.  Hundreds or thousands of such snippets could appear in procs, and with each snippet getting executed multiple times a day.  Thus, allowing such poor coding will easily waste multiple millions of I/Os per day.

Finally, why encourage people to write such horribly inefficient code to begin with?  Code like this tends to be copied and used over and over, as witnessed by it being copied here initially.  Good coders should have a basic, minimal standard of coding, not lazily using known poor techniques because "it will only run once a day".

I mean, even the most basic date construction in the code:
select convert( datetime,
            convert( char(8),
               @yr * 10000 + @mo * 100 + 1
            )
         )
 is very poor coding technique.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question