Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case when

Author  Topic 

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-17 : 08:45:46
declare @table3 table(phone2 int) --
insert into @table3 values(111111)
insert into @table3 values(222222)
insert into @table3 values(333333)
insert into @table3 values(444444)
insert into @table3 values(555555)
--
declare @table2 table(phone1 int,debt money)
insert into @table2 values(111111,1.23)
insert into @table2 values(222222,9.7)
declare @table1 table(phone int,old_debt money)
insert into @table1 values(111111,6.3)
insert into @table1 values(444444,8.9)
declare @t242 table(ph int,qebzbr money)
insert into @t242 values(111111,6.3)
insert into @t242 values(333333,9.0)
declare @t1 table(phone int,sadbr money)
insert into @t1 values(111111,-1.10)

--
declare @absher table(telefon varchar(10),odenis varchar(10))
insert into @absher values('00111111','000000089')
insert into @absher values('00111111','000000056')

----------
declare @avans table(tlf varchar(10),Test varchar(10))
insert into @avans values('00111111','000000009')
insert into @avans values('00111111','000000523')
insert into @avans values('00111111','000000026')

--------------------------------------------------------------

select *from (select t3.phone2,
ISNULL(tt1.sadbr,0.0)-ISNULL(t242.qebzbr,0.0) as f2
,ISNULL(t2.debt,0.0)-isnull(t1.old_debt,0.0) as f1
,ISNULL(p.mny,0) as abser_ode
,ISNULL(d.av_sad,0) as avansid_ode
from @table3 as t3
left join (select ph,SUM(qebzbr) as qebzbr FROM @t242 group by ph) as t242 ON t242.ph= t3.phone2
left join (select phone,SUM(sadbr) as sadbr FROM @t1 group by phone) as tt1 ON tt1.phone= t3.phone2
left join (select phone1,SUM(debt) as debt FROM @table2 group by phone1) as t2 ON t2.phone1 = t3.phone2
left join (select phone,SUM(old_debt) as old_debt from @table1 group by phone) as t1 ON t1.phone = t3.phone2
left join (select convert(int,telefon) as tf,sum(convert(money,odenis)/100.0) as mny
from @absher group by convert(int,telefon)) p ON p.tf = t3.phone2
left join (select convert(int,tlf) as tlf ,sum(convert(money,Test)/100.0) as av_sad
from @avans group by convert(int,tlf))as d on d.tlf=t3.phone2
) uuu


result query

-----------
phon2 f2 f1 absher_ode avans_ode
111111 -7,40 -5,07 1.450000000 5.580000000
222222 0,00 9,70 0.000000000 0.000000000
333333 -9,00 0,00 0.000000000 0.000000000
444444 0,00 -8,90 0.000000000 0.000000000
555555 0,00 0,00 0.000000000 0.000000000




how can I get


when phone2>=1 and phone2<=2 then 'A'

phone2>=3 and phone2<=5 then 'B'

how can I get result


phon2 f2 f1 absher_ode avans_ode
A -7,40 -5,07 1.450000000 5.580000000
A 0,00 9,70 0.000000000 0.000000000
B -9,00 0,00 0.000000000 0.000000000
B 0,00 -8,90 0.000000000 0.000000000
B 0,00 0,00 0.000000000 0.000000000

kmkmmm

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-17 : 08:51:22
SELECT
CASE WHEN Phone2 between 1 and 2 then 'A'
WHEN phone2 between 3 and 5 then 'B'
END as Phone2

,F2
,F1
,Absher_Ode
,Avans_Ode
FROM (
........
)

Cheers
MIK
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 08:55:52
- I think this one
select *
from (select CASE WHEN LEFT(t3.phone2, 1) BETWEEN 1 AND 2 THEN 'A'
WHEN LEFT(t3.phone2, 1) BETWEEN 3 AND 5 THEN 'B' END Phone2,
ISNULL(tt1.sadbr,0.0)-ISNULL(t242.qebzbr,0.0) as f2



--
Chandu
Go to Top of Page

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-17 : 09:04:20
thank you

kmkmmm
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 09:24:32
quote:
Originally posted by pascal_jimi

thank you

kmkmmm


Which one worked out?
My solution is dependent on the first digit in the phone2 whereas MIK's solution is for checking entire number in the Phone2 column


--
Chandu
Go to Top of Page

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-17 : 09:25:39
bandi


why LEFT(t3.phone2, 1) ??


kmkmmm
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 09:29:53
quote:
Originally posted by pascal_jimi

bandi
why LEFT(t3.phone2, 1) ??
Kmkmmm


You had Phone2 values as 111111, 222222 ....
By looking at your required output, I checked for first digit in the Phone2 column

LEFT ( Phone2, 1) means first character(digit) from the column Phone2

--
Chandu
Go to Top of Page

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-17 : 09:37:55
ok thank you

but i wrote
select CASE WHEN t3.phone2 BETWEEN 111111 AND 222222 THEN 'A'
WHEN phone2 BETWEEN 333333 AND 555555 THEN 'B' else 'nomre' END Phone2,

I need a complete full phone2

kmkmmm
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 09:59:30
quote:
Originally posted by pascal_jimi

ok thank you

but i wrote
select CASE WHEN t3.phone2 BETWEEN 111111 AND 222222 THEN 'A'
WHEN phone2 BETWEEN 333333 AND 555555 THEN 'B' else 'nomre' END Phone2,

I need a complete full phone2

kmkmmm


ok... is that Phone2 values had length of 6 digits only?

--
Chandu
Go to Top of Page
   

- Advertisement -