Solved

Finding Most Recent of 3 Dates in SQL Query

Posted on 2016-08-08
4
23 Views
Last Modified: 2016-10-09
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
0
Comment
Question by:sparker1970
  • 2
4 Comments
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 500 total points
ID: 41747694
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
 

Author Comment

by:sparker1970
ID: 41748751
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
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 41748785
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question