Finding Most Recent of 3 Dates in SQL Query

I have a query where I need to return the most recent date when comparing 3 fields (garn.BGARNDT, garn.WGARNDT, tax.TGARNDT).

I do not want to display 'NULL' as a response if all are blank
There is a chance were dates between fields may be the same

Code is attached...right now my query outputs all 3 fields
QuerySyntax.txt
sparker1970Asked:
Who is Participating?
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
Let me try your situation: BGARNDT is null, WGARNDT = 01/07/2016, and TGARNDT = 11/24/2015.

Yes, the null is causing that wrong result.

declare @data as table (BGARNDT datetime, WGARNDT datetime, TGARNDT datetime)

insert into @data values (null, '20160107', '20151124')

Select     ISNULL( convert(varchar, Case 
            When Coalesce(BGARNDT,'19000101') >= Coalesce(WGARNDT,'19000101') 
			 And Coalesce(BGARNDT,'19000101') >= Coalesce(TGARNDT,'19000101') Then BGARNDT
            When Coalesce(WGARNDT,'19000101') >= Coalesce(BGARNDT,'19000101') 
			 And Coalesce(WGARNDT,'19000101') >= Coalesce(TGARNDT,'19000101') Then WGARNDT 
            Else TGARNDT
            End,101), '') As MaxDate from @data

Open in new window


Choose whatever ultimo date far off before any real date. In the end result after converting to varchar, you can translate 01/01/1900 00:00:00 am (which results if all fields are null) to the blank string.

Bye, Olaf.
0
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
It is sufficient to know this detail of your query:
	ISNULL(convert(varchar,garn.BGARNDT,101),'') as 'LAST BANK GARN DATE',
	ISNULL(convert(varchar,garn.WGARNDT,101),'') as 'LAST WAGE GARN DATE',
	ISNULL(convert(varchar,tax.TGARNDT,101),'') as 'LAST TAX GARN DATE',

Open in new window

This has the one essential information, it means garn.BGARNDT, garn.WGARNDT, tax.TGARNDT are date types. Most probably datetimes.

It's a bad idea to format datetimes to strings, this formatting and output is job of the frontend or report, not of a database, but I think I'll state that to the end of my life and nobody will listen.

Well, the < operator applies to these columns, so you can find the smallest of three columns by two > or >=  comparisons. MAX() does not apply, as it only applies to groups of data, not to three columns. If there would be more columns UNPIVOT might be a choice, but just for three columns, you can go straight forward with a CASE:

       ...,
    ISNULL( convert(varchar,Case When garn.BGARNDT > garn.WGARND And garn.BGARNDT > tax.TGARNDT Then garn.BGARNDT
            When garn.WGARND > garn.BGARNDT And garn.WGARND > tax.TGARNDT Then garn.WGARND 
            Else tax.TGARNDT
            End,101), '') As MinDate

Open in new window


And if you'd like to know the source of that MinDate:
       ...,
    Case When garn.BGARNDT > garn.WGARND And garn.BGARNDT > tax.TGARNDT Then 'garn.BGARNDT'
            When garn.WGARND > garn.BGARNDT And garn.WGARND > tax.TGARNDT Then 'garn.WGARND' 
            Else 'tax.TGARNDT'
            End As MinDateSource

Open in new window


Bye, Olaf.
0
 
sparker1970Author Commented:
Thanks for the quick response.

A couple of issues:

1    I need the most recent date...not the oldest.

2    The formula fails when either BGARNDT or WGARNDT is null. If BGARNDT is null, WGARNDT = 01/07/2016, and TGARNDT = 11/24/2015, your query syntax is returning 11/24/2015. I think this is because of the null value in BGARNDT.

Here is code I wrote to get around the issue of nulls. I was hoping there was a more simple way to write this but your use of CASE tells me there may not be:

'LAST GARN DATE' = CASE
            when garn.BGARNDT IS null and garn.WGARNDT IS null and tax.TGARNDT IS null then ''
            when garn.BGARNDT IS null and garn.WGARNDT IS null and tax.TGARNDT IS not null then CONVERT(VARCHAR,tax.TGARNDT,101)
            when garn.BGARNDT IS null and garn.WGARNDT IS not null and tax.TGARNDT IS null then convert(varchar,garn.WGARNDT,101)
            when garn.BGARNDT IS not null and garn.WGARNDT IS null and tax.TGARNDT IS null then convert(varchar,garn.BGARNDT,101)
            when garn.BGARNDT IS null and garn.WGARNDT IS NOT null and tax.TGARNDT IS not null AND tax.TGARNDT > garn.WGARNDT THEN CONVERT(VARCHAR,tax.TGARNDT,101)
            when garn.BGARNDT IS NOT null and garn.WGARNDT IS null and tax.TGARNDT IS not null AND tax.TGARNDT > garn.BGARNDT THEN CONVERT(VARCHAR,tax.TGARNDT,101)
            when garn.BGARNDT IS null and garn.WGARNDT IS NOT null and tax.TGARNDT IS not null AND tax.TGARNDT < garn.WGARNDT THEN convert(varchar,garn.WGARNDT,101)
            when garn.BGARNDT IS NOT null and garn.WGARNDT IS null and tax.TGARNDT IS not null AND tax.TGARNDT < garn.BGARNDT THEN convert(varchar,garn.BGARNDT,101)
            when garn.BGARNDT IS null and garn.WGARNDT IS NOT null and tax.TGARNDT IS not null AND tax.TGARNDT > garn.WGARNDT THEN CONVERT(VARCHAR,tax.TGARNDT,101)
            when garn.BGARNDT IS NOT null and garn.WGARNDT IS null and tax.TGARNDT IS not null AND tax.TGARNDT > garn.BGARNDT THEN CONVERT(VARCHAR,tax.TGARNDT,101)
            when garn.BGARNDT IS null and garn.WGARNDT IS NOT null and tax.TGARNDT IS not null AND tax.TGARNDT < garn.WGARNDT THEN convert(varchar,garn.WGARNDT,101)
            when garn.BGARNDT IS NOT null and garn.WGARNDT IS null and tax.TGARNDT IS not null AND tax.TGARNDT < garn.BGARNDT THEN convert(varchar,garn.BGARNDT,101)
            when garn.BGARNDT IS null and garn.WGARNDT IS not null and tax.TGARNDT IS null then convert(varchar,garn.WGARNDT,101)
            when garn.BGARNDT IS not null and garn.WGARNDT IS null and tax.TGARNDT IS null then convert(varchar,garn.BGARNDT,101)
            WHEN garn.BGARNDT > garn.WGARNDT and garn.BGARNDT > tax.TGARNDT then convert(varchar,garn.BGARNDT,101)
            WHEN garn.WGARNDT > garn.BGARNDT and garn.WGARNDT > tax.TGARNDT then convert(varchar,garn.WGARNDT,101)
            when tax.TGARNDT > garn.BGARNDT and tax.TGARNDT > garn.WGARNDT then CONVERT(VARCHAR,tax.TGARNDT,101)
            end,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.