CODEWITHSUNDEEP
sqlsql-servert-sql
Nils
Nils

Reputation: 514

Case when on multiple tables

My Table looks like this: 

SELECT TOP 5 PlanningID, ActivityID, 
       [This Week - 2], [This Week - 1]
FROM [BI_Planning].[dbo].[tblPlanning]

enter image description here

I would like the outcome of this to be:

enter image description here

The code below does not give me what i want however the CASE expression should gives me 201814 and 201815 but it does not :/.

SELECT TOP 5 PlanningID,
       ActivityID,
       CASE WHEN [This Week - 2] IN ('TRUE','FALSE')-- OR  [This Week - 2] = 'FALSE'
            THEN (SELECT LEFT(CONVERT(VARCHAR(10),GETDATE(),120),4) +  
                              CAST((DATEPART(ISOWK,GETDATE()) - 2) AS NVARCHAR(2)))
            WHEN [This Week - 1] IN ('TRUE','FALSE')-- OR  [This Week - 1] = 'FALSE'
            THEN (SELECT LEFT(CONVERT(VARCHAR(10),GETDATE(),120),4) +  
                              CAST((DATEPART(ISOWK,GETDATE()) - 1) AS NVARCHAR(2)))
       END AS YearWeek
FROM [BI_Planning].[dbo].[tblPlanning]

Do i need to use some sort of Cross join and how can i use case when for multiple columns?

Upvotes: 1

Views: 279

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use apply operator 

SELECT p.PlanningID, p.ActivityID,
       (CASE WHEN t.id = 2 AND t.Weeks IN (1, 0) 
             THEN '201814' WHEN t.id = 1 AND t.Weeks IN (1, 0) 
             THEN '201815' END) AS ThisWeek
FROM [BI_Planning].[dbo].[tblPlanning] p 
CROSS APPLY (VALUES (2, [This Week - 2]), (1, [This Week - 1])) t(Id, Weeks);

However, i didn't find the logic behind the 0 or 1 for your weeks columns

Upvotes: 1

Related Questions