Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Finding Most Recent of 3 Dates in SQL Query

Posted on 2016-08-08
4
Medium Priority
?
29 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 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 2000 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 30

Accepted Solution

by:
Olaf Doschke earned 2000 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

Industry Leaders: 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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

688 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