Case Statement Returning False when Two Columns are Blank when should be True

Transact-SQL
1

Afternoon Team,

Hope your well and can help me with a little problem i am encountering.

The query below compares the closed data in legacy vs closed date in current. What i have noticed is if the closed date is blank in both legacy and current then the ClosedMatch = False.

The ideal outcome would be ClosedMatch = True ( as both Columns are the same).
What else do i need to add to my statement below to get the correct outcome ?

Select
LEGACY.DATE_CLOSED__C as LClosedDate,
CURRENT.DATE_CLOSED__C as ClosedDate,
Case when CURRENT.DATE_CLOSED__C = LEGACY.DATE_CLOSED__C Then 'True' ELSE 'False' END AS ClosedMatch
From LEGACY
Inner join CURRENT
On LEGACY.ACCOUNT_CODE__C=CURRENT.Account_Code__c

Looking forward to your help.

2

What you see as blanks may be NULLs. Try the following:

SELECT
	LEGACY.DATE_CLOSED__C AS LClosedDate,
	CURRENT.DATE_CLOSED__C AS ClosedDate,
	CASE 
		WHEN COALESCE(CURRENT.DATE_CLOSED__C,'19000101') = COALESCE(LEGACY.DATE_CLOSED__C,'19000101') THEN 'True' 
		ELSE 'False' 
	END AS ClosedMatch
FROM 
	LEGACY
	INNER JOIN [CURRENT]
		ON LEGACY.ACCOUNT_CODE__C=CURRENT.Account_Code__c

You might have to tweak the query based on whether you have 1/1/1900 in any of the actual data.

1 Like
3

@JamesK -
i looked at the schema and also noticed the following.

table : LEGACY
Field : DATE_CLOSED__C
Data Type : TimeStamp(6)

table : Current
Field : DATE_CLOSED__C
Data Type : Date

4

hi masond

Hope this helps :slight_smile: :slight_smile:

both the columns must be of the same datatype
when comparing ... to give correct results

to do this
you will have to change the datatype of any one column to the other column

in tsql to do this you have

  1. CAST
  2. CONVERT

example :
legacy.date_closed_c = cast ( current.date_closed_c as timestamp(6) )

https://www.w3schools.com/sql/func_sqlserver_cast.asp

https://www.w3schools.com/sql/func_sqlserver_convert.asp

5

@harishgg1 i updated all the tables to a consist data type. which hopefully makes my life a little similar going forward. Thank you all for your inputs :slight_smile: