3rd Friday of the Month

Question

Post reply

3rd Friday of the Month

water490

SSC Eights!

Points: 918

March 19, 2024 at 4:15 am

#4397863

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 #TestTable

Expected Outcome:

Screenshot 2024-03-18 211008

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100949 More actions · Answer 1

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) A socialist is someone who will give you the shirt off *someone else's* back.

water490 SSC Eights! Points: 918 More actions · Answer 2

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.

This reply was modified 1 month, 1 week ago by water490.
This reply was modified 1 month, 1 week ago by water490.