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;