SQLTeam.com | Weblogs | Forums

Count, Case, Condition help


#41

it makes sense

what i am thinking could be the reason
i mean the logic i said i noticed
"is irrelevant ""not correct"


#42

I see, do you have any suggestion in what to do or how to do it ?


#43

hi

you could add that column final_assetnum to logic


#44

Sorry not sure how you mean,
which logic? and where,
do you mind to explain or send me step by step instruction or just add it to where I should cange from the code i sent ?
thanks


#45

Hi

please try this

,cte4 as (
select
FINAL_ASSETNUM, -- add this
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'
,* -- remove this
from
cte 3


#47

by removing ,* -- remove this, some of my column that I need to use are not included anymore, for example I cant see conkey , pmkey and Final_classificationid to compare whether or not conkey and pmkey match ,
I only get
FINAL_ASSETNUM COMpliant_Status NotPlan Complaint Plan Non Complaint Plan
and even without conkey and pm key I can see that isnt right and it is just the same from final_Assetnum and Complaint_Status, I belive this hasnt made any change to the result that we are aiming,

previouse code retuens below columns which I need,
Compliant_Status FINAL_ASSETNUM FINAL_classificationid assetattrid alnvalue pmkey conKey OnDemand NotPlan Complaint Plan Non Complaint Plan

:slight_smile:


#48

hi hatra

Please provide me with
sample data from actual data
in the form of scripts

and the SQL u r working on

Then I can see whats going on !!

Like i said
at each step it has to be checked and verified

Sorry Not able to understand
"without"
data and scripts for me to work on
:slight_smile:
:slight_smile:


#49

can you help me to understand how to provide data in form of script?
the sql I am working is exaclty what I sent you already , did you mean anything else?


#50

hi

i mean like this

drop table data
go

create table data
(
name varchar(100),
age int
)
go

insert into data select 'pam',40
insert into data select 'harry',20
insert into data select 'ravi',32
go

select * from data
go

if you have other tables in join
like above

Sorry I can solve your issue very quickly
I have a lot of experience

But I cannot do it
without getting hands on your data
and script


#51

I understand, and thank you for helping as this is very urgent , I just have a limitation to perform this actions,
how ever I can copy and paste a few lines which include all scenarios and hopefully that might help ?
the code I sent is a complete code for this solution. would that help ?


#52

hi

i have been in your situation
MANY MANY MANY times
while working

I have faced your same situation
MANY MANY times

Not sure how to do this !!!


#53

ok thanks no problem,
in my head seems simple and there most be some quick way to do it but I cant translate it into code :frowning: but thanks anyway, maybe some expert can see this and help


#54

wish you all the best

data makes code break in a lot of ways

sorry could not help
:slight_smile:
:slight_smile:


#55

:+1: for effort


#56

thanks :slight_smile:


#57

could this be solved by using partition? anyone ere know how to use the partition in my code attached above?
thanks


#58

https://docs.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes


#59

#60

ok thanks, still to figure out whether this approach works for above scenario


#61

all the best !! good luck .. :slight_smile: :slight_smile: