Solved

Finding Most Recent of 3 Dates in SQL Query

Posted on 2016-08-08
4
25 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 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