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 DateCodeINTO #tempTablePLC_GLOBAL_INFO_Actual_6FROM dbo.PLC_6_Months_FileSELECT dbo.#tempTablePLC_GLOBAL_INFO_Actual_6.[MATNR], dbo.#tempTablePLC_GLOBAL_INFO_Actual_6.DateCode, dbo.PLC_Status_Values.StatusINTO PLC_GLOBAL_STATUS_FILE_Actual_6FROM 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

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"...

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

;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

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

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 ActualStatusfrom cteleft join PLC_Status_Values tp on tp.DateCode = plannedCodeleft join PLC_Status_Values ta on ta.DateCode = actualCodewhere rn = 1

Featured Post

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers. It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
ht…

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…

Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …