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

x
?
Solved

sql 2008

Posted on 2014-03-14
5
Medium Priority
?
198 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
[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
  • 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 41

Assisted Solution

by:Sharath
Sharath earned 750 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 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