Alvin Ramard (7/3/2015)

---

Dohsan (7/3/2015)

---

Alvin Ramard (7/2/2015)

---

vipin_jha123 (7/2/2015)

---

Hi,

I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.

Please suggest me the logic

Regards,

Vipin jha

Try this in your where clause:

WHERE DATEPART(YEAR, [yourDateColumn]) IN (2014, 2015, 2016)

AND DATEPART(DAY, [yourDateColumn]) <= 7

AND DATENAME(WEEKDAY, [yourDateColumn]) = 'Friday'

Gave it another go to see if i could modify my code and make it work any better, this should work for finding the first day of the month from any range (although limited to first week). Wasn't sure if using MIN over ROW_NUMBER improved it much as I still needed to sort the final result set. At least using ROW_NUMBER you could then add in the variable of which week.

DECLARE@StartDateDATETIME = '20140101',

@EndDateDATETIME = '20161231',

@DayNameVARCHAR(10) = 'Friday',

@DateNumINT;

SELECT@DateNum = CASE @DayName

WHEN 'Monday' THEN 0

WHEN 'Tuesday' THEN 1

WHEN 'Wednesday' THEN 2

WHEN 'Thursday' THEN 3

WHEN 'Friday' THEN 4

WHEN 'Saturday' THEN 5

WHEN 'Sunday' THEN 6

END;

WITH DateBase(DT)

AS

(

--From StartDate work out next day that is the required day to use as start base

SELECTDATEADD(DAY,CA1.DTAdjust,DATEDIFF(DAY,0,@StartDate))

FROM(

SELECT DATEDIFF(DAY,0,@StartDate)%7

) AS A(DTMod)

CROSS

APPLY(

SELECTCASE

WHEN A.DTMod < @DateNum THEN @DateNum - A.DTMod

WHEN A.DTMod > @DateNum THEN @DateNum + 7 - A.DTMod

ELSE A.DTMod

END

) AS CA1(DTAdjust)

)

SELECTMIN(B.DT)

FROM(

SELECTDATEADD(DAY,N*7,B.DT)

FROMDateBase AS B

CROSS

APPLYdbo.GetNums(0,DATEDIFF(DAY,@StartDate,@EndDate)/7)

) AS B(DT)

GROUPBY YEAR(B.DT),

MONTH(B.DT)

ORDERBY YEAR(B.DT),

MONTH(B.DT);

Row_Number

DECLARE@StartDateDATETIME = '20140101',

@EndDateDATETIME = '20161231',

@DayNameVARCHAR(10) = 'Friday',

@DateNumINT,

@WeekNumINT = 1;

SELECT@DateNum = CASE @DayName

WHEN 'Monday' THEN 0

WHEN 'Tuesday' THEN 1

WHEN 'Wednesday' THEN 2

WHEN 'Thursday' THEN 3

WHEN 'Friday' THEN 4

WHEN 'Saturday' THEN 5

WHEN 'Sunday' THEN 6

END;

WITH DateBase(DT)

AS

(

--From StartDate work out next day thatis the required day to use as start base

SELECTDATEADD(DAY,CA1.DTAdjust,DATEDIFF(DAY,0,@StartDate))

FROM(

SELECT DATEDIFF(DAY,0,@StartDate)%7

) AS A(DTMod)

CROSS

APPLY(

SELECTCASE

WHEN A.DTMod < @DateNum THEN @DateNum - A.DTMod

WHEN A.DTMod > @DateNum THEN @DateNum + 7 - A.DTMod

ELSE A.DTMod

END

) AS CA1(DTAdjust)

)

SELECTBS1.DT

FROM(

SELECTBS.DT,

RN = ROW_NUMBER() OVER (PARTITION BY YEAR(BS.DT),MONTH(BS.DT) ORDER BY BS.DT)

FROM(

SELECTDATEADD(DAY,N*7,B.DT)

FROMDateBase AS B

CROSS

APPLYdbo.GetNums(0,DATEDIFF(DAY,@StartDate,@EndDate)/7)

) AS BS(DT)

) AS BS1(DT,RN)

WHEREBS1.RN = @WeekNum;

Why are you trying to make everything so complicated?