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.
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
cheers
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_ASSETNUMHi, 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'
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.
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)
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????
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
;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_ASSETNUMhey ,
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 |
Provide sample data. Otherwise you get this result of people stabbing in the dark. And nothing people are posting is answering your question
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.
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
Hi thanks,
I am struggling to relate this to my scenario 
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?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"can I attach the excel here? I dont seem to see anywhere to attach a file ??
Did you read what I just posted? No excel, post dml and ddl