Post reply

Select oldest dates from 2 columns

  • 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

  • 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

  • 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;

  • January 14, 2019 at 3:52 am

    #2018809

    .

  • 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

  • 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

  • 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.

  • 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;

  • 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

  • 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;

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

  • 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;

  • 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

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy