I've googled the hell out of this question with no luck, I've seen countless snippets to get the date of the last Friday using todays date or
passing in a date, what I'm actually after is to work on a table and get the last Friday's date that appears in the table, I say Friday's date it would be great to be able to pass in any day of the week. I've made a dummy table to try this out and my script is below, I'd appreciate any help on this.
USE test
GO
-- Creating Test Table
CREATE TABLE testtable(date datetime, Job VARCHAR(20))
GO
-- Inserting Data into Table
INSERT INTO testtable(date,job)
VALUES('01/Jan/2018','Test1')
INSERT INTO testtable(date,job)
VALUES('09/Dec/2017','Test2')
INSERT INTO testtable(date,job)
VALUES('08/Dec/2017','Test3')
INSERT INTO testtable(date,job)
VALUES('01/Nov/2017','Test4')
INSERT INTO testtable(date,job)
VALUES('01/Dec/2011','Test5')
This should return 8-12-2017, 'Test3'
the date format is irrelevant as I can sort that out when it's returned.Many thanks
Mick
CREATE TABLE #testtable(date datetime, Job VARCHAR(20))
GO
-- Inserting Data into Table
INSERT INTO #testtable(date,job)
VALUES
('01/Jan/2018','Test1')
,('09/Dec/2017','Test2')
,('08/Dec/2017','Test3')
,('01/Nov/2017','Test4')
,('01/Dec/2011','Test5')
DECLARE @Weekday CHAR(9) = 'Friday'
SELECT
date
,job
FROM
#testtable t
JOIN (
SELECT
MAX(DATE) AS last_day
FROM #testtable
CROSS JOIN
(VALUES('Sunday',1)
,('Monday',2)
,('Tuesday',3)
,('Wednesday',4)
,('Thursday',5)
,('Friday',6)
,('Saturday',7)
)d(DoW,dw)
WHERE
datepart(dw,date) = d.dw
AND d.DoW = @Weekday
) ld ON ld.last_day = t.date
DROP TABLE
#testtable
This will do what you want and can be used with any day of the week. Let me know if you've got any questions.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537