Solved

Make CTE statement a case statement

Posted on 2010-09-23
6
435 Views
Last Modified: 2012-06-21
I have a script that  wIant to make into a case statement so it will populate a case statment field rather than a separate table.  How can I do this?
;with CTE as (

select [MATNR],PlannedDateColumn,Planned_Date 

  from (select * from PLC_6_Months_File) p

       unpivot (Planned_Date for PlannedDateColumn in (DO_Planned,

                                                       CX_Planned,

                                                       NE_Planned,

                                                       PA_Planned,

                                                       GA_Planned,

                                                       SA_Planned,

                                                       PE_Planned,

                                                       ES_Planned,

                                                       EM_Planned,

                                                       ED_Planned)) as unpvt),

 CTE2 as (

 select *, ROW_NUMBER() over (partition by [MATNR] order by Planned_Date desc) rn

   from CTE

  where Planned_Date < GETDATE())

 

 select [MATNR],Planned_Date,DateCode,Status INTO PLC_GLOBAL_STATUS_FILE_Planned_6

   from CTE2 c

   join PLC_Status_Values t on substring(c.PlannedDateColumn,1,2) = t.DateCode

  where rn = 1

  

  

  

  SELECT     [MATNR], CASE WHEN ED_Actual IS NOT NULL THEN 'ED'

WHEN ES_Actual IS NOT NULL THEN 'ES'

WHEN EM_Actual IS NOT NULL THEN 'EM' 

WHEN PE_Actual IS NOT NULL THEN 'PE'

WHEN SA_Actual IS NOT NULL THEN 'SA'

WHEN GA_Actual IS NOT NULL THEN 'GA'

WHEN PA_Actual IS NOT NULL THEN 'PA'

WHEN NE_Actual IS NOT NULL THEN 'NE'

WHEN CX_Actual IS NOT NULL THEN 'CX'

WHEN DO_Actual IS NOT NULL THEN 'DO'

WHEN VR_Actual IS NOT NULL THEN 'VR'

ELSE '' END AS DateCode

INTO #tempTablePLC_GLOBAL_INFO_Actual_6

FROM         dbo.PLC_6_Months_File







SELECT     dbo.#tempTablePLC_GLOBAL_INFO_Actual_6.[MATNR], dbo.#tempTablePLC_GLOBAL_INFO_Actual_6.DateCode, 

                      dbo.PLC_Status_Values.Status



INTO PLC_GLOBAL_STATUS_FILE_Actual_6

FROM         dbo.PLC_Status_Values RIGHT OUTER JOIN

                      dbo.#tempTablePLC_GLOBAL_INFO_Actual_6 ON 

                      dbo.PLC_Status_Values.DateCode = dbo.#tempTablePLC_GLOBAL_INFO_Actual_6.DateCode

Open in new window

0
Comment
Question by:Fairfield
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33752107
can you please clarify why you want to change from CTE to CASE ?

should the data output change, somehow?
please clarify
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 33752156
can you explain what it is you want to do in some more detail?

which column from which table do you want to query/use?

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33752374
Well, looking at it briefly, I reckon it is safest to say "no"

first there are three different queries.

the 1st one is the CTE and creates PLC_GLOBAL_STATUS_FILE_Planned_6
the 2nd one creates #tempTablePLC_GLOBAL_INFO_Actual_6 (and might have been able to incorporate that query directly into the third)
the 3rd (and last) creates PLC_GLOBAL_STATUS_FILE_Actual_6

Cannot see where PLC_GLOBAL_STATUS_FILE_Planned_6 (ie results of CTE) comes into play in the other queries, let alone which column is required for the case.

Very involved piece of T-SQL code. Looks like there might be a couple of ways to achieve those queries... So need your help to turn that "no" into "yay"...
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33752993
Do you mean this?
;with CTE as (

select [MATNR],PlannedDateColumn,Planned_Date 

  from (select * from PLC_6_Months_File) p

       unpivot (Planned_Date for PlannedDateColumn in (DO_Planned,

                                                       CX_Planned,

                                                       NE_Planned,

                                                       PA_Planned,

                                                       GA_Planned,

                                                       SA_Planned,

                                                       PE_Planned,

                                                       ES_Planned,

                                                       EM_Planned,

                                                       ED_Planned)) as unpvt),

 CTE2 as (

 select *, ROW_NUMBER() over (partition by [MATNR] order by Planned_Date desc) rn

   from CTE

  where Planned_Date < GETDATE())



-- cte2 is LATEST planned status by < getdate()

 select coalesce(c.[MATNR],actual.[MATNR]) [MATNR],

	c.Planned_Date,

	t.DateCode PlannedCode,

	t.Status PlannedStatus,

	ta.DateCode ActualCode, ta.Status ActualStatus

   from CTE2 c

   full outer join

	(

	SELECT     [MATNR], CASE WHEN ED_Actual IS NOT NULL THEN 'ED'

	WHEN ES_Actual IS NOT NULL THEN 'ES'

	WHEN EM_Actual IS NOT NULL THEN 'EM' 

	WHEN PE_Actual IS NOT NULL THEN 'PE'

	WHEN SA_Actual IS NOT NULL THEN 'SA'

	WHEN GA_Actual IS NOT NULL THEN 'GA'

	WHEN PA_Actual IS NOT NULL THEN 'PA'

	WHEN NE_Actual IS NOT NULL THEN 'NE'

	WHEN CX_Actual IS NOT NULL THEN 'CX'

	WHEN DO_Actual IS NOT NULL THEN 'DO'

	WHEN VR_Actual IS NOT NULL THEN 'VR'

	ELSE '' END AS DateCode

	FROM         dbo.PLC_6_Months_File

	) actual on actual.[MATNR] = c.[MATNR]

   left join PLC_Status_Values t on substring(c.PlannedDateColumn,1,2) = t.DateCode

   left join PLC_Status_Values ta on actual.DateCode = ta.DateCode

  where rn = 1

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33753005
Correction, for the full outer join to be useful, the rn=1 filter should be brought into the planned part.
;with CTE as (

select [MATNR],PlannedDateColumn,Planned_Date 

  from (select * from PLC_6_Months_File) p

       unpivot (Planned_Date for PlannedDateColumn in (DO_Planned,

                                                       CX_Planned,

                                                       NE_Planned,

                                                       PA_Planned,

                                                       GA_Planned,

                                                       SA_Planned,

                                                       PE_Planned,

                                                       ES_Planned,

                                                       EM_Planned,

                                                       ED_Planned)) as unpvt),

 CTE2 as (

 select *, ROW_NUMBER() over (partition by [MATNR] order by Planned_Date desc) rn

   from CTE

  where Planned_Date < GETDATE())



-- cte2 is LATEST planned status by < getdate()

 select coalesce(c.[MATNR],actual.[MATNR]) [MATNR],

	c.Planned_Date,

	t.DateCode PlannedCode,

	t.Status PlannedStatus,

	ta.DateCode ActualCode, ta.Status ActualStatus

   from (

	select * from CTE2 where rn = 1

	) c

   full outer join

	(

	SELECT     [MATNR], CASE WHEN ED_Actual IS NOT NULL THEN 'ED'

	WHEN ES_Actual IS NOT NULL THEN 'ES'

	WHEN EM_Actual IS NOT NULL THEN 'EM' 

	WHEN PE_Actual IS NOT NULL THEN 'PE'

	WHEN SA_Actual IS NOT NULL THEN 'SA'

	WHEN GA_Actual IS NOT NULL THEN 'GA'

	WHEN PA_Actual IS NOT NULL THEN 'PA'

	WHEN NE_Actual IS NOT NULL THEN 'NE'

	WHEN CX_Actual IS NOT NULL THEN 'CX'

	WHEN DO_Actual IS NOT NULL THEN 'DO'

	WHEN VR_Actual IS NOT NULL THEN 'VR'

	ELSE '' END AS DateCode

	FROM         dbo.PLC_6_Months_File

	) actual on actual.[MATNR] = c.[MATNR]

   left join PLC_Status_Values t on substring(c.PlannedDateColumn,1,2) = t.DateCode

   left join PLC_Status_Values ta on actual.DateCode = ta.DateCode

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33758742
Really ? wow, wouldnt have thought that was what was being asked for... So long as it gives you the desired result.

Really was hoping for some feedback from you before embarking on the code (like those select into - are they used / needed subsequently) because it is apparent that we are seeing just a section (there must be the checking and dropping of some tables for the select into).

For what it's worth, this is what I was thinking (and maybe you might find some use - had written it anyway whilst waiting for feedback) :


;with CTE as 

(



   select [MATNR],PlannedDateColumn,Planned_Date, left(PlannedDateColumn,2) as PlannedCode

          , ROW_NUMBER() over (partition by [MATNR] order by Planned_Date desc) rn

          , CASE WHEN ED_Actual IS NOT NULL THEN 'ED'

	             WHEN ES_Actual IS NOT NULL THEN 'ES'

	             WHEN EM_Actual IS NOT NULL THEN 'EM' 

	             WHEN PE_Actual IS NOT NULL THEN 'PE'

	             WHEN SA_Actual IS NOT NULL THEN 'SA'

	             WHEN GA_Actual IS NOT NULL THEN 'GA'

	             WHEN PA_Actual IS NOT NULL THEN 'PA'

	             WHEN NE_Actual IS NOT NULL THEN 'NE'

	             WHEN CX_Actual IS NOT NULL THEN 'CX'

	             WHEN DO_Actual IS NOT NULL THEN 'DO'

	             WHEN VR_Actual IS NOT NULL THEN 'VR'

	        ELSE '' 

            END AS ActualCode 

   from  (select * from PLC_6_Months_File) p

          unpivot 

         (Planned_Date for PlannedDateColumn in (DO_Planned,CX_Planned,NE_Planned,PA_Planned,GA_Planned,SA_Planned,PE_Planned,ES_Planned,EM_Planned,ED_Planned)) as unpvt

   where Planned_Date < GETDATE()



)



select matnr, Planned_Date, PlannedCode, tp.status as PlannedStatus, ActualCode, ta.status as ActualStatus

from cte

left join PLC_Status_Values tp on tp.DateCode = plannedCode

left join PLC_Status_Values ta on ta.DateCode = actualCode

where rn = 1

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

16 Experts available now in Live!

Get 1:1 Help Now