3rd Friday of the Month
-
Hi everyone
I need to find the last business of the week. In most cases this will be a Friday. However, if Friday is a holiday then the last business day is Thursday. The way to know if a Thursday is the last business day is to apply the following logic...The data is stock market data. The markets are closed on holidays so that means there will be gaps in the data. If Friday is a holiday then there is no trading data for that day. So, a Thursday is the last business day of the week if there is no record for Friday. This is what my code does.
I need to modify this code so I know which expiration date is the 3rd Friday of the month (or Thursday if Friday is a holiday).
If the expiration date is a third friday of the month then THIRD_FRIDAY = 'Y' else 'N'. How can I update my code to account for this?
Code:
DROP TABLE IF exists #TestTable
DROP TABLE IF EXISTS #WEEKLASTDAY1;
DROP TABLE IF EXISTS #TEMP1;
CREATE TABLE #TestTable
(
expiration [date] null
)
INSERT INTO #TestTable
VALUES
('2022-03-02'),('2022-03-04'),('2022-03-07'),('2022-03-09'),('2022-03-11'),('2022-03-14'),('2022-03-16'),('2022-03-18'),('2022-03-21'),
('2022-03-23'),('2022-03-25'),('2022-03-28'),('2022-03-30'),('2022-03-31'),('2022-04-01'),('2022-04-04'),('2022-04-06'),('2022-04-08'),
('2022-04-11'),('2022-04-13'),('2022-04-14'),('2022-04-18'),('2022-04-20'),('2022-04-22'),('2022-04-25'),('2022-04-26'),('2022-04-27'),
('2022-04-29'),('2022-05-02'),('2022-05-03'),('2022-05-04'),('2022-05-06'),('2022-05-09'),('2022-05-10'),('2022-05-11'),('2022-05-13'),
('2022-05-16'),('2022-05-17'),('2022-05-18'),('2022-05-19'),('2022-05-20'),('2022-05-23'),('2022-05-24'),('2022-05-25'),('2022-05-26'),
('2022-05-27'),('2022-05-31')
SELECT DISTINCT T1.EXPIRATION
INTO #TEMP1
FROM #TestTable T1
CREATE CLUSTERED INDEX #CI_TEMP1 ON #TEMP1
(EXPIRATION
);
SELECT DISTINCT T1.EXPIRATION,
DATEADD(DAY, -23, T1.EXPIRATION) AS FROM_QUOTE_DATE_23,
DATEADD(DAY, -30, T1.EXPIRATION) AS FROM_QUOTE_DATE_30
INTO #WEEKLASTDAY1
FROM #TEMP1 T1
WHERE DATENAME(WEEKDAY, T1.EXPIRATION) = 'FRIDAY';
CREATE CLUSTERED INDEX #CI_WEEKLASTDAY1 ON #WEEKLASTDAY1
(EXPIRATION
);
INSERT INTO #WEEKLASTDAY1
SELECT DISTINCT T1.EXPIRATION,
DATEADD(DAY, -23, T1.EXPIRATION) AS FROM_QUOTE_DATE_23,
DATEADD(DAY, -30, T1.EXPIRATION) AS FROM_QUOTE_DATE_30
FROM #TEMP1 T1
WHERE DATENAME(WEEKDAY, T1.EXPIRATION) = 'THURSDAY'
AND NOT EXISTS (SELECT *
FROM #TEMP1 W2
WHERE W2.EXPIRATION = DATEADD(DAY, 1, T1.EXPIRATION)
);
SELECT*, '' as 'THIRD_FRIDAY'
FROM#WEEKLASTDAY1
DROP TABLE #TestTableExpected Outcome:
March 18, April 14, and May 20 should be THIRD_FRIDAY = 'Y'. The other days will be 'N'. April 15 is a holiday so April 14 (Thursday) is the last business day of the week.
Thank you
-
It would be simpler and more efficient to just calc the 3rd Friday of the month and then see if there are any rows for that date, i.e., you don't need to go thru a list of dates. All you need is a min and max date (so you know which months you need to calc the 3rd Fridays for).
Here's how to calc the 3rd Fridays. I'm not exactly sure what table you need to check for a date present, so I haven't coded that part. It would be a CASE WHEN EXISTS()NOT EXISTS to subtract 0/-1 from the 3rdFriday to arrive at the FinalDate.
DECLARE @end_date date;
DECLARE @start_date date;
SET @start_date = '20220302'
SET @end_date = '20220531'
/*
SELECT @start_date = MIN(Expiration), @end_date = MAX(Expiration)
FROM <table_name>
*/;WITH
cte_tally10 AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
)
SELECT [3rdFriday]
FROM cte_tally10 t
CROSS APPLY (
SELECT DATEADD(DAY, 20, DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date) + t.number, 0)) AS Day21
) AS ca1
CROSS APPLY (
SELECT CAST(DATEADD(DAY, -DATEDIFF(DAY, 4, Day21) % 7, Day21) AS date) AS [3rdFriday]
) AS ca2
WHERE t.number BETWEEN 0 AND DATEDIFF(MONTH, @start_date, @end_date)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".
- ScottPletcher wrote:
It would be simpler and more efficient to just calc the 3rd Friday of the month and then see if there are any rows for that date, i.e., you don't need to go thru a list of dates. All you need is a min and max date (so you know which months you need to calc the 3rd Fridays for).
Here's how to calc the 3rd Fridays. I'm not exactly sure what table you need to check for a date present, so I haven't coded that part. It would be a CASE WHEN EXISTS()NOT EXISTS to subtract 0/-1 from the 3rdFriday to arrive at the FinalDate.
DECLARE @end_date date;
DECLARE @start_date date;
SET @start_date = '20220302'
SET @end_date = '20220531'
/*
SELECT @start_date = MIN(Expiration), @end_date = MAX(Expiration)
FROM <table_name>
*/;WITH
cte_tally10 AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
)
SELECT [3rdFriday]
FROM cte_tally10 t
CROSS APPLY (
SELECT DATEADD(DAY, 20, DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date) + t.number, 0)) AS Day21
) AS ca1
CROSS APPLY (
SELECT CAST(DATEADD(DAY, -DATEDIFF(DAY, 4, Day21) % 7, Day21) AS date) AS [3rdFriday]
) AS ca2
WHERE t.number BETWEEN 0 AND DATEDIFF(MONTH, @start_date, @end_date)Thank you for this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply