Count, Case, Condition help

Thanks for respond Muj9, as I mentioned we have to conciser Final_AssetNum because it could be more than one, this is very important part
ie as per my example before ( i have posted it again for information) pmKey is nul in all three finnal_Assetnum and conkey is valid in all three Finnal_Assetnum so the result should be "NOPLAN"

10442114 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS

on the other scenario ( see below data , I have posted again ) pmkey and conkey both match on all three FINNAL_assetnum so the result should be "Compliant Plan",

10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS

else "Non Compliant Plan" but again considering Finnal_asstnum

10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS NULL
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS NULL

thanks

So this is where I get confused, in your Non Compliant Plan you have a case where both conkey and pmkey match and all your examples use the same Final_Assetnum. so how is the line I highlighted in this example any different than your Compliant plan case?????

Now if you gave me an example like below I can understand :

"NOPLAN"

1111111 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
1111111 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
1111111 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS

"Compliant Plan",

2222222 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
2222222 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
2222222 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS

"Non Compliant Plan"

10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS NULL
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS NULL

Sorry, that is based on scenario that I have build, a Non Compliant Plan , a no plan, a Compliant Plan senario
I have changed the finnal_assetnum (edited ) so it wont make any confusion,
I hope that make it clearer ,

FINAL_ASSETNUM FINAL_classificationid assetattrid alnvalue pmKey conKey (Non Compliant Plan)
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS NULL
10442114 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS NULL
20442115 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS (Compliant Plan)
20442115 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
20442115 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS
30442116 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS No Plan
30442116 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
30442116 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS

thanks

I think I have a solution run the below script and the last three columns should guide you on what you should put for your new column

;with asset_list_pmkey as (
	select 
	asset.assetnum, 
	classstructure.classificationid, 
	assetattrid, 
	alnvalue, 
	pm.priority, 
	pmsequence.jpnum, 
	pm.frequency * pmsequence.interval as frequency, 
	pm.frequnit,
	 case 
	 when assetattrid is null then CONCAT (asset.assetnum, '-', classstructure.classificationid, '-NOPT-NOALN-' , pm.priority, '-',pmsequence.jpnum, '-',pm.frequency * pmsequence.interval,'-',pm.frequnit)
	 else CONCAT (asset.assetnum, '-',classstructure.classificationid, '-' ,assetattrid, '-', alnvalue, '-' , pm.priority, '-',pmsequence.jpnum, '-',pm.frequency * pmsequence.interval,'-',pm.frequnit) end as pmKey
	from ASSET
	 left join classstructure on asset.classstructureid = classstructure.classstructureid
	 left join assetspec on asset.assetnum = assetspec.assetnum and asset.siteid = assetspec.siteid and assetspec.assetattrid = 'PLANNING TYPE'
	 left join pm on pm.assetnum = asset.assetnum and pm.siteid = asset.siteid
	 left join pmsequence on pm.pmnum = pmsequence.pmnum and pm.siteid = pmsequence.siteid
	 where asset.siteid = 'HAL'
	 and asset.status in ('OPERATING','AWAITPLAN')
	 and classificationid = '621' and pm.status = 'ACTIVE'
 ),MPM_Key as (
	select 
	asset.assetnum as MPM_ASSET, 
	classstructure.classificationid as MPM_CLASS, 
	s.[Attribute Name], s.[Attribute Value],
	CASE WHEN s.[Attribute Value] is NULL THEN CONCAT(asset.assetnum, '-', s.Classification, '-NOPT-NOALN-',s.Priority,'-', s.[JOB PLAN], '-',CASE WHEN s.[JP Frequency] is NULL THEN 'On' else s.[JP Frequency] END, '-', s.[Freq unit])
	 ELSE CONCAT(asset.assetnum, '-', s.Classification, '-', s.[Attribute Name],'-',s.[Attribute Value],'-' ,s.Priority,'-', s.[JOB PLAN], '-',CASE WHEN s.[JP Frequency] is NULL THEN 'On' else s.[JP Frequency] END, '-', s.[Freq unit]) end as ConKey
	from ASSET
	 left join classstructure on asset.classstructureid = classstructure.classstructureid
	 left join assetspec on asset.assetnum = assetspec.assetnum and asset.siteid = assetspec.siteid and assetspec.assetattrid = 'PLANNING TYPE'
	 left outer join star s on classificationid = s.classification and isnull(alnvalue, 'NOALN') = isnull(s.[Attribute Value], 'NOALN') and isnull(assetattrid, 'NOPT') = isnull(s.[Attribute Name],'NOPT')
	 where asset.siteid = 'HAL'
	 and asset.status in ('OPERATING','AWAITPLAN')

),cte3 as (
	SELECT 
	 isnull(asset_list_pmkey.assetnum, MPM_Key.MPM_ASSET) as FINAL_ASSETNUM,
	 isnull(asset_list_pmkey.classificationid, MPM_Key.MPM_CLASS) as FINAL_classificationid,
	 isnull(asset_list_pmkey.assetattrid, MPM_Key.[Attribute Name]) as assetattrid,
	 isnull(asset_list_pmkey.alnvalue, MPM_Key.[Attribute Value]) as alnvalue,
	 asset_list_pmkey.pmKey,
	 MPM_Key.conKey
	 ,count(asset_list_pmkey.pmKey) as countpmkey, 
	  count(MPM_Key.conKey) as countconkey
	 ,Case when MPM_Key.conKey like '%-On-%' Then 'On' End as 'On Demand'
	 from asset_list_pmkey
	 full outer join MPM_Key on pmKey = ConKey
	 group by 
	 asset_list_pmkey.assetnum, MPM_Key.MPM_ASSET, asset_list_pmkey.classificationid, MPM_Key.MPM_CLASS, asset_list_pmkey.assetattrid, MPM_Key.[Attribute Name],
	 asset_list_pmkey.alnvalue, MPM_Key.[Attribute Value], asset_list_pmkey.pmKey,MPM_Key.conKey
) ,cte4 as ( 
	select 
	 FINAL_ASSETNUM
	,sum(case when pmKey  is null and  conKey is not null then 1 else 0 end) as [NoPlan]
	,sum(case when pmKey  is not null and  conKey is not null then 1 else 0 end) as [Compliant Plan]
	,sum(case when conKey is null then 1 else 0 end) as [Non Compliant Plan]
	from cte3
	group by 
	FINAL_ASSETNUM
),cte5 as (
	select 
	 cte3.* 
	,[NoPlan]
	,[Compliant Plan]
	,[Non Compliant Plan]
	from cte3 
	left join cte4 on cte3.FINAL_ASSETNUM = cte4.FINAL_ASSETNUM
)
select * from cte5

did the above work?

very smart , I think you did it :slight_smile: ,quick questions so I can understand better please ,
I tried to chekc the last three columns to see what is happening but i am note sure could you please give a very quick brief ? and the second question is what do these three lines do mainly what Sum and 1,0 do , please

to each row if the condition is met 1 is record and 0 where the condition has not been met.
Then sum all the ones for Final Assetnum, in the last three columns which ever returns the highest value that is the name you will give in your new column.

thank you very much for your help , :slight_smile:

@Muj9 can we leave the question as original I have posted and remove the specific codes but leaving your last brief explanation here rather than exact codes . thanks

@Muj9
There one other issue and I just noticed it now ,
It still doesn't show the Compliant_Status right,
for example I have 4 of FINAL_ASSETNUM the same and conkey and pmKey not match and it should show "Non Compliant Plan" but it shows 'Non Compliant Plan' for two of same FINAL_ASSETNUM and 'NOPLAN for the other 2 ' of same FINAL_ASSETNUM,
because all 4 FINAL_ASSETNUM are same is should give 'Non Compliant Plan' for all the rows as conkey and pmkey not match,
any advice ?
sample attached below

Non Compliant Plan 10001527 136-362 NULL NULL 10001527-136-362-NOPT-NOALN-3-1007A-192-DAYS NULL NULL 2 0
NoPlan 10001527 136-362 NULL NULL NULL 10001527-136-362-NOPT-NOALN-3-1007A-6-MONTHS NULL 2 0
NoPlan 10001527 136-362 NULL NULL NULL 10001527-136-362-NOPT-NOALN-3-1007B-1-YEARS NULL 2 0
Non Compliant Plan 10001527 136-362 NULL NULL 10001527-136-362-NOPT-NOALN-3-1007B-384-DAYS NULL NULL 2 0

another example

NoPlan 89095011 823-608-361 PLANNING TYPE OTHER NULL 89095011-823-608-361-PLANNING TYPE-OTHER-1-01-18525-1-YEARS NULL 2 0 2
NoPlan 89095011 823-608-361 PLANNING TYPE OTHER NULL 89095011-823-608-361-PLANNING TYPE-OTHER-1-01-33712-3-MONTHS NULL 2 0 2
Non Compliant Plan 89095011 823-608-361 PLANNING TYPE OTHER 89095011-823-608-361-PLANNING TYPE-OTHER-1-1047-384-DAYS NULL NULL 2 0 2
Non Compliant Plan 89095011 823-608-361 PLANNING TYPE OTHER 89095011-823-608-361-PLANNING TYPE-OTHER-1-1055-96-DAYS NULL NULL 2 0 2

anyone can advice?

hi

this is a case where you have to
"debug"
to find out the issue

what i mean is
see the data and check the data
"at each step"
examining it .. if it looks like EXPECTED

if there looks like something is NOT CORRECT
dig into it
see the logic , columns , data involved

:slight_smile:
:slight_smile:
I would do it for you but i don't have access to the data

HI thanks for the respond the last code I have it does work for 2 scenario but only fail for the else case,
I have tried to debug and test and only realised it recently, I know the issue but I cant figure out what to use or where to change,
:frowning:

thinking about it

works for 2 scenarios
fails in else

how to debug this ?
that means the logic being used is not matching all the scenarios
in the else part the other scenarios are going

example ..lets says you have

select case when column123 = 'A' then 1 else 2 end as ok

here it means when column123 is A then replace with 1 otherwise
if column123 is not A then replace with 2

but a hidden logic is missed here
where if column123 is null

if data has nulls then fails

question is
how to find out these other logics
test
and change code

thanks for your advice,
I am happy to share the code I have and if you have any better solution I will be grateful?
I can explain the 3 scenario that expected if previous comment not making sense

thanks

please send me the code

explain what you are looking for

thank you ever so much ,
so my current code is as below

with asset_list_pmkey as (

select asset.assetnum, classstructure.classificationid, assetattrid, alnvalue, pm.priority, pmsequence.jpnum, pm.frequency * pmsequence.interval as frequency, pm.frequnit,
case when assetattrid is null then CONCAT (asset.assetnum, '-', classstructure.classificationid, '-NOPT-NOALN-' , pm.priority, '-',pmsequence.jpnum, '-',pm.frequency * pmsequence.interval,'-',pm.frequnit)
else CONCAT (asset.assetnum, '-',classstructure.classificationid, '-' ,assetattrid, '-', alnvalue, '-' , pm.priority, '-',pmsequence.jpnum, '-',pm.frequency * pmsequence.interval,'-',pm.frequnit) end as pmKey
from ASSET

left join classstructure on asset.classstructureid = classstructure.classstructureid
left join assetspec on asset.assetnum = assetspec.assetnum and asset.siteid = assetspec.siteid and assetspec.assetattrid = 'PLANNING TYPE'
left join pm on pm.assetnum = asset.assetnum and pm.siteid = asset.siteid
left join pmsequence on pm.pmnum = pmsequence.pmnum and pm.siteid = pmsequence.siteid
where asset.siteid = 'HAL'
and asset.status in ('OPERATING','AWAITPLAN')
--and classificationid = '622' and pm.status = 'ACTIVE'
and pm.status = 'ACTIVE'
)
,

MPM_Key as (

select asset.assetnum as MPM_ASSET, classstructure.classificationid as MPM_CLASS, s.[Attribute Name], s.[Attribute Value],
CASE WHEN s.[Attribute Value] is NULL THEN CONCAT(asset.assetnum, '-', s.Classification, '-NOPT-NOALN-',s.Priority,'-', s.[JOB PLAN], '-',CASE WHEN s.[JP Frequency] is NULL THEN 'On' else s.[JP Frequency] END, '-', s.[Freq unit])
ELSE CONCAT(asset.assetnum, '-', s.Classification, '-', s.[Attribute Name],'-',s.[Attribute Value],'-' ,s.Priority,'-', s.[JOB PLAN], '-',CASE WHEN s.[JP Frequency] is NULL THEN 'On' else s.[JP Frequency] END, '-', s.[Freq unit]) end as ConKey

from ASSET
left join classstructure on asset.classstructureid = classstructure.classstructureid
left join assetspec on asset.assetnum = assetspec.assetnum and asset.siteid = assetspec.siteid and assetspec.assetattrid = 'PLANNING TYPE'
left outer join star s on classificationid = s.classification and isnull(alnvalue, 'NOALN') = isnull(s.[Attribute Value], 'NOALN') and isnull(assetattrid, 'NOPT') = isnull(s.[Attribute Name],'NOPT')
where asset.siteid = 'HAL'
and asset.status in ('OPERATING','AWAITPLAN')

--and classificationid = '622'
),cte3 as (
SELECT
isnull(asset_list_pmkey.assetnum, MPM_Key.MPM_ASSET) as FINAL_ASSETNUM,
isnull(asset_list_pmkey.classificationid, MPM_Key.MPM_CLASS) as FINAL_classificationid,
isnull(asset_list_pmkey.assetattrid, MPM_Key.[Attribute Name]) as assetattrid,
isnull(asset_list_pmkey.alnvalue, MPM_Key.[Attribute Value]) as alnvalue,
asset_list_pmkey.pmKey,
MPM_Key.conKey

 ,Case when MPM_Key.conKey like '%-On-%' Then 'On' End as 'On Demand'
 from asset_list_pmkey
 full outer join MPM_Key on pmKey = ConKey

) ,cte4 as (
select
case
when pmKey is null and conKey is not null then 'NoPlan'
when pmKey is not null and conKey is not null then 'Compliant Plan'
else 'Non Compliant Plan'
end as 'Compliant_Status'
,*
from cte3
),cte5 as (
select
FINAL_ASSETNUM
,sum(case when Compliant_Status = 'NoPlan' then 1 else 0 end) as [NoPlan]
,sum(case when Compliant_Status = 'Compliant Plan' then 1 else 0 end) as [Compliant Plan]
,sum(case when Compliant_Status = 'Non Compliant Plan' then 1 else 0 end) as [Non Compliant Plan]
from cte4
group by
FINAL_ASSETNUM

),cte6 as (
select
cte4.*
,[NoPlan]
,[Compliant Plan]
,[Non Compliant Plan]
from cte4
left join cte5 on cte4.FINAL_ASSETNUM = cte5.FINAL_ASSETNUM
)

select * from cte6
where FINAL_classificationid = '622'

the scenarios are as bellow

  1. for all of same FINAL_ASSETNUM ( it could be 1 to 4 or 5 of same assetnum) where 'pmkey' are null and conkey are valid ( return data) = 'No Plan'
  2. for all of same FINAL_ASSETNUM ( it could be 1 to 4 or 5 of same assetnum) where 'pmkey' and conkey match ( are the same) = 'Compliant Plan'
  3. for all of same FINAL_ASSETNUM ( it could be 1 to 4 or 5 of same assetnum) where pmkey and conkey not match ie for the same Final assetnum we may have some of pmkey null and some vaild which match conkey but not all match .
    some sample example
FINAL_ASSETNUM FINAL_classificationid assetattrid alnvalue pmKey conKey
50442123 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
50442123 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
50442123 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS
50442123 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS NULL
50442123 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS NULL
FINAL_ASSETNUM FINAL_classificationid assetattrid alnvalue pmKey conKey
43442118 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
43442118 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
43442118 621 PLANNING TYPE 3M-AUTO 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS
FINAL_ASSETNUM FINAL_classificationid assetattrid alnvalue pmKey conKey
10442114 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS
10442114 621 PLANNING TYPE 3M-AUTO NULL 10442114-621-PLANNING TYPE-3M-AUTO-3-01-33659-3-MONTHS

thanks

hi

I tried to debug this statement and results

I created sample data for myself and tested the logic

drop create data
use tempdb
go 


drop table sampledata
go 

create table sampledata
(
pmkey int null , 
conkey int null
)
go 

insert into sampledata select 1,null
insert into sampledata select  1,2
insert into sampledata select  null,3
insert into sampledata select  null,5
insert into sampledata select  5,6 
insert into sampledata select  5,null 
go 

select * from sampledata
go
SQL with cte where LOGIC is used
;WITH cte 
     AS (SELECT pmkey, 
                conkey, 
                CASE 
                  WHEN pmkey IS NULL 
                       AND conkey IS NOT NULL THEN 'NoPlan' 
                  WHEN pmkey IS NOT NULL 
                       AND conkey IS NOT NULL THEN 'Compliant Plan' 
                  ELSE 'Non Compliant Plan' 
                END AS 'Compliant_Status' 
         FROM   sampledata) 
SELECT * 
FROM   cte 

go
Result

image

What I observe is in RED
case when CASE
WHEN pmkey IS NOT NULL
AND conkey IS NULL

I dont see this logic anywhere

Could this be the reason ?
Please check

:slight_smile:
:slight_smile:

that wouldn't happen, the scenario would only happen under above 3 roles.
I think the else part in case is not taking final_assetnum in account and therefore it returns wrong result,
did my explanation on sample data make sense ?
it would be only under those roles

thanks