November 30, 2020 at 9:06 am
I would like to get a Thursday in a month which is followed with 2nd Tuesday. If the output is greater than today, Then I need to show the last month Thursday which is followed with 2nd Tuesday.
For Instance,
CASE 1 - If Today is 30 Nov 2020 -
2nd Tue is 10 Nov 2020. Then the Output is (12 Nov 2020)
CASE 2 - If Today is 1 Dec 2020 - Then, The output should be (12 Nov 2020) -
Reason, 10 Dec 2020 (Thursday followed with 2nd Tue) is future date. 2nd Tue of Dec 2020 is 8th Dec 2020
November 30, 2020 at 2:00 pm
When you write "which is followed with..." it seems you're intending it to mean "which follows the..."? The following has 2 Common Table Expressions: 1) 'test_tues_cte' which contains all of the Tuesday dates in the test month and a row number ordered by date, and 2) 'prior_thurs_cte' which contains all of the Thursday dates in the month prior to the test_date and a row number ordered by date. The rows were generated using a Tally function (which see here) and DATEADD function. Something like this
declare @test_dt date='2020-11-30',
@today date='2020-11-30';
with
test_tues_cte(tues_dt, rn) as (
select tues_dt.dt, row_number() over (order by fn.n) rn
from dbo.fnTally(0, day(eomonth(@test_dt))-1) fn
cross apply (values (dateadd(day, fn.n, datefromparts(year(@test_dt), month(@test_dt), 1)))) tues_dt(dt)
where datepart(weekday, tues_dt.dt)=3),
prior_thurs_cte(thurs_dt, rn) as (
select thurs_dt.dt, row_number() over (order by fn.n) rn
from (values (eomonth(@test_dt, -1))) prior_mo(dt)
cross apply dbo.fnTally(0, day(prior_mo.dt)-1) fn
cross apply (values (dateadd(day, fn.n, datefromparts(year(prior_mo.dt), month(prior_mo.dt), 1)))) thurs_dt(dt)
where datepart(weekday, thurs_dt.dt)=5)
select case when test_thurs_dt.dt>@today
then (select pt.thurs_dt
from prior_thurs_cte pt
where pt.rn=2)
else test_thurs_dt.dt end answer_dt
from test_tues_cte t
cross apply (values (dateadd(day, 2, t.tues_dt))) test_thurs_dt(dt)
where t.rn=2;
Output
answer_dt
2020-11-12
If the test_date is changed to 1 Dec 2020
declare @test_dt date='2020-12-01',
@today date='2020-11-30';
Output
answer_dt
2020-11-12
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 30, 2020 at 3:20 pm
fnTally function is not available. Can you help me? - I'm intended to get Thursday of a month which is followed with 2nd Tuesday
November 30, 2020 at 3:41 pm
Sorry... had to take down this post because I found a bug in the formula.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 3:43 pm
fnTally function is not available. Can you help me? - I'm intended to get Thursday of a month which is followed with 2nd Tuesday
See above.
Steve, if you're going to use the fnTally function, you need to tell people where to get it. Since I use it so often, I just put the link in my signature line and tell people to get it at that link.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 3:58 pm
I had to take down the previous post because I found a bug in the formula. Looking for how to fix it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 4:13 pm
Even if fnTally is not available it's still a useful article to read. Any table with reliably more than 31 rows could be used tho. In this case I created an additional CTE called 'numbers_cte' that contains 31 rows. Then the 2 pre-existing CTE's were altered to use a TOP row goal to select the appropriate number of rows. Also, both CTE's calculate using Tuesday dates and the convert to Thursday when necessary.
declare @test_dt date='2020-12-01',
@today date='2020-11-30';
with
numbers_cte(n) as (
select * from (values (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
(31)) v(n)),
test_tues_cte(tues_dt, rn) as (
select tues_dt.dt, row_number() over (order by fn.n) rn
from (select top(day(eomonth(@test_dt))) * from numbers_cte) fn
cross apply (values (dateadd(day, fn.n, datefromparts(year(@test_dt), month(@test_dt), 1)))) tues_dt(dt)
where datepart(weekday, tues_dt.dt)=3),
prior_tues_cte(tues_dt, rn) as (
select tues_dt.dt, row_number() over (order by fn.n) rn
from (values (eomonth(@test_dt, -1))) prior_mo(dt)
cross apply (select top(day(eomonth(prior_mo.dt))) * from numbers_cte) fn
cross apply (values (dateadd(day, fn.n, datefromparts(year(prior_mo.dt), month(prior_mo.dt), 1)))) tues_dt(dt)
where datepart(weekday, tues_dt.dt)=3)
select case when test_thurs_dt.dt>@today
then (select (dateadd(day, 2, pt.tues_dt))
from prior_tues_cte pt
where pt.rn=2)
else test_thurs_dt.dt end answer_dt
from test_tues_cte t
cross apply (values (dateadd(day, 2, t.tues_dt))) test_thurs_dt(dt)
where t.rn=2;
Output
answer_dt
2020-11-12
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 30, 2020 at 4:16 pm
Steve, if you're going to use the fnTally function, you need to tell people where to get it. Since I use it so often, I just put the link in my signature line and tell people to get it at that link.
It was embedded link in the word here. It works for me 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 30, 2020 at 4:21 pm
I had to take down the previous post because I found a bug in the formula. Looking for how to fix it.
Ah I didn't see what was posted. My code here does seem overly complicated
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 30, 2020 at 4:30 pm
Jeff Moden wrote:Steve, if you're going to use the fnTally function, you need to tell people where to get it. Since I use it so often, I just put the link in my signature line and tell people to get it at that link.
It was embedded link in the word here. It works for me 🙂
Ah... got it. I missed that and, apparently, the OP did too.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 4:35 pm
Ok... One more time. This time, actually verified.
Here's the formula for use with GETDATE(). You can change the two "GETDATE()"s to a column in a table, as well.
SELECT DATEADD(dd,2,DATEADD(dd,(DATEDIFF(dd,'17530101',DATEADD(dd,-DATEPART(dd,GETDATE()),GETDATE()))+6)/7*7,'17530109'))
The following code is to test the formula. The comments explain it all...
--===== Create a test table that contains random dates and times from 1900-01-01 up to and not
-- including 2100-01-01.
DROP TABLE IF EXISTS #MyHead
;
SELECT TOP 100000
SomeDateTime = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+CONVERT(DATETIME,'1900') --Inherently DATETIME
-- SomeDateTime = CONVERT(DATETIME2(7),RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+CONVERT(DATETIME,'1900')) --DATETIME2()
-- SomeDateTime = CONVERT(DATE,ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'1900','2100'))+CONVERT(DATETIME,'1900')) --DATE
INTO #MyHead
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Return the 1st Thursday after the 2nd Tuesday of the month for the given date
-- (and the original date) as well as a weekday name and day check for the date
-- created by the formula. The ThuFollowing2ndTueOfMonth should (obviously)
-- always be a Thursday and it should always be a day between 10 and 16.
WITH cteFindThursday AS
(
SELECT SomeDateTime
,ThuFollowing2ndTueOfMonth = DATEADD(dd,2,DATEADD(dd,(DATEDIFF(dd,'17530101',DATEADD(dd,-DATEPART(dd,SomeDateTime),SomeDateTime))+6)/7*7,'17530109'))
FROM #MyHead
)
SELECT SomeDateTime
,ThuFollowing2ndTueOfMonth
,DoW = DATENAME(dw,ThuFollowing2ndTueOfMonth)
,IsValid = IIF( DATENAME(dw,ThuFollowing2ndTueOfMonth) = 'Thursday'
AND DATEPART(dd,ThuFollowing2ndTueOfMonth) BETWEEN 10 and 16
,1,0)
FROM cteFindThursday
WHERE 0 =IIF( DATENAME(dw,ThuFollowing2ndTueOfMonth) = 'Thursday' --Uncomment this WHERE clause to check for bad dates only
AND DATEPART(dd,ThuFollowing2ndTueOfMonth) BETWEEN 10 and 16
,1,0)
ORDER BY SomeDateTime
;
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 5:42 pm
There is another calendar format is used in the Scandinavian countries and has an ISO standard. It looks like "yyyyW[0-9][0-9]-[1-7]" the W is a number between 01 and 52 or 53 that is the number of the week within that year. The – separates out a number that represents the day of the week (1 = Monday, 7 = Sunday). You can download it and add it to your usual calendar table in the usual ISO-8601 "yyyy-mm-dd" display date. You can find a couple sites in the Internet and actually download the week–within–year display format as text. .
Please post DDL and follow ANSI/ISO standards when asking for help.
November 30, 2020 at 5:53 pm
There is another calendar format is used in the Scandinavian countries and has an ISO standard. It looks like "yyyyW[0-9][0-9]-[1-7]" the W is a number between 01 and 52 or 53 that is the number of the week within that year. The – separates out a number that represents the day of the week (1 = Monday, 7 = Sunday). You can download it and add it to your usual calendar table in the usual ISO-8601 "yyyy-mm-dd" display date. You can find a couple sites in the Internet and actually download the week–within–year display format as text. .
I think you may have posted to the wrong thread, Joe.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 9:08 pm
Ok... One more time. This time, actually verified.
Here's the formula for use with GETDATE(). You can change the two "GETDATE()"s to a column in a table, as well.
SELECT DATEADD(dd,2,DATEADD(dd,(DATEDIFF(dd,'17530101',DATEADD(dd,-DATEPART(dd,GETDATE()),GETDATE()))+6)/7*7,'17530109'))The following code is to test the formula. The comments explain it all...
--===== Create a test table that contains random dates and times from 1900-01-01 up to and not
-- including 2100-01-01.
DROP TABLE IF EXISTS #MyHead
;
SELECT TOP 100000
SomeDateTime = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+CONVERT(DATETIME,'1900') --Inherently DATETIME
-- SomeDateTime = CONVERT(DATETIME2(7),RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+CONVERT(DATETIME,'1900')) --DATETIME2()
-- SomeDateTime = CONVERT(DATE,ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'1900','2100'))+CONVERT(DATETIME,'1900')) --DATE
INTO #MyHead
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Return the 1st Thursday after the 2nd Tuesday of the month for the given date
-- (and the original date) as well as a weekday name and day check for the date
-- created by the formula. The ThuFollowing2ndTueOfMonth should (obviously)
-- always be a Thursday and it should always be a day between 10 and 16.
WITH cteFindThursday AS
(
SELECT SomeDateTime
,ThuFollowing2ndTueOfMonth = DATEADD(dd,2,DATEADD(dd,(DATEDIFF(dd,'17530101',DATEADD(dd,-DATEPART(dd,SomeDateTime),SomeDateTime))+6)/7*7,'17530109'))
FROM #MyHead
)
SELECT SomeDateTime
,ThuFollowing2ndTueOfMonth
,DoW = DATENAME(dw,ThuFollowing2ndTueOfMonth)
,IsValid = IIF( DATENAME(dw,ThuFollowing2ndTueOfMonth) = 'Thursday'
AND DATEPART(dd,ThuFollowing2ndTueOfMonth) BETWEEN 10 and 16
,1,0)
FROM cteFindThursday
WHERE 0 =IIF( DATENAME(dw,ThuFollowing2ndTueOfMonth) = 'Thursday' --Uncomment this WHERE clause to check for bad dates only
AND DATEPART(dd,ThuFollowing2ndTueOfMonth) BETWEEN 10 and 16
,1,0)
ORDER BY SomeDateTime
;
Just a little warning - you should not use a string literal in the function as that will be implicitly converted and could cause cardinality issues. Instead - you can use the integer value which will be natively converted to a datetime value.
SELECT DATEADD(dd,2,DATEADD(dd,(DATEDIFF(dd,-53690,DATEADD(dd,-DATEPART(dd,GETDATE()),GETDATE()))+6)/7*7,-53682))
Normally - this would just be the second Thursday of the month...but for those dates between the 2nd Tuesday and the 2nd Thursday we would have an issue. That is...on 2020-12-09 the Thursday following the 2nd Tuesday that is less than the current/test date would be 2020-12-10.
Declare @testDate date = '2020-12-09';
Select Top 1
ThursdayFollowing = dateadd(day, 2, nwd.nthDate)
From (Values (-1), (0)) As t(n)
Cross Apply dbo.fnGetNthWeekDay(dateadd(month, t.n, @testDate), 2, 2) nwd
Where nwd.nthDate <= @testDate
Order By
nwd.nthDate desc;
This uses the following function:
Create Function dbo.fnGetNthWeekDay (
@theDate datetime
, @theWeekday int
, @theNthDay int
)
Returns Table
As
Return
/*
Adapted from a version published by Peter Larson - with minor modifications for performance
and restructured to eliminate usage of a derived table.
*/
Select theDate = @theDate
, dt.nthDate
From (Values (dateadd(month, datediff(month, @theNthDay, @theDate), 0))) As mm(FirstOfMonth)
Cross Apply (Values (dateadd(day, 7 * @theNthDay - 7 * sign(@theNthDay + 1)
+ (@theWeekday + 6 - datediff(day, -53690, mm.FirstOfMonth) % 7) % 7, mm.FirstOfMonth))) As dt(nthDate)
Where @theWeekday Between 1 And 7
And datediff(month, dt.nthDate, @theDate) = 0
And @theNthDay In (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5);
*** Yes - I really do need to add documentation to the function - just have not gotten around to it yet ***
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 30, 2020 at 10:06 pm
Here's a tvf
drop function if exists dbo.fn_test_scnd_thurs;
go
create function dbo.fn_test_scnd_thurs(
@test_date date)
returns table
as return
select dateadd(day, 9, calc_dt.dt) thurs_dt
from (values (1),(2),(3),(4),(5),(6),(7)) fn(n)
cross apply (values (datefromparts(year(@test_date),
month(@test_date),
1))) dfp(dt)
cross apply (values (dateadd(day, fn.n-1, dfp.dt))) calc_dt(dt)
where datepart(weekday, calc_dt.dt)=3;
go
Query
declare @test_dt date='2020-12-01',
@today date='2020-11-30';
select iif(t.thurs_dt>@today, pr.thurs_dt, t.thurs_dt) answer_dt
from dbo.fn_test_scnd_thurs(@test_dt) t
cross join dbo.fn_test_scnd_thurs(eomonth(@test_dt, -1)) pr;
Output
answer_dt
2020-11-12
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply