September 23, 2021 at 10:29 pm
ScottPletcher wrote: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?
Yeah, took 'em about two weeks, but they did. We were reviewing all our security measures as part of an audit, so they had to go extreme on anything that could even potentially be an issue.
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 23, 2021 at 11:45 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;
I've amended your code. I think this should be a few times faster as it removes the need for a tally of 7 week days and just calculates the offset needed from the first weekday of the first day of the month to get the first Friday.
set datefirst 7
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);
declare @weekday int = 6 -- 6=Friday
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 (values(dateadd(dd,((@weekday-datepart(weekday,calc_mo.mo_dt))+7)%7,calc_mo.mo_dt))) calc_day(day_dt)
September 24, 2021 at 4:11 am
You ran a comparison?
Would we need to? A single math calc vs iterating thru string generations and comparisons?
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 24, 2021 at 6:18 am
Heh... one good test result is worth a thousand expert opinions. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2021 at 12:05 pm
I've amended your code. I think this should be a few times faster as it removes the need for a tally of 7 week days and just calculates the offset needed from the first weekday of the first day of the month to get the first Friday.
set datefirst 7
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);
declare @weekday int = 6 -- 6=Friday
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 (values(dateadd(dd,((@weekday-datepart(weekday,calc_mo.mo_dt))+7)%7,calc_mo.mo_dt))) calc_day(day_dt)
This is awesome. Thank you Jonathan. I will take a close look at this and run some comparisons. Much appreciated
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 24, 2021 at 12:28 pm
Heh... one good test result is worth a thousand expert opinions. 😀
This 🙂
Steve Collins wrote:You ran a comparison?
Would we need to? A single math calc vs iterating thru string generations and comparisons?
All right Scott maybe you're right and I'm wrong. If I change my original code so that: 1) it filters for a specific day, and 2) increase the tally function row target to a big number... it takes forever to run. Which seems to suggest there is some iteration happening
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 24, 2021 at 12:57 pm
Jonathan AC Roberts wrote:I've amended your code. I think this should be a few times faster as it removes the need for a tally of 7 week days and just calculates the offset needed from the first weekday of the first day of the month to get the first Friday.
set datefirst 7
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);
declare @weekday int = 6 -- 6=Friday
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 (values(dateadd(dd,((@weekday-datepart(weekday,calc_mo.mo_dt))+7)%7,calc_mo.mo_dt))) calc_day(day_dt)This is awesome. Thank you Jonathan. I will take a close look at this and run some comparisons. Much appreciated
I'd be real careful with that. It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations. I avoid its use entirely.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2021 at 3:35 pm
I'd be real careful with that. It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations. I avoid its use entirely.
It's somewhat better than what I had before. Are you not in a code writing mood today? Casting doubt tho 😉
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 24, 2021 at 3:56 pm
I'd be real careful with that. It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations. I avoid its use entirely.
At the top of the code I put set datefirst 7 which should prevent any of those issues.
Should also note that the return from DATENAME is also subject to change in different languages.
September 24, 2021 at 4:04 pm
Jeff Moden wrote:I'd be real careful with that. It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations. I avoid its use entirely.
At the top of the code I put
set datefirst 7which should prevent any of those issues.Should also note that the return from
DATENAMEis also subject to change in different languages.
The SET DATEFIRST could cause other issues, esp. if the local @@DATEFIRST setting is different. You could cause bad results from other functions by setting DATEFIRST to an unexpected value. In short, any date-related code you write should work regardless of DATEFIRST setting.
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 24, 2021 at 4:13 pm
Jonathan AC Roberts wrote:Jeff Moden wrote:I'd be real careful with that. It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations. I avoid its use entirely.
At the top of the code I put
set datefirst 7which should prevent any of those issues.Should also note that the return from
DATENAMEis also subject to change in different languages.The SET DATEFIRST could cause other issues, esp. if the local @@DATEFIRST setting is different. You could cause bad results from other functions by setting DATEFIRST to an unexpected value. In short, any date-related code you write should work regardless of DATEFIRST setting.
I've rewritten so DATEFIRST is not changed and also guarantees the weekday selected is a Friday (or any other weekday you specify in text):
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);
declare @weekday int =(select datepart(weekday,dt.Date)
from (values ('20210101'),('20210102'),('20210103'),('20210104'),('20210105'),('20210106'),('20210107'))dt(Date)
where datename(weekday,dt.Date)='Friday')
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 (values(dateadd(dd,((@weekday-datepart(weekday,calc_mo.mo_dt))+7)%7,calc_mo.mo_dt))) calc_day(day_dt)
Hopefully you and Jeff will be ok with it?
September 24, 2021 at 6:16 pm
I tested it with a couple of different DATEFIRST settings. It appears to work correctly now.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2021 at 6:34 pm
Over the years, and especially this last 18 months, it seems like the problem of calculating the Nth Occurrence of some weekday has come up over and over. It always turns out to be a fairly long winded thread and is sometimes resurrected from years earlier. Combine that with questions like "How can I calculate the first of every month" or "How can I calculate the last day of every month" or "How can I calculate the last <insert weekday name here> of every month, and you end up with a whole bunch of common questions usually solved only by calendar tables or code that calculates way too many days and then filters them and a whole bunch of Nimrods that say "performance doesn't matter because of the small rowcount" or whatever.
I've even had a discussion with a well known personality that has published a shedload of code and it's a riddled with While Loops and his excuse is that he "only uses While Loops when they're absolutely necessary".
I've also never seen anyone request to do any such calculations for dates earlier than the year 1900 and that makes the calculations even easier. With that, I wrote a function to handle any and all of that with just a few parameters. It contains its own Tally cCTE so people aren't put off by having a function call a function (which would be ok with me for this because both are iTVFs) or because they don't wanna have two functions. Waaaa!!!
So, I finished the following up yesterday. I'd like to publish it in an article here on SSC for the community to use and improve on kind of like the community did with DelimitedSplit8K but wanted to run it past you gents first for any immediate suggestions that would end up in the Revision History in the flower box. And, yes... internally, it generates just one row per month for working dates.
CREATE OR ALTER FUNCTION dbo.NthWeekdayOfMonth
/**********************************************************************************************************************
Purpose:
Generate particular days of the month for a range of months.
-----------------------------------------------------------------------------------------------------------------------
Syntax:
--===== Basic Syntax
SELECT DoMDate
FROM dbo.NthWeekdayOfMonth (@LoDate,@HiDate,@Week,@DoW)
;
Parameters:
@LoDate - Date containing the starting month of the range of months to be returned.
- Must be >= '1900-01-01'.
@HiDate - Date containing the ending month of the range of months to be returned.
Must be >= '1900-01-01' and the YYYY/MM combination must be >= than the YYYY/MM combination of @LoDate.
Otherwise, a "A TOP N or FETCH rowcount value may not be negative." will be returned.
For a single month, this can be any date in the same month as @LoDate (usually, the same date as @LoDate)
@Week - The week of the month the desired day of the week will be returned from. Must be one of the following.
0 = The first day of each month regardless of the value of @DoW.
1-4 = The week number to extract the day of the week from.
This setting is used for things like finding the 2nd Thursday of each month.
5 = The last week of the month, which can be the 4th or 5th week depending on the month.
This setting is handy for things like finding the last Wednesday of each month.
6 = The last day of each month regardless of the value of @DoW.
@DoW - Identifies the desired day of the week to return. 1 = Monday thru 7 = Sunday. Must be 1 thru 7.
- This setting is ignored when the @Week setting is not between 1 and 5.
DoMDate - One or more dates according to the inputs above.
Exception: If any of the inputs are incorrect, and empty set will be returned.
-----------------------------------------------------------------------------------------------------------------------
Programmer Notes:
1. Optimized to internally create only one day per given month rather than creating multiple dates per month and then
selecting the right ones.
2. No Developers or DBAs were harmed in the making of this code. ;)
-----------------------------------------------------------------------------------------------------------------------
Usage Examples:
Note that the dates can be of any format that SQL Server/T-SQL can convert to a date.
--===== Return the first day of each month for a range of months.
SELECT DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',0,1)
;
--===== Return the 3rd Thursday of each month for a range of months.
SELECT DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',3,4)
;
--===== Return the last Tuesday of each month for a range of months.
SELECT DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',5,2)
;
--===== Return the last day of each month for a range of months.
SELECT DoMDate
FROM dbo.NthWeekdayOfMonth('Jun 2015','2021-10-15',6,1)
;
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 20 Mar 2020 - Jeff Moden
- Proof of Principle for single date.
Rev 01 - 21 Sep 2021 - Proof of Principle using fnTally() function to return a range of dates.
Rev 02 - 22 Sep 2021 - Proof of Principle using inline cCTE to return a range of dates.
Rev 03 - 23 Sep 2021 - Jeff Moden
- Optimize, convert to documented iTVF, final unit test.
**********************************************************************************************************************/--===== Function I/O
(
@LoDate DATE
,@HiDate DATE
,@Week TINYINT
,@DoW TINYINT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
--======= Inline "Tally" sequence generator (0 up to/thru 100,000)
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
, E5(N) AS (SELECT TOP (DATEDIFF(mm,@LoDate,@HiDate)) 1 FROM E1 a,E1 b,E1 c,E1 d,E1 e)
,Tally(N) AS (SELECT 0 UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E5)
--======= Function Body
SELECT DoMDate = CONVERT(DATE
,CASE
--== First Day of Month
WHEN @Week = 0 THEN fom.DT
--== 1-4 = Week of month OR 5 = last DOW for the month
WHEN @Week BETWEEN 1 AND 5 THEN DATEADD(dd,-IIF(MONTH(tgt.DT) = MONTH(fom.DT),0,7),tgt.DT )
--== Last Day of Month
WHEN @Week = 6 THEN EOMONTH(fom.DT)
END
)
FROM Tally t --Month count sequence starting at 0
CROSS APPLY (VALUES (DATEADD(mm,DATEDIFF(mm,0,@LoDate)+t.N,0)))fom(DT) -- First of Month
CROSS APPLY (VALUES (DATEADD(dd,DATEDIFF(dd,@DoW-1,DATEADD(dd,(@Week*7)-1,fom.DT))/7*7,@DoW-1)))tgt(DT) --DoMDate Raw
WHERE @LoDate >= '19000101' -- No dates prior to 1900-01-01
AND @Week BETWEEN 0 AND 6 -- See comments for CASE expression in SELECT list
AND @DoW BETWEEN 1 AND 7 -- Day of week must be 1 to 7, Mon to Sun
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2021 at 6:37 pm
p.s. I hate the double spacing this forum does with code. It makes even well formatted code look like junk.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2021 at 7:14 pm
Ha, nice Jeff. @@DATEFIRST returns 7 on my test instance so no worries there. In the tvf it seems possible to factor out a common table expression and the UNION ALL by adding +1 to the row goal and -1 to the sequence accessor. Maybe like this
CREATE OR ALTER FUNCTION dbo.NthWeekdayOfMonth
(
@LoDate DATE
,@HiDate DATE
,@Week TINYINT
,@DoW TINYINT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
--======= Inline "Tally" sequence generator (0 up to/thru 100,000)
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))
,Tally(N) AS (SELECT TOP (DATEDIFF(mm,@LoDate,@HiDate)+1)
ROW_NUMBER() OVER (ORDER BY (SELECT Null))
FROM E1 a,E1 b,E1 c,E1 d,E1 e)
--======= Function Body
SELECT DoMDate = CONVERT(DATE
,CASE
--== First Day of Month
WHEN @Week = 0 THEN fom.DT
--== 1-4 = Week of month OR 5 = last DOW for the month
WHEN @Week BETWEEN 1 AND 5 THEN DATEADD(dd,-IIF(MONTH(tgt.DT) = MONTH(fom.DT),0,7),tgt.DT )
--== Last Day of Month
WHEN @Week = 6 THEN EOMONTH(fom.DT)
END
)
FROM Tally t --Month count sequence starting at 0
CROSS APPLY (VALUES (DATEADD(mm,DATEDIFF(mm,0,@LoDate)+t.N-1,0)))fom(DT) -- First of Month
CROSS APPLY (VALUES (DATEADD(dd,DATEDIFF(dd,@DoW-1,DATEADD(dd,(@Week*7)-1,fom.DT))/7*7,@DoW-1)))tgt(DT) --DoMDate Raw
WHERE @LoDate >= '19000101' -- No dates prior to 1900-01-01
AND @Week BETWEEN 0 AND 6 -- See comments for CASE expression in SELECT list
AND @DoW BETWEEN 1 AND 7 -- Day of week must be 1 to 7, Mon to Sun
;
GO
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 31 through 45 (of 61 total)
You must be logged in to reply to this topic. Login to reply