Select oldest dates from 2 columns

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  January 14, 2019 at 3:23 am

  #370064

  Hi Guys,

  Is there a way to select the oldest dates from more than one column.  With regards to the below data I want to be able to select the oldest data based on the NextServ and MotDate.  
  The below holds duplicate data but i would like the results to only show the oldest dates.  So when the below is run its shows as per the uploaded image

  Hope this makes sense.

  Thanks

  -- Sample data
  IF OBJECT_ID('tempdb..#lab') IS NOT NULL DROP TABLE #lab
  SELECT * INTO #lab FROM (VALUES
  ( 'CA', '101', '2019-01-14 00:00:00.000', '2018-06-30 00:00:00.000' ),
  ( 'NF', '200', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'NF', '200', '2019-01-14 00:00:00.000', '2019-10-08 00:00:00.000' ),
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  ) d

  ( DEAL, REFNO, NextServ, MotDate)

  SELECT DEAL, REFNO, NextServ, MotDate FROM #lab

• debasis.yours

  Hall of Fame

  Points: 3402

  More actions

  January 14, 2019 at 3:40 am

  #2018807

  Hi,

  Does this help?
  
;WITH MinDate(DateValue)AS
(SELECT MIN(NextServ) AS MinDate FROM #lab UNION
SELECT MIN(MotDate) FROM #lab)
SELECT MIN(DateValue) FROM MinDate


• Jonathan AC Roberts

  SSCoach

  Points: 18257

  More actions

  January 14, 2019 at 3:52 am

  #2018808

  Your jpg file attachment is showing a later date for CF, is this what you meant. I've assumed you didn't?
  Here are two different ways to get the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.DEAL, X.REFNO, X.NextServ, X.MotDate
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT DEAL, REFNO, NextServ, MotDate
  FROM CTE
WHERE RowNum = 1;

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  January 14, 2019 at 3:52 am

  #2018809

  .

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  January 14, 2019 at 3:54 am

  #2018810

  Jonathan AC Roberts - Monday, January 14, 2019 3:52 AM

  In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?
  These two different ways will show the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.*
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT *
  FROM CTE
WHERE RowNum = 1;

  Amazing thank you so much

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  January 14, 2019 at 4:06 am

  #2018811

  craig.jenkins - Monday, January 14, 2019 3:54 AM

  Jonathan AC Roberts - Monday, January 14, 2019 3:52 AM

  In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?
  These two different ways will show the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.*
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT *
  FROM CTE
WHERE RowNum = 1;

  Amazing thank you so much

  Sorry, just noticed your text above
  'In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?  Yes i am looking for the older date in CF as well so the result should be as per attachement.  The oldest date in either column

  

• Jonathan AC Roberts

  SSCoach

  Points: 18257

  More actions

  January 14, 2019 at 4:24 am

  #2018812

  craig.jenkins - Monday, January 14, 2019 4:06 AM

  craig.jenkins - Monday, January 14, 2019 3:54 AM

  Jonathan AC Roberts - Monday, January 14, 2019 3:52 AM

  In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?
  These two different ways will show the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.*
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT *
  FROM CTE
WHERE RowNum = 1;

  Amazing thank you so much

  Sorry, just noticed your text above
  'In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?  Yes i am looking for the older date in CF as well so the result should be as per attachement.  The oldest date in either column

  

  In your insert you have:
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' is the oldest date for CF, not as in your attachment.

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  January 14, 2019 at 4:35 am

  #2018813

  Jonathan AC Roberts - Monday, January 14, 2019 4:24 AM

  craig.jenkins - Monday, January 14, 2019 4:06 AM

  craig.jenkins - Monday, January 14, 2019 3:54 AM

  Jonathan AC Roberts - Monday, January 14, 2019 3:52 AM

  In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?
  These two different ways will show the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.*
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT *
  FROM CTE
WHERE RowNum = 1;

  Amazing thank you so much

  Sorry, just noticed your text above
  'In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?  Yes i am looking for the older date in CF as well so the result should be as per attachement.  The oldest date in either column

  

  In your insert you have:
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' is the oldest date for CF, not as in your attachment.

  I'm so so sorry I actually need the newest dates from both columns not oldest.

  Am i correct in thinking the below would do the trick?

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
  SELECT X.*
  FROM CTE
  CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
       FROM #lab l
       WHERE l.DEAL = CTE.DEAL
       ORDER BY CASE WHEN l.NextServ>l.NextServ THEN l.NextServ
               WHEN l.MotDate>l.MotDate THEN l.MotDate
               ELSE '' END) X;

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  January 14, 2019 at 4:59 am

  #2018815

  craig.jenkins - Monday, January 14, 2019 4:35 AM

  Jonathan AC Roberts - Monday, January 14, 2019 4:24 AM

  craig.jenkins - Monday, January 14, 2019 4:06 AM

  craig.jenkins - Monday, January 14, 2019 3:54 AM

  Jonathan AC Roberts - Monday, January 14, 2019 3:52 AM

  In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?
  These two different ways will show the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.*
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT *
  FROM CTE
WHERE RowNum = 1;

  Amazing thank you so much

  Sorry, just noticed your text above
  'In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?  Yes i am looking for the older date in CF as well so the result should be as per attachement.  The oldest date in either column

  

  In your insert you have:
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' is the oldest date for CF, not as in your attachment.

  I'm so so sorry I actually need the newest dates from both columns not oldest.

  Am i correct in thinking the below would do the trick?

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
  SELECT X.*
  FROM CTE
  CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
       FROM #lab l
       WHERE l.DEAL = CTE.DEAL
       ORDER BY CASE WHEN l.NextServ>l.NextServ THEN l.NextServ
               WHEN l.MotDate>l.MotDate THEN l.MotDate
               ELSE '' END) X;

  Actually just testing my code and i'm miles off i think

• Jonathan AC Roberts

  SSCoach

  Points: 18257

  More actions

  January 14, 2019 at 5:06 am

  #2018816

  craig.jenkins - Monday, January 14, 2019 4:59 AM

  craig.jenkins - Monday, January 14, 2019 4:35 AM

  Jonathan AC Roberts - Monday, January 14, 2019 4:24 AM

  craig.jenkins - Monday, January 14, 2019 4:06 AM

  craig.jenkins - Monday, January 14, 2019 3:54 AM

  Jonathan AC Roberts - Monday, January 14, 2019 3:52 AM

  In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?
  These two different ways will show the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.*
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT *
  FROM CTE
WHERE RowNum = 1;

  Amazing thank you so much

  Sorry, just noticed your text above
  'In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?  Yes i am looking for the older date in CF as well so the result should be as per attachement.  The oldest date in either column

  

  In your insert you have:
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' is the oldest date for CF, not as in your attachment.

  I'm so so sorry I actually need the newest dates from both columns not oldest.

  Am i correct in thinking the below would do the trick?

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
  SELECT X.*
  FROM CTE
  CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
       FROM #lab l
       WHERE l.DEAL = CTE.DEAL
       ORDER BY CASE WHEN l.NextServ>l.NextServ THEN l.NextServ
               WHEN l.MotDate>l.MotDate THEN l.MotDate
               ELSE '' END) X;

  Actually just testing my code and i'm miles off i think

  If you want the latest dates, these will do the job:

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.DEAL, X.REFNO, X.NextServ, X.MotDate
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l 
              WHERE l.DEAL = CTE.DEAL 
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) X;

  ;WITH CTE AS 
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) RowNum
     FROM #lab l
)
SELECT DEAL, REFNO, NextServ, MotDate
  FROM CTE
WHERE RowNum = 1;

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  January 14, 2019 at 5:45 am

  #2018821

  Jonathan AC Roberts - Monday, January 14, 2019 5:06 AM

  craig.jenkins - Monday, January 14, 2019 4:59 AM

  craig.jenkins - Monday, January 14, 2019 4:35 AM

  Jonathan AC Roberts - Monday, January 14, 2019 4:24 AM

  craig.jenkins - Monday, January 14, 2019 4:06 AM

  craig.jenkins - Monday, January 14, 2019 3:54 AM

  Jonathan AC Roberts - Monday, January 14, 2019 3:52 AM

  In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?
  These two different ways will show the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.*
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT *
  FROM CTE
WHERE RowNum = 1;

  Amazing thank you so much

  Sorry, just noticed your text above
  'In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?  Yes i am looking for the older date in CF as well so the result should be as per attachement.  The oldest date in either column

  

  In your insert you have:
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' is the oldest date for CF, not as in your attachment.

  I'm so so sorry I actually need the newest dates from both columns not oldest.

  Am i correct in thinking the below would do the trick?

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
  SELECT X.*
  FROM CTE
  CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
       FROM #lab l
       WHERE l.DEAL = CTE.DEAL
       ORDER BY CASE WHEN l.NextServ>l.NextServ THEN l.NextServ
               WHEN l.MotDate>l.MotDate THEN l.MotDate
               ELSE '' END) X;

  Actually just testing my code and i'm miles off i think

  If you want the latest dates, these will do the job:

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.DEAL, X.REFNO, X.NextServ, X.MotDate
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l 
              WHERE l.DEAL = CTE.DEAL 
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) X;

  ;WITH CTE AS 
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) RowNum
     FROM #lab l
)
SELECT DEAL, REFNO, NextServ, MotDate
  FROM CTE
WHERE RowNum = 1;

  Thanks for this but I think maybe i have confused you a little.  RefNo will sometimes have duplicates (but should not) unless it has a different deal code.  So Refno should always be unique unless it has a different Deal code but there will always be duplicate Deal codes. I am still looking to pull out the newest date in the NextServ and MotDate columns if there are duplicate RefNos.   I have added the following data and expected results below.  Sorry about this

  -- Sample data
  IF OBJECT_ID('tempdb..#lab') IS NOT NULL DROP TABLE #lab
  SELECT * INTO #lab FROM (VALUES
  ( 'CA', '101', '2019-01-14 00:00:00.000', '2018-06-30 00:00:00.000' ),
  ( 'BA', '101', '2019-01-14 00:00:00.000', '2018-06-30 00:00:00.000' ),
  ( 'NF', '200', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'NF', '200', '2019-01-14 00:00:00.000', '2019-10-08 00:00:00.000' ),
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'NF', '206', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'BA', '207', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'BA', '208', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'BA', '208', '2019-01-15 00:00:00.000', '2019-01-15 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  ) d

  ( DEAL, REFNO, NextServ, MotDate)

  

• Jonathan AC Roberts

  SSCoach

  Points: 18257

  More actions

  January 14, 2019 at 7:06 am

  #2018829

  craig.jenkins - Monday, January 14, 2019 5:45 AM

  Jonathan AC Roberts - Monday, January 14, 2019 5:06 AM

  craig.jenkins - Monday, January 14, 2019 4:59 AM

  craig.jenkins - Monday, January 14, 2019 4:35 AM

  Jonathan AC Roberts - Monday, January 14, 2019 4:24 AM

  craig.jenkins - Monday, January 14, 2019 4:06 AM

  craig.jenkins - Monday, January 14, 2019 3:54 AM

  Jonathan AC Roberts - Monday, January 14, 2019 3:52 AM

  In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?
  These two different ways will show the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.*
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT *
  FROM CTE
WHERE RowNum = 1;

  Amazing thank you so much

  Sorry, just noticed your text above
  'In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?  Yes i am looking for the older date in CF as well so the result should be as per attachement.  The oldest date in either column

  

  In your insert you have:
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' is the oldest date for CF, not as in your attachment.

  I'm so so sorry I actually need the newest dates from both columns not oldest.

  Am i correct in thinking the below would do the trick?

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
  SELECT X.*
  FROM CTE
  CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
       FROM #lab l
       WHERE l.DEAL = CTE.DEAL
       ORDER BY CASE WHEN l.NextServ>l.NextServ THEN l.NextServ
               WHEN l.MotDate>l.MotDate THEN l.MotDate
               ELSE '' END) X;

  Actually just testing my code and i'm miles off i think

  If you want the latest dates, these will do the job:

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.DEAL, X.REFNO, X.NextServ, X.MotDate
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l 
              WHERE l.DEAL = CTE.DEAL 
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) X;

  ;WITH CTE AS 
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) RowNum
     FROM #lab l
)
SELECT DEAL, REFNO, NextServ, MotDate
  FROM CTE
WHERE RowNum = 1;

  Thanks for this but I think maybe i have confused you a little.  RefNo will sometimes have duplicates (but should not) unless it has a different deal code.  So Refno should always be unique unless it has a different Deal code but there will always be duplicate Deal codes. I am still looking to pull out the newest date in the NextServ and MotDate columns if there are duplicate RefNos.   I have added the following data and expected results below.  Sorry about this

  -- Sample data
  IF OBJECT_ID('tempdb..#lab') IS NOT NULL DROP TABLE #lab
  SELECT * INTO #lab FROM (VALUES
  ( 'CA', '101', '2019-01-14 00:00:00.000', '2018-06-30 00:00:00.000' ),
  ( 'BA', '101', '2019-01-14 00:00:00.000', '2018-06-30 00:00:00.000' ),
  ( 'NF', '200', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'NF', '200', '2019-01-14 00:00:00.000', '2019-10-08 00:00:00.000' ),
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'NF', '206', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'BA', '207', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'BA', '208', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'BA', '208', '2019-01-15 00:00:00.000', '2019-01-15 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  ) d

  ( DEAL, REFNO, NextServ, MotDate)

  

  ;WITH CTE AS (SELECT DISTINCT DEAL, REFNO FROM #lab)
SELECT X.DEAL, X.REFNO, X.NextServ, X.MotDate
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l 
              WHERE l.DEAL = CTE.DEAL 
                AND l.REFNO = CTE.REFNO
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) X;

  ;WITH CTE AS 
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL, REFNO
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) RowNum
     FROM #lab l
)
SELECT DEAL, REFNO, NextServ, MotDate
  FROM CTE
WHERE RowNum = 1;

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  January 18, 2019 at 4:48 am

  #2019345

  Jonathan AC Roberts - Monday, January 14, 2019 7:06 AM

  craig.jenkins - Monday, January 14, 2019 5:45 AM

  Jonathan AC Roberts - Monday, January 14, 2019 5:06 AM

  craig.jenkins - Monday, January 14, 2019 4:59 AM

  craig.jenkins - Monday, January 14, 2019 4:35 AM

  Jonathan AC Roberts - Monday, January 14, 2019 4:24 AM

  craig.jenkins - Monday, January 14, 2019 4:06 AM

  craig.jenkins - Monday, January 14, 2019 3:54 AM

  Jonathan AC Roberts - Monday, January 14, 2019 3:52 AM

  In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?
  These two different ways will show the results:
  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.*
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l
              WHERE l.DEAL = CTE.DEAL
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) X;

  ;WITH CTE AS
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.NextServ ELSE l.MotDate END) RowNum
     FROM #lab l
)
SELECT *
  FROM CTE
WHERE RowNum = 1;

  Amazing thank you so much

  Sorry, just noticed your text above
  'In your jpg file it is showing a later date for CF, is this what you meant. I've assumed you didn't?  Yes i am looking for the older date in CF as well so the result should be as per attachement.  The oldest date in either column

  

  In your insert you have:
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' is the oldest date for CF, not as in your attachment.

  I'm so so sorry I actually need the newest dates from both columns not oldest.

  Am i correct in thinking the below would do the trick?

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
  SELECT X.*
  FROM CTE
  CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
       FROM #lab l
       WHERE l.DEAL = CTE.DEAL
       ORDER BY CASE WHEN l.NextServ>l.NextServ THEN l.NextServ
               WHEN l.MotDate>l.MotDate THEN l.MotDate
               ELSE '' END) X;

  Actually just testing my code and i'm miles off i think

  If you want the latest dates, these will do the job:

  ;WITH CTE AS (SELECT DISTINCT DEAL FROM #lab)
SELECT X.DEAL, X.REFNO, X.NextServ, X.MotDate
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l 
              WHERE l.DEAL = CTE.DEAL 
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) X;

  ;WITH CTE AS 
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) RowNum
     FROM #lab l
)
SELECT DEAL, REFNO, NextServ, MotDate
  FROM CTE
WHERE RowNum = 1;

  Thanks for this but I think maybe i have confused you a little.  RefNo will sometimes have duplicates (but should not) unless it has a different deal code.  So Refno should always be unique unless it has a different Deal code but there will always be duplicate Deal codes. I am still looking to pull out the newest date in the NextServ and MotDate columns if there are duplicate RefNos.   I have added the following data and expected results below.  Sorry about this

  -- Sample data
  IF OBJECT_ID('tempdb..#lab') IS NOT NULL DROP TABLE #lab
  SELECT * INTO #lab FROM (VALUES
  ( 'CA', '101', '2019-01-14 00:00:00.000', '2018-06-30 00:00:00.000' ),
  ( 'BA', '101', '2019-01-14 00:00:00.000', '2018-06-30 00:00:00.000' ),
  ( 'NF', '200', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'NF', '200', '2019-01-14 00:00:00.000', '2019-10-08 00:00:00.000' ),
  ( 'CF', '201', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'NF', '206', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'BA', '207', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'BA', '208', '2019-01-14 00:00:00.000', '2019-01-14 00:00:00.000' ),
  ( 'BA', '208', '2019-01-15 00:00:00.000', '2019-01-15 00:00:00.000' ),
  ( 'CF', '201', '2019-01-16 00:00:00.000', '2019-10-21 00:00:00.000' )
  ) d

  ( DEAL, REFNO, NextServ, MotDate)

  

  ;WITH CTE AS (SELECT DISTINCT DEAL, REFNO FROM #lab)
SELECT X.DEAL, X.REFNO, X.NextServ, X.MotDate
  FROM CTE
 CROSS APPLY(SELECT TOP(1) DEAL, REFNO, NextServ, MotDate
               FROM #lab l 
              WHERE l.DEAL = CTE.DEAL 
                AND l.REFNO = CTE.REFNO
              ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) X;

  ;WITH CTE AS 
(
    SELECT DEAL, REFNO, NextServ, MotDate,
           ROW_NUMBER() OVER (PARTITION BY DEAL, REFNO
                                  ORDER BY CASE WHEN l.NextServ<l.MotDate THEN l.MotDate ELSE l.NextServ END DESC) RowNum
     FROM #lab l
)
SELECT DEAL, REFNO, NextServ, MotDate
  FROM CTE
WHERE RowNum = 1;

  This is awesome thanks so much