Solved

Finding Most Recent of 3 Dates in SQL Query

Posted on 2016-08-08
4
24 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 48
Trouble with <> 2 26
Parse this column 6 34
partitioning database after decade growth 8 54
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

679 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