First Wednesday after the Second Tuesday In a Month —Solution 2


  
 
   DECLARE  @StartDate  DATETIME = '2019-01-01'
DECLARE @EndDate DATETIME = DATEADD(DAY,900,@StartDate)
 
DECLARE @CustomOrderTable TABLE (DayOfWeek INT, OrderInTable INT)
INSERT @CustomOrderTable VALUES (7,7),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6)
 
 
  
   
--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
   
 --  
,DateRange as
(
select  dateadd(day,n-1,@startdate) dt from Nums  
where dateadd(day,n-1,@startdate)<=@EndDate)  
  
  
 
 
 
,mycte as (
SELECT  *,DayName = DATENAME(WEEKDAY,dt), DayOfWeek = DATEPART(DW,dt),Week = DATEPART(WEEK,dt),
 Sum(case when   DATENAME(WEEKDAY,dt)='Tuesday' then 1 else 0 end) Over(Partition by Year(dt), month(dt)   order by dt) grp
FROM    DateRange DR)
 
--INSERT INTO YourTable
 
 
 Select dt, DayName,  
 Year(dt) Year
 ,Case when  Day(DateAdd(Month, DateDiff(Month, 0, dt), 0)+6 
             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
             +@@DateFirst+2)%7 --First Wednesday  IN the month
			 )=1 then 
			 DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
             +@@DateFirst+2)%7
			 Else
			  DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+7
             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
             +@@DateFirst+2)%7
			  END  First_Wednesday_after_the_SecondTuesday_In_a_Month

    from mycte 
   
   

 

https://www.red-gate.com/simple-talk/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

https://jingyangli.wordpress.com/2019/01/03/first-wednesday-after-the-second-tuesday-in-a-month/

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s