Get data with the max date

• mike.jones 92577

  SSC Journeyman

  Points: 86

  More actions

  April 25, 2017 at 9:04 am

  #324890

  I wish to get the entry with the highest date, however when I use the code below I get.  Can someone please help and bear in mind I'm new to sql server.  THANK YOU

  

  SELECT dbo.Contacts.Cust_MPAN, MAX(CONVERT(date, dbo.Jobs.PlannedStart, 103)) AS Date, dbo.Jobs.Status, dbo.worksheet_pvt.AbortCategory
  FROM dbo.Jobs
  LEFT OUTER JOIN
  dbo.worksheet_pvt ON dbo.Jobs.JobId = dbo.worksheet_pvt.jobid

  LEFT OUTER JOIN
  dbo.Contacts ON dbo.Jobs.ContactId = dbo.Contacts.ContactId
  GROUP BY dbo.Contacts.Cust_MPAN, dbo.Jobs.Status, dbo.worksheet_pvt.AbortCategory
  HAVING (dbo.Jobs.Status = N'completed with issues') AND (dbo.Contacts.Cust_MPAN = N'2199996080116')

  ORDER BY dbo.Contacts.Cust_MPAN, Date DESC

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 25, 2017 at 9:19 am

  #1939781

  A CTE and ROW_NUMBER should do the trick for you:
  CREATE TABLE #SAMPLE
  (Cust BIGINT,
  [Date] date,
  [Status] varchar(150),
  Category varchar(50));
GO

  INSERT INTO #SAMPLE
VALUES
  (2199996080116, '20160909','Completed with issues', 'UNSAFE'),
  (2199996080116, '20160817','Completed with success', 'NO ACCESS');
GO

  WITH MaxDate AS (
  SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Cust ORDER BY [Date] DESC) RN
  FROM #SAMPLE S)
SELECT Cust, [Date], [Status], Category
FROM MaxDate
WHERE RN = 1;
GO

  --Clean up
DROP TABLE #SAMPLE;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 26, 2017 at 8:18 am

  #1939978

  I'm thinking the net result is something like this:
  WITH SELECTED_DATA AS (

      SELECT C.Cust_MPAN, CONVERT(date, J.PlannedStart, 103) AS [Date], J.[Status], WP.AbortCategory,
        ROW_NUMBER() OVER(PARTITION BY C.Cust_MPAN, J.[Status] ORDER BY CONVERT(date, J.PlannedStart, 103) DESC) AS RN
    FROM dbo.Jobs AS J
        LEFT OUTER JOIN dbo.worksheet_pvt AS WP
            ON J.JobId = WP.jobid
        LEFT OUTER JOIN dbo.Contacts AS C
            ON J.ContactId = C.ContactId
    WHERE J.[Status] = N'completed with issues'
        AND C.Cust_MPAN = N'2199996080116'
)
SELECT *
FROM SELECTED_DATA AS SD
WHERE SD.RN = 1
ORDER BY SD.Cust_MPAN;

  Edit: Query corrected to include DESC for the row number order by.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  April 26, 2017 at 10:02 am

  #1940033

  I've got a question, in the original post you said that you wanted the entry with the most recent date by Contact, but the queries posted here are getting the most recent "completed with issues" Job entry by Contact.  It may not be relevant, but if the most recent Job for a Contact had a different status, would you expect a row in the results or not?

  I notice there is a LEFT OUTER JOIN to Contact, but it is also in the WHERE clause so it seems like it should just be an INNER JOIN.

  Another thing, depending on how many Jobs each Contact has, it may be more efficient to do an APPLY with a TOP 1 correlated subquery:
  
SELECT C.Cust_MPAN, CONVERT(date, LJ.PlannedStart, 103) AS [Date], LJ.[Status], WP.AbortCategory
FROM dbo.Contacts AS C
OUTER APPLY
    (SELECT TOP 1 CONVERT(date, J.PlannedStart, 103) AS [Date], J.[Status]
     FROM dbo.Jobs AS J WHERE J.ContactId = C.ContactId
     WHERE J.[Status] = N'completed with issues'
     ORDER BY CONVERT(date, J.PlannedStart, 103) DESC)) LJ
LEFT OUTER JOIN dbo.worksheet_pvt AS WP ON LJ.JobId = WP.jobid
WHERE C.Cust_MPAN = N'2199996080116'