help in a query

[jrprogr]

I need a help in getting the output.
I want to display the values of T5 to 1,T3 to 2,T1 to 5.When the count is > then 1 it should be 4.

declare @Prod1 table
(

Csid bigint,
sid bigint
)
insert into @Prod1
select 4103, 101 union all
select 4101, 195 union all
select 4103, 101 Union all
select 4104, 201 Union all
select 4105, 202


declare @Prod2 table
(
sid int,
Prod2 char(2)
)

insert into @Prod2
select 101, 'T1' union all
select 101, 'T2' union all
select 195, 'T5' Union all
Select 201, 'T3' Union all
Select 202, 'T1'




select p1.Csid, p1.sid,
prod2 = case when count(*) > 1 then '4' else max(p2.Prod2) end
from @Prod1 p1 inner join @Prod2 p2
on p1.sid = p2.sid
group by p1.Csid, p1.sid

Output:

Csid sid prod2
-------------------- -------------------- -----
4103 101 4
4101 195 1
4104 201 2
4105 202 5

thanks for yr help in advance!

 

[SQLDenis]

you should have a lookup instead of this KLUDGE

select    p1.Csid, p1.sid, 
    prod2    = case when count(*) > 1 then '4' 
when max(p2.Prod2) = 'T5' then '1'
when max(p2.Prod2) = 'T3' then '2'
when max(p2.Prod2) = 'T1' then '5'else max(p2.Prod2) end
from    @Prod1 p1 inner join @Prod2 p2
    on    p1.sid    = p2.sid
group by p1.Csid, p1.sid

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[AlexCuse]

Well, it appears that you want the maximum numeric value of the right-most character of Prod2 (which should always be int?)

I think you made a mistake in your desired result set. Where does the 1 come from for 4101, shouldn't it be 5?

If I am reading correctly, this would be something like what you want. If not, please provide more information.

select    p1.Csid, p1.sid, 
    prod2    = case when count(*) > 1 then '4' else max(right(p2.Prod2, 1)) end
from    @Prod1 p1 inner join @Prod2 p2
    on    p1.sid    = p2.sid
group by p1.Csid, p1.sid

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Ah now it is clear Denis. Thanks.

jrprogr- for maintainability reasons I would recommend that you create a table that has your lookup values for t1, t4, bla bla bla.

That way if your rules were ever to change you only need to change a value in a table rather than every convoluted case statement that you have in various queries.

Anyway, using Denis' code you should be able to get it working, whatever it may be.

Ignorance of certain subjects is a great part of wisdom

 

[jrprogr]

Thanks Denis It works...
Thanks Alex..