SQLTeam.com | Weblogs | Forums

Count, Case, Condition help


#1

Hi,
I have got a view, in my view there are two columns that I need to make a count and compare and name them as per the condition

counting the first and the second column to return the result.
In this example I want to check if the second column has more null , then return 'semi valid'

ref no       first column      second column      result (new column) 
1234         yes               yes 
1234         null              null
1234         yes               null

In this example I want to check if there's no null in both, then return 'valid'

ref no       first column      second column      result (new column) 
1234         yes               yes 
1234         yes               yes
1234         yes              yes

In this example I want to check if the first column has more null , then return 'not valid'

ref no       first column      second column      result (new column) 
1234         null              yes 
1234         null              yes
1234         null              yes

I am happy to forward the view and better data if this could be done and if anyone can help , thanks


#2

What is the difference in your case condition between 1st and 3rd example apart from giving it a different value?

case
when [first column]  is not null and  [second column] is null then 'semi valid'
when [first column]  is not null and  [second column] is not null then 'valid'
when [first column]  is null and  [second column] is not null then 'Not valid'
end as [new column]

#3

hi thanks for your respond , in first condition thee is one null in first column and two null in second column
your example couldn't work because as per my example I has multiple values for each ref no.

basically I want to say count number of null in second column and if it is more then null in first column " failed"
example 2,if both columns has no null pass
sample 3, count number of null in both first and second column and if one has no null and other has null semifailed
example if first column includs null for all refno and second column no null check

thanks
ps: happy provide my view an more data if something you think it can be done .


#4

Add this into your SQL select statement and see the order as a start:-

row_number() over (partition by [ref no] order by 	case	when [first column]  is not null and  [second column] is not null then 1
								when [first column]  is not null and  [second column] is null then 2
								when [first column]  is null and  [second column] is not null then 3
							end 
) as Check_column_order

#5

Muj9 thanks ,
the order goes to 624 and starts again on 1,
repeated refno for example
refno checkcolumnorder
1234 1
1234 2


#6

yes ok


#7

is there not a direct message here


#8

hi

please find my work

is this what you are looking for ????

in the data

drop create data
use  tempdb 
go 


drop table temp
go 


create table temp
(
refno    int ,    
firstcolumn varchar(100) null,      
secondcolumn varchar(100) null)
go


insert into temp  select 1234   ,'yes','yes '
insert into temp  select 1234   ,null,null
insert into temp  select 1234   ,'yes',null
go
SQL
SELECT a.refno, 
       firstcolumn, 
       secondcolumn, 
       result 
FROM   temp a 
       JOIN (SELECT refno, 
                    CASE 
                      WHEN Sum(CASE 
                                 WHEN firstcolumn IS NULL THEN 1 
                                 ELSE 0 
                               END) < Sum(CASE 
                                            WHEN secondcolumn IS NULL THEN 1 
                                            ELSE 0 
                                          END) THEN 'semi valid' 
                    END AS result 
             FROM   temp 
             GROUP  BY refno) b 
         ON a.refno = b.refno
Result

image


#9

@harishgg1
thanks for the respond , no
I ma trying to generate a new column based on count and compare two columns,
here is my view ( for the attention of @Muj9 too)
as above


#10

using the script created by @harishgg1

drop table #temp
go 


create table #temp
(
refno    int ,    
firstcolumn varchar(100) null,      
secondcolumn varchar(100) null)
go


insert into #temp  select 1234   ,'yes','yes '
insert into #temp  select 1234   ,null,null
insert into #temp  select 1234   ,'yes',null
go

insert into #temp  select 1234   ,'yes','yes'
insert into #temp  select 1234   ,'yes','yes'
insert into #temp  select 1234   ,'yes','yes'
go

insert into #temp  select 1234   ,'yes','yes'
insert into #temp  select 1234   ,'yes','yes'
insert into #temp  select 1234   ,'yes','yes'
go

insert into #temp  select 1234   ,'yes','yes'
insert into #temp  select 1234   ,'yes','yes'
insert into #temp  select 1234   ,'yes',NULL
go

insert into #temp  select 1234   ,NULL,'yes'
insert into #temp  select 1234   ,NULL,'yes'
insert into #temp  select 1234   ,NULL,'yes'
go


select * ,case
when firstcolumn  is not null and  secondcolumn is null then 'semi valid'
when firstcolumn  is not null and  secondcolumn is not null then 'valid'
when firstcolumn  is null and  secondcolumn is not null then 'Not valid'
end as [new column]
,row_number() over (partition by refno order by 	
							case	
								when firstcolumn  is not null and  secondcolumn is not null and firstcolumn = secondcolumn  then 1
								when firstcolumn  is not null and  secondcolumn is null then 2
								when firstcolumn  is null and  secondcolumn is not null then 3
								else 4
							end ) as Check_column_order

from #temp

#11

sqlteam


#12

thanks for the sample, I have tried to implement it to my view but I failed to have it working, I have posted my view and sample,
is this something can be done on the code i posted ,
you sample looks great


#13

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
) 
select
case
when  pmKey  is not null and  conKey is 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

#14

Thanks , I think this is very very close to what I want,
there is only two issue,

  1. it doesn't conciser FINAL_ASSETNUM in the case , thats why I am getting a
    |Compliant Plan|85009913|621|PLANNING TYPE|3M-AUTO|85009913-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS|85009913-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS|NULL|
    |---|---|---|---|---|---|---|---|
    |NoPlan|85009913|621|PLANNING TYPE|3M-AUTO|NULL|85009913-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS|NULL|
    which is of two same FINAL_ASSETNUM value the result for new column is 'Compliant Plan' and 'NoPlan', for the same FINAL_ASSETNUM
    so it really should look at the FInnal_AssetNum ( because there is more likely there would be some same ) first and than conkey and than pmkey, in above sample I should only get 'NonCompliant Plan'

#15

ok 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
				row_number() over (partition by FINAL_ASSETNUM order by case
																		when  pmKey  is not null and  conKey is null then 1
																		when  pmKey  is not null and  conKey is not null then 2
																		else 3
																		end --desc
																		) as Check_column_order
				,case
				when  pmKey  is not null and  conKey is 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
) select * from cte4 
where Check_column_order = 1

#16

sorry why should it return 'NonCompliant Plan'?


#17

Thanks again my friend , very kind of you to helping me out,

it is because conkey are valid under two final_assetNum and pmkey has one null and one matching valu with conkey

there most be some issues firstly I have commented out the counts line and group by lines, how ever the first solution you gaveeme it returns 610605 which was right number of result and i have some valid pmkey but the second solution returns 548721 records and all valid pmkey are blank which is not right number of records, not sure what coused it? t


#18

@Muj9
any thoughts ?


#19

So you said the above example should return 'NonCompliant Plan', lets look at the first example it :
|Compliant Plan|85009913|621|PLANNING TYPE|3M-AUTO|85009913-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS|85009913-621-PLANNING TYPE-3M-AUTO-1-01-33764-6-MONTHS|NULL|
The pmkey and conkey match so it returned Compliant Plan

the 2nd example
NoPlan|85009913|621|PLANNING TYPE|3M-AUTO|NULL|85009913-621-PLANNING TYPE-3M-AUTO-1-01-87277-1-MONTHS|NULL|

The Pmkey is null and conkey is there and it returned NoPlan which is what we are telling it to do so I am confused as to why it would return 'NonCompliant Plan' as we are telling the code only to do this when non of the condition meet which we have set


#20

perhaps go over the conditions again and tell me if
conkey and pmkey exists then to do what ?
conkey and pmkey does not exist then to do what ?
conkey exists and pmkey does not exist then do what?
etc...