Solved

sql 2008

Posted on 2014-03-14
5
187 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

773 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