Count, Case, Condition help

hatra · December 7, 2018, 4:01pm

Thanks but it seems like you missed it or i haven't been saying it clearer,
I have a "limitation" to perform anything like these on the sever, all I have is the code and number rows of data,
just to recap again
" I shared the code, and I can post various row of data to cover all three scenarios".
thanks

Muj9 · December 7, 2018, 4:14pm

try this:-

  ;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 (pmKey is null or 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 
case when [NoPlan] >= [Non Compliant Plan] then Null else case when [Non Compliant Plan] > 0 then 'Non Compliant Plan' end end as First_Value_to_Consider_if_not_null
,* 
from cte5
order by FINAL_ASSETNUM

hatra · December 7, 2018, 4:56pm

returned:
-------------------------------- null, where it should have been NON complaint plan,

First_Value_to_Consider_if_not_null	FINAL_ASSETNUM	FINAL_classificationid	assetattrid	alnvalue	pmKey	conKey	On Demand	NoPlan	Compliant Plan	Non Compliant Plan
NULL	82021050	621	PLANNING TYPE	2M-AUTO	82021050-621-PLANNING TYPE-2M-AUTO-1-01-33764-6-MONTHS	82021050-621-PLANNING TYPE-2M-AUTO-1-01-33764-6-MONTHS	NULL	1	2	1
NULL	82021050	621	PLANNING TYPE	2M-AUTO	NULL	82021050-621-PLANNING TYPE-2M-AUTO-1-01-87277-1-MONTHS	NULL	1	2	1
NULL	82021050	621	PLANNING TYPE	2M-AUTO	82021050-621-PLANNING TYPE-2M-AUTO-3-01-33659-2-MONTHS	82021050-621-PLANNING TYPE-2M-AUTO-3-01-33659-2-MONTHS	NULL	1	2	1

---------------------------- below returns correct , but I believe it is broken, because , it shows either null or non complaint plan, where I know the data should return the other two "NoPLan" and complaint Plan,

the last code I have posted was the closest we got
------ this code -------------
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 = '621'

if you want I can put come data in csv and send it the way you prefer??
I have one question and I might be thinking completely wrong:
can we not just simplify and say " count the number of FINAL_ASSETNUM " if it is repeated than compare conky with pmkey

if final_Assetnum is same or repeated than if conkey for all those repeated final_asset key valid ( not null) and all pmkey for the same repeated final_assetnum is null = "noplan"
1. if final_Assetnum is same or repeated than if conkey for all those repeated final_asset key valid ( not null) and all pmkey for the same repeated final_assetnum valid ( not null) = "Compliant Plan"
  3, if for all repeated final_assetnum copare conkey and pmkey and if all not match ="Non Compliant Plan" ( regardles how many null conkey or pmkey may return ) but we need to group the final_assetnum tight so it always fall into it.

i am online for another 15 min if you need me to send you the data in form of csv to anywhere

thanks

yosiasz · December 7, 2018, 5:26pm

No one is asking you to do this on your server.
Sample data is for us to do it in our server

Count, Case, Condition help

select * from cte6 where FINAL_classificationid = '621'

select * from cte6
where FINAL_classificationid = '621'