October 25, 2016 at 3:05 pm
I have a table, in each row, I have an CaseID, MTDThresholdDate, and YTDThresholdDate
Both of the date could be null, I would like to return what is the earlier date or smaller date.
If either of them is null, return the date that is not null.
If both null then null, if there is one null, then return non-null date.
Can anyone help, thanks
October 26, 2016 at 12:43 am
This should do what you are looking for
DECLARE @datetest TABLE (CaseID INT, MTDThresholdDate date, YTDThresholdDate date);
INSERT INTO @datetest (CaseID, MTDThresholdDate, YTDThresholdDate)
VALUES (1, NULL, NULL)
, (2, NULL, '2016-05-15')
, (3, '2016-06-15', NULL)
, (4, '2016-05-05', '2016-06-15')
, (5, '2016-06-05', '2016-05-15')
DECLARE @MaxDate DATE = '2999-12-31';
SELECT MT.CaseID, MinDate = NULLIF(MA.MinDate, @MaxDate), MinDateCol = MA.ColName
FROM @datetest AS MT
CROSS APPLY (
SELECT TOP(1) VA.ColName, VA.DateCol
FROM (VALUES('MTDThresholdDate', ISNULL(MT.MTDThresholdDate, @MaxDate)),('YTDThresholdDate', ISNULL(MT.YTDThresholdDate, @MaxDate))) AS VA(ColName, DateCol)
ORDER BY VA.DateCol
) AS MA(ColName, MinDate);
EDIT: Added code tags
October 26, 2016 at 7:50 am
Here's a bit simpler version of DesNorton's query.
SELECT MT.CaseID, MA.MinDate, MinDateCol = MA.ColName
FROM @datetest AS MT
OUTER APPLY (
SELECT TOP(1) VA.ColName, VA.DateCol
FROM (VALUES('MTDThresholdDate', MT.MTDThresholdDate),
('YTDThresholdDate', MT.YTDThresholdDate)) AS VA(ColName, DateCol)
WHERE DateCol IS NOT NULL
ORDER BY VA.DateCol
) AS MA(ColName, MinDate);
October 26, 2016 at 8:08 am
This is my version since there was no explicit request to know which column the date was returned from.
SELECT MT.CaseID, MA.MinDate
FROM @datetest AS MT
OUTER APPLY (
SELECT MIN(VA.DateCol)
FROM (VALUES(MT.MTDThresholdDate),
(MT.YTDThresholdDate)) AS VA(DateCol)
WHERE DateCol IS NOT NULL
) AS MA(MinDate);
Also attached the execution plan.
October 26, 2016 at 9:21 am
Here's another method:
SELECT CaseID,
min_date=COALESCE(CASE WHEN MTDThresholdDate<YTDThresholdDate
THEN MTDThresholdDate
WHEN YTDThresholdDate<MTDThresholdDate
THEN YTDThresholdDate
END,
MTDThresholdDate,
YTDThresholdDate
)
FROM @datetest;
The method with APPLY is more flexible if you end up needing to compare more than two dates or need to return a description of the column the value came from, but this method's lighter on CPU (on a million rows, about 1100 ms vs 600 ms on my machine).
Cheers!
October 26, 2016 at 3:10 pm
Another option...
You can use the values clause to get min and max values across a row but YMMV. I've had case statements outperform it at times and other times it performed better or the same as other approaches:
SELECT CaseID,
(SELECT MIN(x)
FROM (VALUES (MTDThresholdDate),(YTDThresholdDate)) AS value(x)) as MinDate
from YourTable
Sue
October 26, 2016 at 3:14 pm
Sue_H (10/26/2016)
Another option...You can use the values clause to get min and max values across a row but YMMV. I've had case statements outperform it at times and other times it performed better or the same as other approaches:
SELECT CaseID,(SELECT MIN(x)
FROM (VALUES (MTDThresholdDate),(YTDThresholdDate)) AS value(x)) as MinDate
from YourTable
Sue
Yes, this is what I suggested above.
October 26, 2016 at 4:54 pm
You guys are all great, let me try each one to see which one works better for my case! Thanks much
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply