How to select code type 1900 and 1885 when exist at least one time per part ?
-
How to select code type 1900 and 1885 when exist at least one time per part ?
I work on sql server 2012 I face issue I can't select Parts from table trades
that exist at least one time per for code type 1900 and 1885
so i need to make query select and get parts that
1- have code type 1885 or code type 1900 or both
2- if part id have code type 1885 or 1900 multiple time per part display it as part 20890
3- if code type have 1885 and have also code type 1995 not display part as 22390
4-if part have code type 1900 and code type 3400 not display part as 27981
what i try
select * from #PartsTransactions where codetype in (1885,1900)
but what i do on another case
so how to make query do all all points above and give me result below
create table #PartsTransactions
(
TradeCodesId int identity(1,1),
PartId int,
CodeType int,
Code int,
PartLevel int
)
insert into #PartsTransactions(PartId,CodeType,Code,PartLevel)
values
(12590,1885,1000981,0),
(14320,1700,4321094,0),
(14320,1885,8551094,0),
(14320,1900,8925678,0),
(14320,1300,5876541,0),
(55321,1900,1124338,0),
(12590,1900,0198222,0),
(12590,1885,7023339,0),
(12890,1885,9904455,0),
(12890,5431,6667789,0),
(12590,7000,8765877,0),
(12590,8000,4441322,0),
(15320,3000,5901134,0),
(15320,2500,5000111,0),
(20890,1885,5790000,0),
(20890,1885,7777722,0),
(22390,1885,9801111,0),
(22390,1995,4443211,0),
(22390,6000,2234343,0),
(25792,1900,8999011,0),
(25792,2500,9000001,0),
(27981,1900,9876411,0),
(27981,3400,9011118,0),
(27981,2800,7770002,0)expected result
-
Maybe something like this
with part_cte as (
select PartId,
sum(case when CodeType=1885 then 1 else 0 end) n1885_count,
sum(case when CodeType=1900 then 1 else 0 end) n1900_count,
sum(case when CodeType=1995 then 1 else 0 end) n1995_count,
sum(case when CodeType=3400 then 1 else 0 end) n3400_count
from #PartsTransactions
group by PartId)
select p.*, pt.*,
case when p.n1885_count>1 or p.n1900_count>1 then 20890 /* accomplish #2 */
else pt.PartId end calc_PartId
from part_cte p
join #PartsTransactions pt on p.PartId=pt.PartId
where (p.n1885_count+p.n1900_count)>0 /* accomplish #1 */
and not((p.n1885_count>0 and p.n1995_count>0) and pt.PartId=22390) /* accomplish #3 */
and not((p.n1900_count>0 and p.n3400_count>0) and pt.PartId=27981) /* accomplish #4 */
order by TradeCodesId;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
thank you for reply
but sample is static code
I need query to be not static
as pt.PartId=22390
because table have more than 1000000 rows
-
What needs to be dynamic?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
I think he means this..
Just trimming Steve's code, this produces the "expected result"
with part_cte as (
select PartId,
sum(case when CodeType=1885 then 1 else 0 end) n1885_count,
sum(case when CodeType=1900 then 1 else 0 end) n1900_count,
sum(case when CodeType=1995 then 1 else 0 end) n1995_count,
sum(case when CodeType=3400 then 1 else 0 end) n3400_count
from #PartsTransactions
group by PartId)
select pt.TradeCodesId, pt.PartId, pt.CodeType, pt.Code, pt.PartLevel
from part_cte p
join #PartsTransactions pt on p.PartId=pt.PartId
where (p.n1885_count+p.n1900_count)>0 /* accomplish #1 */
and not(p.n1885_count>0 and p.n1995_count>0) /* accomplish #3 */
and not(p.n1900_count>0 and p.n3400_count>0) /* accomplish #4 */
order by TradeCodesId;Ken
-
thank you very much
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply