Question how to create this in SQL Server

IF ADJ_DAYS_OFF_FLAG= '1' and   ADJ_CONSECUTIVE_DAYS ='1'
THEN [value] of ADJ_DAYS_OFF 
ELSE [value] of ELIG_DAYS_OFF

I have:

I want:

I've tried:

SELECT [PARTICIPANT], 
FORMAT((CASE 
WHEN [PERIOD]='2021-12-01' THEN ((22-SUM([VALUE]))/22)/100
WHEN [PERIOD]='2022-01-01' THEN ((23-SUM([VALUE]))/23)/100
WHEN [PERIOD]='2022-02-01' THEN ((21-SUM([VALUE]))/21)/100
END), 'P') AS 'PERCENT', [PERIOD]
FROM [TABLE]
WHERE CASE
WHEN [NAME]='ADJ_DAYS_OFF_FLAG' AND [NAME]='ADJ_CONSECUTIVE_DAYS' AND SUM([VALUE])>='2'
THEN [NAME]='ADJ_DAYS_OFF'
ELSE [NAME]='ELIG_DAYS_OFF'
END
GROUP BY [PARTICIPANT], [PERIOD]
;

CodePudding user response：

Simple conditional aggregation seems to suffice

SELECT
  t.PARTICIPANT,
  t.PERIOD,
  CONCAT(
    (23 - CASE WHEN
       SUM(CASE WHEN t.NAME = 'ADJ_DAYS_OFF_FLAG'    THEN t.VALUE END) *
       SUM(CASE WHEN t.NAME = 'ADJ_CONSECUTIVE_DAYS' THEN t.VALUE END)
     > 0 THEN
       SUM(CASE WHEN t.NAME = 'ADJ_DAYS_OFF'         THEN t.VALUE END)
     ELSE
       SUM(CASE WHEN t.NAME = 'ELIG_DAYS_OFF'        THEN t.VALUE END)
     END
     ) * 100 / 23
    ,'%')
FROM YourTable t
GROUP BY
  t.PARTICIPANT,
  t.PERIOD;

db<>fiddle

CodePudding user response：

SQL Server has the IIF keyword for this. (Short for Inline IF)

Here is a guide.

Here is an example:

SELECT Username,
  IIF(Role = 'Manager' OR Role = 'Lead', 1, 0) AS IsSupervisor
FROM Users