July 3, 2015 at 9:36 am
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?
Why use a CASE statement to derive @DateNum ? Why not use DATEPART(WeekDay, @StartDate) or any other date?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 3, 2015 at 10:26 am
The idea was to not make it dependent on the DATEFIRST setting and the basis there was no calendar table. Using DATEDIFF(DAY,0,@StartDate)%7 you know that 0 will always be the Monday. Rather than generating all the dates from the range and then filtering it down, was just playing around with finding the first of the required days in the range then adding the multiples of 7 to get the subsequent days.
As I said boredom and a bit of fun!
July 6, 2015 at 5:54 pm
Here's a completely setting-independent way to calc only Fridays; it's actually a straight-forward task. Note: Since my software at work blocks CTEs (as some type of "injection"), I had to code it using a stored "standard" tally table. If you don't have a tally table, you can easily change it to a tally CTE, or just go ahead and create a physical tally table ;-):
DECLARE @start_year smallint
DECLARE @end_year smallint
SET @start_year = 2014
SET @end_year = 2016
SELECT
--back up to actual first Friday date from max possible first Friday date
DATEADD(DAY, -DATEDIFF(DAY, 4, month_day_7) % 7, month_day_7) AS Friday_Date
FROM (
--The "- 1"/"+ 1" is just to allow use of a tally table without a zero row in it
SELECT @start_year + years.tally - 1 AS year
FROM dbo.tally years
WHERE
years.tally BETWEEN 1 AND @end_year - @start_year + 1
) AS years
INNER JOIN dbo.tally months ON
months.tally BETWEEN 1 AND 12
CROSS APPLY (
SELECT CAST(CAST(years.year * 10000 + months.tally * 100 + 7 AS char(8)) AS date) AS month_day_7
) AS last_possible_Friday_day_for_each_month
ORDER BY Friday_Date
Edit: Edited comments.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 21, 2021 at 3:58 pm
solved!
1st friday : (SELECT DATEADD(DAY, DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
2nd friday : (SELECT DATEADD(DAY, 7 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
3rd friday : (SELECT DATEADD(DAY, 14 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
Ignore following!
5th Business Day SQL : (SELECT DATEADD(DAY, 6 - DECODE(EXTRACT(DOW FROM START_DATE), 1, 2,0, 1, 0), START_DATE) FROM (SELECT date_trunc('month',current_date) START_DATE))
September 21, 2021 at 4:32 pm
solved!
1st friday : (SELECT DATEADD(DAY, DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
2nd friday : (SELECT DATEADD(DAY, 7 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
3rd friday : (SELECT DATEADD(DAY, 14 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))
Ignore following!
5th Business Day SQL : (SELECT DATEADD(DAY, 6 - DECODE(EXTRACT(DOW FROM START_DATE), 1, 2,0, 1, 0), START_DATE) FROM (SELECT date_trunc('month',current_date) START_DATE))
Welcome. You realise that you posted in a SQL Server forum, I hope 🙂 Your code won't work there.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 21, 2021 at 6:44 pm
Fwiw in 2021. In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month. Then in the WHERE clause filter on datename(dw, calc_day.day_dt)='Friday'. Maybe like this
declare
@start_year smallint=2014,
@end_year smallint=2016;
declare
@start_dt date=datefromparts(@start_year, 1, 1),
@end_dt date=datefromparts(@end_year, 12, 31);
select calc_day.day_dt as first_friday
from dbo.fnTally(0, datediff(month, @start_dt, @end_dt)) fn
cross apply (values (dateadd(month, fn.n, @start_dt))) calc_mo(mo_dt)
cross apply dbo.fnTally(0, 6) sev
cross apply (values (dateadd(day, sev.n, calc_mo.mo_dt))) calc_day(day_dt)
where datename(dw, calc_day.day_dt)='Friday'
order by calc_day.day_dt;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 21, 2021 at 6:48 pm
Fwiw in 2021. In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month. Then in the WHERE clause filter on datename(dw, calc_day.day_dt)='Friday'. Maybe like this
declare
@start_year smallint=2014,
@end_year smallint=2016;
declare
@start_dt date=datefromparts(@start_year, 1, 1),
@end_dt date=datefromparts(@end_year, 12, 31);
select calc_day.day_dt as first_friday
from dbo.fnTally(0, datediff(month, @start_dt, @end_dt)) fn
cross apply (values (dateadd(month, fn.n, @start_dt))) calc_mo(mo_dt)
cross apply dbo.fnTally(0, 6) sev
cross apply (values (dateadd(day, sev.n, calc_mo.mo_dt))) calc_day(day_dt)
where datename(dw, calc_day.day_dt)='Friday'
order by calc_day.day_dt;
But why all that extra overhead when a simple calc will give you the date you need?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 21, 2021 at 7:25 pm
You ran a comparison?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 21, 2021 at 9:16 pm
Fwiw in 2021. In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month. Then in the WHERE clause filter on datename(dw, calc_day.day_dt)='Friday'. Maybe like this
declare
@start_year smallint=2014,
@end_year smallint=2016;
declare
@start_dt date=datefromparts(@start_year, 1, 1),
@end_dt date=datefromparts(@end_year, 12, 31);
select calc_day.day_dt as first_friday
from dbo.fnTally(0, datediff(month, @start_dt, @end_dt)) fn
cross apply (values (dateadd(month, fn.n, @start_dt))) calc_mo(mo_dt)
cross apply dbo.fnTally(0, 6) sev
cross apply (values (dateadd(day, sev.n, calc_mo.mo_dt))) calc_day(day_dt)
where datename(dw, calc_day.day_dt)='Friday'
order by calc_day.day_dt;
I was looking at that with a bit of a skeptical eye but that's pretty damned clever. It's also a different take on something that I've been working. I'm going to have to do some more testing.
Thanks for the post and the code, Steven.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 21, 2021 at 9:54 pm
Thank you Jeff. It's my pleasure of course. Please let me know if there's anything else I could do
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 21, 2021 at 10:47 pm
Steve Collins wrote:Fwiw in 2021. In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month. Then in the WHERE clause filter on datename(dw, calc_day.day_dt)='Friday'. Maybe like this
declare
@start_year smallint=2014,
@end_year smallint=2016;
declare
@start_dt date=datefromparts(@start_year, 1, 1),
@end_dt date=datefromparts(@end_year, 12, 31);
select calc_day.day_dt as first_friday
from dbo.fnTally(0, datediff(month, @start_dt, @end_dt)) fn
cross apply (values (dateadd(month, fn.n, @start_dt))) calc_mo(mo_dt)
cross apply dbo.fnTally(0, 6) sev
cross apply (values (dateadd(day, sev.n, calc_mo.mo_dt))) calc_day(day_dt)
where datename(dw, calc_day.day_dt)='Friday'
order by calc_day.day_dt;I was looking at that with a bit of a skeptical eye but that's pretty damned clever. It's also a different take on something that I've been working. I'm going to have to do some more testing.
Thanks for the post and the code, Steven.
It looks very similar to my approach, except using iteration and strings rather than a numeric calc. I start at the 7th day of the month -- the last possible day for the first Fri of a month -- and use a single numeric date calc to "go back" to the actual first Friday date.
If DATEFROMPARTS is available on the version of SQL one is using, that function should indeed be used to calc the month start date. It is more efficient; I just don't like to assume that people have it available to them when posting code for everyone to use.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 21, 2021 at 10:53 pm
Can you post your code, Scott?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 21, 2021 at 10:55 pm
Sure, it's above; I posted it years ago, when this q was first asked.
Actually I should adjust it again, you only need to calc the very first Friday, from then on you can just add 7 days.
As noted, I had to use an actual tally table rather than a CTE, which I prefer, because of a malfunctioning filter at work at the time that took any CTE as some type of hack/attack attempt.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 22, 2021 at 1:03 pm
Here's another version which replaces the second dbo.fnTally tvf with an explicit values table (0-6) and which eliminates the outer WHERE clause by adding the row filter to the final CROSS APPLY. (Edited: fixed mistakes)
/* 2nd attempt */
select calc_day.day_dt as first_friday
from dbo.fnTally(0, datediff(month, @start_dt, @end_dt)) fn
cross apply (values (dateadd(month, fn.n, @start_dt))) calc_mo(mo_dt)
cross apply (select cc.c_dt
from (values (0),(1),(2),(3),(4),(5),(6)) sev(n)
cross apply (values (dateadd(day, sev.n, calc_mo.mo_dt))) cc(c_dt)
where datename(dw, cc.c_dt)='Friday') calc_day(day_dt)
order by calc_day.day_dt;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 23, 2021 at 10:25 pm
Sure, it's above; I posted it years ago, when this q was first asked.
Actually I should adjust it again, you only need to calc the very first Friday, from then on you can just add 7 days.
As noted, I had to use an actual tally table rather than a CTE, which I prefer, because of a malfunctioning filter at work at the time that took any CTE as some type of hack/attack attempt.
Ah, sorry. I missed it before.
As a bit of a side bar on the malfunctioning "filter" you spoke of... WOW! Seriously? Did they ever fix that?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 61 total)
You must be logged in to reply to this topic. Login to reply