Last friday's date that is in a table

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  January 19, 2018 at 6:31 am

  #325502

  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: 23009

  More actions

  January 19, 2018 at 7:24 am

  #1976506

  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

  More actions

  January 19, 2018 at 7:43 am

  #1976510

  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