Find Alternate Monday,1st Monday,third Monday and like this

GO
/****** Object:  StoredProcedure [common].[DateInsert]    Script Date: 9/16/2015 5:00:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- PASS @noOfWeek = 0 AND @IsAlternateDate = 1 IF ALTERNATE WEEK NEEDED. 
-- PASS @noOfWeek = 1/2/3/4/5 AND @IsAlternateDate = 0  FOR nth, for example if finding the 3rd Monday, set @noOfWeek=3

-- EXEC [common].[DateInsert] 1,'','','01/01/2015','12/31/2015',2,0,1
ALTER PROCEDURE [common].[DateInsert] 
@ClinicID int,
@StartTime datetime,
@EndTime datetime,
@Start datetime, 
@End datetime,  
@dayno int ,-- 1=Mon, 2=Tue,... 7=Sun
@noOfWeek int, -- nth, for example if finding the 3rd Monday, set @noOfWeek=3
@IsAlternateDate bit 

AS
BEGIN
SET NOCOUNT OFF
SET FMTONLY OFF
DECLARE @COST DECIMAL(18,2)
  
IF OBJECT_ID('dbo.#t') is not null 
 DROP TABLE dbo.#t;

Declare @StartDateOfMonth date

SELECT @StartDateOfMonth= DATEADD(month, DATEDIFF(month, 0, @Start), 0)

CREATE TABLE #t ([Date] datetime,
  [Year] smallint,
  [Quarter] tinyint,
  [Month] tinyint
, [Day] smallint -- from 1 to 366 = 1st to 366th day in a year
, [Week] tinyint -- from 1 to 54 = the 1st to 54th week in a year; 
, [Monthly_week] tinyint -- 1/2/3/4/5=1st/2nd/3rd/4th/5th week in a month
, [Week_day] tinyint -- 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun
);

-- populate the table #t, and the day of week is defined as
-- 1=Mon, 2=Tue, 3=Wed, 4=Thu,5=Fri, 6=Sat, 7=Sun

;WITH   C0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
  C1   AS (SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B),
  C2   AS (SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B),
  C3   AS (SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B),
  C4   AS (SELECT 1 AS c FROM C3 AS A CROSS JOIN C3 AS B), 
  C5   AS (SELECT 1 AS c FROM C4 AS A CROSS JOIN C3 AS B),
  C6   AS (select rn=row_number() over (order by c)  from C5),
  C7   as (select [date]=dateadd(day, rn-1, @Start) FROM C6 WHERE rn <= datediff(day, @Start, @End)+1)

INSERT INTO #t ([year], [quarter], [month], [week], [day], [monthly_week], [week_day], [date])

SELECT datepart(yy, [DATE]), datepart(qq, [date]), datepart(mm, [date]), datepart(wk, [date])
, datediff(day, dateadd(year, datediff(year, 0, [date]), 0), [date])+1
, datepart(week, [date]) -datepart(week, dateadd(month, datediff(month, 0, [date]) , 0))+1
, CASE WHEN datepart(dw, [date])+@@datefirst-1 > 7 THEN (datepart(dw, [date])+@@datefirst-1)%7
ELSE datepart(dw, [date])+@@datefirst-1 END
, [date]
FROM C7
--where [date] between @Start and @End;

--select * from  #t 

-- find nth week/weekend day of each prev/curr/next month
-- eg. find the 2nd Monday of each prev/curr/next month

; with c as (select 
ROW_NUMBER()OVER (ORDER BY [date])AS ROW,
RNK=RANK() over (partition by [month] order by [date] ASC),
Month,Day,Week,
Monthly_week, [Date] -- attention to 'ASC'
from #t
where Week_Day = @dayno -- 1=Mon, 2=Tue,... 7=Sun
--and Monthly_week>=@noOfWeek
)

select * INTO #t2 from c

--select * from #t2 

select ClinicID = @ClinicID,EffectiveDate = @Start,ForDate = [Date],StartTime=@StartTime,EndTime=@EndTime, IsActive=1 -- ,Monthly_week --,ROW--,rn 
from #t2 

where RNK =CASE WHEN ISNULL(@noOfWeek,0) = 0 THEN RNK ELSE @noOfWeek END-- nth, for example if finding the 3rd Monday, set rn=3
and (ROW%2)<> CASE WHEN @IsAlternateDate=1 THEN 0 ELSE 3 END


END