SQLTeam.com | Weblogs | Forums

Count, Case, Condition help


#62

the reason it is doing this is because the case condition only looks as Conkey and Pmkey along side each other but the examples you have shown me show either Conkey to be null and Pmkey not null or visa versa.

what you need to do is use

,coalesce(pmKey,conKey) as [Key]
and do row number to take this into consideration

sorry I am at work atm and very busy if this hasn't been resolved by the afternoon by yourself I will work at it.


#63

Thanks @Muj9,
I am not sure how to use the coalesce and where to, I will try to learn a little bit of it, and see if I can do it, I very much appreciate you help,
just looked at the coalesce, this will fill the null row to the new column,

I think what we are not putting into account and ignoring is FInal_AssetNum and if they are same than compare the conkey and pmkey.
cheers


#64

cheers


#65

i have just looked at the code you are using and it does not seem to be the latest code I had posted can you confirm if you are still having the same issue using 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 = '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
)	select 
	 cte3.* 
	,[NoPlan]
	,[Compliant Plan]
	,[Non Compliant Plan]
	from cte3 
	left join cte4 on cte3.FINAL_ASSETNUM = cte4.FINAL_ASSETNUM

#66

Hi, this isnt the latest as you see there is not 'Compliant_Status' which we have added toward the end, also the lonf listing group wasn't necessary, as you might remember we had to have Compliant_Status' to determine what the result will be, also ,count(asset_list_pmkey.pmKey) as countpmkey,
count(MPM_Key.conKey) as countconkey wasnt valid, i may have removed the latest code,
-------- here is the latest code----- and issue is still there

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'


#67

use my code the one I have pasted and let me know if the problem is still there what you have pasted is not what my latest code is.


#68

I just have and there is the same issue for example

FINAL_ASSETNUM FINAL_classificationid assetattrid alnvalue pmKey conKey countpmkey countconkey On Demand NoPlan Compliant Plan Non Compliant Plan
30219542 621 PLANNING TYPE 2M-NONAUTO NULL 30219542-621-PLANNING TYPE-2M-NONAUTO-1-01-87277-1-WEEKS 0 1 NULL 1 2 0
30219542 621 PLANNING TYPE 2M-NONAUTO 30219542-621-PLANNING TYPE-2M-NONAUTO-1-01-33764-6-MONTHS 30219542-621-PLANNING TYPE-2M-NONAUTO-1-01-33764-6-MONTHS 1 1 NULL 1 2 0
30219542 621 PLANNING TYPE 2M-NONAUTO 30219542-621-PLANNING TYPE-2M-NONAUTO-3-01-33659-2-MONTHS 30219542-621-PLANNING TYPE-2M-NONAUTO-3-01-33659-2-MONTHS 1 1 NULL 1 2 0

this should return Non Complaint plan as
pm key not matching conkey for all same final_assetnum

......
( if pm key is null for all same FINAL_ASSETNUM and conkey are all valid for the all same FINAL_assetnum 'noplan', if conkey and pmkey for the all same FINALkey are match 'Complaint PLan' and the else when all FINAL_ASSETNAM are same, just like the sample data i have posted)


#69

so basically if the [Non Compliant Plan] filed returns a value greater than 0 then just mark the column as Non Complaint plan, you have the three columns with their value so what is the problem????


#70

conkey for the same 3 FINAL_ASSETNUM are valid but for the pmkey of the 3 same FINALASSETNUM one is null so that should give hightest number for Non Complaint, in this instant the complaint plan has the highset number which is not correct becasue , for Complaint plan both keys con and pm are matching and valid for the same FINALASSETNUM,
please see the result i sent , nonCOmplaint is 0


#71
  ;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.* 
			,case when [Non Compliant Plan] > 0 then 'Non Compliant Plan' end as First_Value_to_Consider_if_not_null
			,[NoPlan]
			,[Compliant Plan]
			,[Non Compliant Plan]
			from cte3 
	left join cte4 on cte3.FINAL_ASSETNUM = cte4.FINAL_ASSETNUM
	
)
select * from cte5
order by FINAL_ASSETNUM

#73

hey ,
thanks for reply, with the latest code everything return are 'Non Compliant' ,
please see below example from latest code,
---------------------------------------------------------------- this example is correct

FINAL_ASSETNUM FINAL_classificationid assetattrid alnvalue pmKey conKey On Demand First_Value_to_Consider_if_not_null NoPlan Compliant Plan Non Compliant Plan
82017288 259-386 NULL NULL 82017288-259-386-NOPT-NOALN-1-01-19589-32-DAYS NULL NULL Non Compliant Plan 1 0 2
82017288 259-386 NULL NULL NULL 82017288--NOPT-NOALN---On- On Non Compliant Plan 1 0 2

-------------------------below example not correct it should show noPLan

FINAL_ASSETNUM FINAL_classificationid assetattrid alnvalue pmKey conKey On Demand First_Value_to_Consider_if_not_null NoPlan Compliant Plan Non Compliant Plan
82017287 259-386 NULL NULL NULL 82017287--NOPT-NOALN---On- On Non Compliant Plan 1 0 1

#74

Provide sample data. Otherwise you get this result of people stabbing in the dark. And nothing people are posting is answering your question


#75

I can copy some data in csv file, attach it or copy and paste it here,
would that work?
I would have provided in early stage but not able to perform much on the database.


#76

i have seen a post in sqlteam.com

where

the data was posted in google docs excel

and the link was provided
here in post

hope this suggestion helps
:slight_smile:
:slight_smile:


#77

hi

please see below post

you will get idea


#78

Hi thanks,
I am struggling to relate this to my scenario :frowning:


#79

hi

what i posted was related to your comment

I can copy some data in csv file, attach it or copy and paste it here,
would that work?

#80

The better thing will be providing the data the following. If you provide excel then we have to do the below. Folks are busy

--below is sample table, provide one with real column names. This way we have your data in our sql server to try to help you
Create table #sample(col1 varchar(50), col2 int,, Cole bit, col4 datetime)
--then sample data. Here I am showing bogus data. In your case provide real or newer to real data
Insert into #sample
Select 'a', 1234, 0, getdate() union
Select "b", 345, 1, "2018-01-01"

#81

can I attach the excel here? I dont seem to see anywhere to attach a file ??


#82

Did you read what I just posted? No excel, post dml and ddl