January 14, 2019 at 3:23 am
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
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
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
.
January 14, 2019 at 3:54 am
Jonathan AC Roberts - Monday, January 14, 2019 3:52 AMIn 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
craig.jenkins - Monday, January 14, 2019 3:54 AMJonathan AC Roberts - Monday, January 14, 2019 3:52 AMIn 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
craig.jenkins - Monday, January 14, 2019 4:06 AMcraig.jenkins - Monday, January 14, 2019 3:54 AMJonathan AC Roberts - Monday, January 14, 2019 3:52 AMIn 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
Jonathan AC Roberts - Monday, January 14, 2019 4:24 AMcraig.jenkins - Monday, January 14, 2019 4:06 AMcraig.jenkins - Monday, January 14, 2019 3:54 AMJonathan AC Roberts - Monday, January 14, 2019 3:52 AMIn 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 columnIn 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
craig.jenkins - Monday, January 14, 2019 4:35 AMJonathan AC Roberts - Monday, January 14, 2019 4:24 AMcraig.jenkins - Monday, January 14, 2019 4:06 AMcraig.jenkins - Monday, January 14, 2019 3:54 AMJonathan AC Roberts - Monday, January 14, 2019 3:52 AMIn 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 columnIn 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
craig.jenkins - Monday, January 14, 2019 4:59 AMcraig.jenkins - Monday, January 14, 2019 4:35 AMJonathan AC Roberts - Monday, January 14, 2019 4:24 AMcraig.jenkins - Monday, January 14, 2019 4:06 AMcraig.jenkins - Monday, January 14, 2019 3:54 AMJonathan AC Roberts - Monday, January 14, 2019 3:52 AMIn 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 columnIn 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
Jonathan AC Roberts - Monday, January 14, 2019 5:06 AMcraig.jenkins - Monday, January 14, 2019 4:59 AMcraig.jenkins - Monday, January 14, 2019 4:35 AMJonathan AC Roberts - Monday, January 14, 2019 4:24 AMcraig.jenkins - Monday, January 14, 2019 4:06 AMcraig.jenkins - Monday, January 14, 2019 3:54 AMJonathan AC Roberts - Monday, January 14, 2019 3:52 AMIn 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 columnIn 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
craig.jenkins - Monday, January 14, 2019 5:45 AMJonathan AC Roberts - Monday, January 14, 2019 5:06 AMcraig.jenkins - Monday, January 14, 2019 4:59 AMcraig.jenkins - Monday, January 14, 2019 4:35 AMJonathan AC Roberts - Monday, January 14, 2019 4:24 AMcraig.jenkins - Monday, January 14, 2019 4:06 AMcraig.jenkins - Monday, January 14, 2019 3:54 AMJonathan AC Roberts - Monday, January 14, 2019 3:52 AMIn 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 columnIn 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
Jonathan AC Roberts - Monday, January 14, 2019 7:06 AMcraig.jenkins - Monday, January 14, 2019 5:45 AMJonathan AC Roberts - Monday, January 14, 2019 5:06 AMcraig.jenkins - Monday, January 14, 2019 4:59 AMcraig.jenkins - Monday, January 14, 2019 4:35 AMJonathan AC Roberts - Monday, January 14, 2019 4:24 AMcraig.jenkins - Monday, January 14, 2019 4:06 AMcraig.jenkins - Monday, January 14, 2019 3:54 AMJonathan AC Roberts - Monday, January 14, 2019 3:52 AMIn 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 columnIn 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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply