SQL query i need each Friday count back 6 days and select each ticker record

Question

Post reply

SQL query i need each Friday count back 6 days and select each ticker record

angang

SSC Enthusiast

Points: 114
More actions
February 29, 2024 at 6:25 am

#4386837

I have a stock market table that includes 70 tickers per day:
ticker date, ticker, close, open
I need to be able to read the previous 5 Friday dates, and for each Friday count back 6 days and select each ticker record including open, close.
It doesn't have holiday dates or weekends on the table but would have to account for holidays if a straight up count example:
for friday 2-23-2024, the records selected should be for 2-15, as 2-19 was a holiday. i have this so far but not working as intended
WITH CTE AS (
SELECT
s.[ticker],
s.[Name],
s.[TickerType],
s.[Tickerdate],
s.[adjustedprice],
s.[unadjustedprice],
s.[open],
s.[low],
s.[high],
s.[volume],
s.[unadjopen],
s.[unadjlow],
s.[unadjhigh],
s.[__FileName],
ROW_NUMBER() OVER (PARTITION BY s.[Tickerdate] ORDER BY s.[Tickerdate] DESC) AS rn,
CASE
WHEN EXISTS (SELECT 1 FROM Holidays WHERE HolidayDate = s.[Tickerdate]) THEN 7
ELSE 6
END AS count_back,
ISNULL((
SELECT TOP 1 Tickerdate
FROM SectorETFsNew
WHERE Tickerdate < s.Tickerdate
AND NOT EXISTS (SELECT 1 FROM Holidays WHERE HolidayDate = Tickerdate)
ORDER BY Tickerdate DESC, ticker
), s.Tickerdate) AS actual_date
FROM
[dbo].[SectorETFsNew] s
)
SELECT
c.[ticker],
c.[Name],
c.[TickerType],
c.[actual_date] AS Tickerdate,
c.[adjustedprice],
c.[unadjustedprice],
c.[open],
c.[low],
c.[high],
c.[volume],
c.[unadjopen],
c.[unadjlow],
c.[unadjhigh],
c.[__FileName],
c.rn,c.count_back
FROM
CTE c
WHERE
c.rn <= c.count_back
ORDER BY
c.[actual_date] DESC,
c.[ticker];
March 3, 2024 at 1:38 am

This was removed by the editor as SPAM

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

angang wrote:

I need to be able to read the previous 5 Friday dates...

A good place to start could be with a query to generate the most recent 5 Friday dates based on relative dating, i.e. no matter when you run it. Since there's no sample data the question needs to be broken down into simple parts. This query uses integer division and makes use of the lowest date (day 0) in SQL Server being a Monday

/* always returns the most recent 5 friday dates */
select v.n, datename(dw,fri.calc_dt) day_name, fri.calc_dt-v.n*7 last_5_fridays
from (values (0),(1),(2),(3),(4)) v(n) 
     cross join (values (dateadd(day, datediff(day, 0, getdate())/7*7+4, 0))) fri(calc_dt);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können