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
- 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'
- 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'
- 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