Neil Burton - Friday, January 19, 2018 7:24 AM

mick burden - Friday, January 19, 2018 6:31 AM

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')
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')

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.