Make CTE statement a case statement

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

FairfieldAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cyberkiwiConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify why you want to change from CTE to CASE ?

should the data output change, somehow?
please clarify
0
 
LowfatspreadCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mark WillsTopic AdvisorCommented:
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
 
cyberkiwiCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.