Chris Harshman (10/31/2014)
I think your ANDs and ORs may be getting mixed up somewhere, the logic in each WHEN looks confusing, maybe parenthesis could help? Also, you say there are 3 sources and 4 dates, but I don't see what will happen in the CASEs when date4 is the most recent?If all you are doing is trying to get the source of the most recent date of the 4 dates, maybe something like:
CREATE FUNCTION dbo.MostRecent(@AppSubmitDate DATETIME2(7), @ApprovedDate DATETIME2(7), @ExpireDate DATETIME2(7), @ReturnedDate DATETIME2(7))RETURNS TABLE AS
RETURN (
SELECT TOP 1 source
FROM (VALUES ('Submit Date', @AppSubmitDate), ('Approved Date', @ApprovedDate), ('Expire Date', @ExpireDate), ('Returned Date', @ReturnedDate)) AS value(source, val)
ORDER BY val DESC
)
GO
** edit
OK, I just saw Jack Corbet's post, maybe I'm way underestimating what the OP is trying to accomplish :unsure:
Chris,
Your post is almost identical to what I originally was going to post, but then I re-read the requirements and a simple max date isn't the desired outcome. There is one instance where the max date is not correct and that is when the ApprovedDate > ExpireDate and ExpireDate is not null, then the OP wants to show Approved, but Expired or something along those lines.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question