SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case when
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/17/2013 :  08:45:46  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/17/2013 :  08:51:22  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 06/17/2013 08:51:49
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 06/17/2013 :  08:55:52  Show Profile  Reply with Quote
- 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

Azerbaijan
150 Posts

Posted - 06/17/2013 :  09:04:20  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
thank you

kmkmmm
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 06/17/2013 :  09:24:32  Show Profile  Reply with Quote
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

Edited by - bandi on 06/17/2013 09:25:11
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/17/2013 :  09:25:39  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
bandi


why LEFT(t3.phone2, 1) ??


kmkmmm
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 06/17/2013 :  09:29:53  Show Profile  Reply with Quote
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

Azerbaijan
150 Posts

Posted - 06/17/2013 :  09:37:55  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 06/17/2013 :  09:59:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000