Solved

SQL If NUll on Unpivot

Posted on 2014-12-19
1
80 Views
Last Modified: 2015-01-15
So wondering if this sql code can have a statemet put in it that will do the following.

It runs the query as printed below but it checks to see if a specific feild is null.

So for instance this query currently does not have the field [appraisefinal]. I want it to while running this query do some if statements and say if appraisefinal is null then insert [appraisal ordered] into the item of outlookreport.dbo.calendar.

;WITH MyCTE AS
(
    SELECT    * 
    FROM      (
                  SELECT   *
                  FROM      emdb.dbo.calendarview 
              )p
    UNPIVOT 
    ( 
        EventDate FOR DateDescription in ([Appraisal Ordered],[Closing Date],[Application Signed],[Appraisal Recieved] ,[Approval To Close],[Insurance Ordered],[Title Ordered])
    ) as unpvt
)
insert into outlookreport.dbo.calendar (eventdate,item,xrefid)
SELECT    
          M.EventDate,
          M.DateDescription,
          T.xrefid
FROM      emdb.dbo.calendarview T 
          JOIN MyCTE M
              ON T.xrefid = M.xrefid
WHERE NOT EXISTS(SELECT 1 FROM outlookreport.dbo.calendar WHERE eventdate = M.EventDate AND xrefid = T.xrefid);

Open in new window

0
Comment
Question by:desiredforsome
1 Comment
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40510753
hmm..

 
WITH Data AS 
	(
		SELECT	[Appraisal Ordered],
				[Closing Date],
				[Application Signed],
				[Appraisal Recieved] ,
				[Approval To Close],
				[Insurance Ordered],
				[Title Ordered],
				COALESCE(appraisefinal, [Appraisal Ordered]) AS appraisefinal
		FROM	emdb.dbo.calendarview 
	)
SELECT	* 
FROM	Data
UNPIVOT ( EventDate FOR DateDescription IN ( appraisefinal, [Appraisal Ordered], [Closing Date], [Application Signed], [Appraisal Recieved], [Approval To Close], [Insurance Ordered], [Title Ordered] ) ) U;

Open in new window

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 44
export sql results to csv 6 36
SQL Server Error 21 8 25
SQL invalid column name 5 13
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

777 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