Solved

sql 2008

Posted on 2014-03-14
5
184 Views
Last Modified: 2014-03-22
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

0
Comment
Question by:pelon_80
  • 3
5 Comments
 
LVL 13

Expert Comment

by:magarity
ID: 39929622
Did you notice your posting does not have a question in it?  What exactly do you need help with?
0
 

Accepted Solution

by:
pelon_80 earned 0 total points
ID: 39929668
Sorry I forgot the first part as well. Wich is
Select
Demographics,
NumberID,
DateAdded,
TimeAdded
From
Commentary
Where
NumberID = NumberID

Union All
I want to be able to show only the Changes.

so if
Address1 = null
Address2 = null
city =Seattle

Then
NumberID  Demographics     DateAdded      TimeAdded
2356            Seattle                   03/14/14         12:00:00
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 39930226
You can simplify the CASE logic like this. But test it for all your data.
SELECT COALESCE(Address1 + CHAR(13) + CHAR(10), '') +
       COALESCE(Address2 + CHAR(13) + CHAR(10), '') +
       COALESCE(City + CHAR(13) + CHAR(10), '') AS Demographics,
       NumberID,
	   DateAdded,
	   TimeAdded	   
  FROM (SELECT CASE 
                 WHEN OrigAddress1 <> NewAddress1 THEN 'Original Address1 = ' + OrigAddress1 + ' : New Address1 = ' + NewAddress1
               END AS Address1,
               CASE 
                 WHEN OrigAddress2 <> NewAddress2 THEN 'Original Address2 = ' + OrigAddress2 + ' : New Address2 = ' + NewAddress2 
               END AS Address2,
               CASE 
                 WHEN OrigCity <> NewCity THEN 'Original City = ' + OrigCity + ' : New City = ' + NewCity 
               END AS City			   
          FROM (SELECT RTRIM(LTRIM(COALESCE(OrigAddress1, ''))) OrigAddress1, 
                       RTRIM(LTRIM(COALESCE(NewAddress1, '')))  NewAddress1,
					   RTRIM(LTRIM(COALESCE(OrigAddress2, ''))) OrigAddress2, 
                       RTRIM(LTRIM(COALESCE(NewAddress2, '')))  NewAddress2,
					   RTRIM(LTRIM(COALESCE(OrigCity, ''))) OrigCity, 
                       RTRIM(LTRIM(COALESCE(NewCity, '')))  NewCity,
					   NumberID,
					   DateAdded,
				       TimeAdded
                  FROM DUH 
                 WHERE NumberID = @NumberID) T1) T2 

Open in new window

0
 

Author Comment

by:pelon_80
ID: 39934959
I also want to put null in the demographics if there is an empty space ('').
0
 

Author Closing Comment

by:pelon_80
ID: 39947240
Close to answer
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now