Last friday's date that is in a table

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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

  • Neil Burton

    SSC-Insane

    Points: 22249

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

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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.

    That's fantastic Neil, exactly what I needed

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

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