below is the given table in DB

expected output is

if ALL the app code for a particular year is LF then 1

if ALL the app code for a particular year is EBB the 2

if the app code for a particular year having both LF and EBB the 3.

need help in SQL

CodePudding user response：

To calculate flags for each year and org_id separately you can use following query:

SELECT *,
MAX(CASE WHEN app_code='EBB' THEN 2 ELSE 0 END) OVER (PARTITION BY org_id, year)  
MAX(CASE WHEN app_code='LF' THEN 1 ELSE 0 END) OVER (PARTITION BY org_id, year) Flag
FROM T

If version < 8.0 you can use more complex query:

SELECT T.*, F.Flag
FROM T
JOIN (
  SELECT org_id, year, CASE WHEN IsEBB=1 AND IsLF=1 THEN 3 WHEN IsEBB=1 THEN 2 ELSE 1 END Flag
    FROM (
      SELECT org_id, year, MAX(CASE app_code WHEN 'EBB' then 1 END) IsEBB, MAX(CASE app_code WHEN 'LF' then 1 END) IsLF
      FROM T
    GROUP BY org_id, year
  ) Group1
) F ON T.org_id=F.org_id AND T.year=F.year

CodePudding user response：

You can do this with CASE.

SELECT
ID
,org_id
,app_code
,year
,CASE WHEN app_code = 'EBB' AND year = 2016 THEN 2
      WHEN app_code = 'LF' AND year = 2017 THEN 1
  --add other conditions here
 END AS Flag

FROM
tbl_name