asked on

sql 2008

I have a query where I have 3 fields Address1, Address2 and City which I am checking to see if they are null. If the fields are not null show them in the demographics if the fields are null then null.
Address1 = 1st Ave.
Address2 = null
City = Seattle
Original Address1= : New Address1 = 1st Ave
Original City = : New City = Seattle.

Select
CASE WHEN  Address1 IS NOT NULL AND Address1 <> '' THEN Address1 + char(13) + char(10) ELSE  
	CASE WHEN Address1 IS NULL THEN '' END END +
CASE WHEN Address2 IS NOT NULL AND Address2 <> '' THEN  Address2 + char(13) + char(10) ELSE
	CASE WHEN Address1 IS NULL THEN Address2 + char(13) + char(10) ELSE
		CASE WHEN Address2 IS NULL THEN '' END END END +
CASE WHEN City IS NOT NULL AND City <> '' THEN City + char(13) + char(10) ELSE
	CASE WHEN Address1 IS NULL AND Address2 IS NULL THEN City + char(13) + char(10) ELSE
		CASE WHEN City is NULL THEN '' END END END AS Demographics,
NumberID,
DateAdded,
TimeAdded
From
(
Select 
----Checking to see if NewAddress1 has been updated
CASE WHEN OrigAddress1 IS NULL and NewAddress1 IS NOT NULL AND LTRIM(RTRIM(NewAddress1)) <> '' THEN 'Original Address1 = : New Address1 = ' + NewAddress1
     WHEN LTRIM(RTRIM(OrigAddress1)) = '' and NewAddress1 IS NOT NULL AND LTRIM(RTRIM(NewAddress1)) <> '' THEN 'Original Address1 = : New Address1 = ' + NewAddress1
     WHEN LTRIM(RTRIM(OrigAddress1)) IS NOT NULL AND  NewAddress1 IS NOT NULL AND LTRIM(RTRIM(NewAddress1)) <> LTRIM(RTRIM(OrigAddress1)) THEN 'Original Address1 = ' + LTRIM(RTRIM(OrigAddress1)) + ' : New Address1 = ' + NewAddress1
     WHEN OrigAddress1 IS NOT NULL and LTRIM(RTRIM(OrigAddress1)) <> '' and NewAddress1 IS NULL AND LTRIM(RTRIM(NewAddress1)) = LTRIM(RTRIM(OrigAddress1)) THEN NULL --'Original Address1 = ' + OrigAddress1 + ': New Address1 = '
END AS Address1,
----Checking to see if NewAddress2 has been updated
CASE WHEN OrigAddress2 IS NULL and NewAddress2 IS NOT NULL AND LTRIM(RTRIM(NewAddress2)) <> '' THEN 'Original Address2 = : New Address2 = ' + NewAddress2
     WHEN LTRIM(RTRIM(OrigAddress2)) = '' and NewAddress2 IS NOT NULL AND LTRIM(RTRIM(NewAddress2)) <> LTRIM(RTRIM(OrigAddress2)) THEN 'Original Address2 = : New Address2 = ' + NewAddress2
     WHEN LTRIM(RTRIM(OrigAddress2)) IS NOT NULL AND  NewAddress2 IS NOT NULL AND LTRIM(RTRIM(NewAddress2)) <> LTRIM(RTRIM(OrigAddress2)) THEN 'Original Address2 = ' + LTRIM(RTRIM(OrigAddress2)) + ' : New Address2 = ' + NewAddress2
     WHEN OrigAddress2 IS NOT NULL and LTRIM(RTRIM(OrigAddress2)) <> '' and NewAddress2 IS NULL AND LTRIM(RTRIM(NewAddress2)) = LTRIM(RTRIM(OrigAddress2)) THEN NULL--'Original Address2 = ' + OrigAddress2 + ': New Address2 = '
END AS Address2,
----Checking to see if NewCity has been updated
CASE WHEN OrigCity IS NULL and NewCity IS NOT NULL AND LTRIM(RTRIM(NewCity)) <> '' THEN 'Original City = : New City = ' + NewCity
     WHEN LTRIM(RTRIM(OrigCity)) = '' and NewCity IS NOT NULL AND LTRIM(RTRIM(NewCity)) <> '' THEN 'Original City = : New City = ' + NewCity
     WHEN LTRIM(RTRIM(OrigCity)) IS NOT NULL AND  NewCity IS NOT NULL AND LTRIM(RTRIM(NewCity)) <> LTRIM(RTRIM(OrigCity)) THEN 'Original City = ' + LTRIM(RTRIM(OrigCity)) + ' : New City = ' + NewCity
     WHEN OrigCity IS NOT NULL and LTRIM(RTRIM(OrigCity)) <> '' and NewCity IS NULL AND LTRIM(RTRIM(NewCity)) = LTRIM(RTRIM(OrigCity)) THEN NULL --'Original City = ' + OrigCity + ': New City = '
END AS City,
NumberID,
Dateadded,
TimeAdded
From 
DUH
WHERE
NumberID = @NumberID
)as Nam
Where
NumberID = @NumberID

Open in new window