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.
Author |
Topic |
pascal_jimi
Posting Yak Master
167 Posts |
Posted - 2013-06-18 : 05:25:18
|
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)insert into @table3 values(666666)-- declare @table2 table(phone1 int,debt money) insert into @table2 values(111111,1.23)insert into @table2 values(222222,9.7)insert into @table2 values(555555,9.7)insert into @table2 values(666666,-5.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)insert into @t242 values(222222,0.1)declare @t1 table(phone int,sadbr money) insert into @t1 values(111111,-1.10)insert into @t1 values(111111,1.10)insert into @t1 values(222222,1.10)insert into @t1 values(444444,1.10)insert into @t1 values(555555,-0.9)-- declare @absher table(telefon varchar(10),odenis varchar(10)) insert into @absher values('00111111','000000089')insert into @absher values('00111111','000000056')insert into @absher values('00222222','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 CASE WHEN t3.phone2 BETWEEN 111111 AND 333333 THEN 'A' WHEN phone2 BETWEEN 333333 AND 666666 THEN 'B' else 'nomre' END 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_odefrom @table3 as t3left join (select ph,SUM(qebzbr) as qebzbr FROM @t242 group by ph) as t242 ON t242.ph= t3.phone2left join (select phone,SUM(sadbr) as sadbr FROM @t1 group by phone) as tt1 ON tt1.phone= t3.phone2left join (select phone1,SUM(debt) as debt FROM @table2 group by phone1) as t2 ON t2.phone1 = t3.phone2left join (select phone,SUM(old_debt) as old_debt from @table1 group by phone) as t1 ON t1.phone = t3.phone2left 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.phone2left 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.phone2Phone2 f2 f1 abser_ode avansid_ode------ ------- ---- - ------------------- -----------------A -6.30 -5.07 1.450000000 5.580000000A 1.00 9.70 0.560000000 0.000000000A -9.00 0.00 0.000000000 0.000000000B 1.10 -8.90 0.000000000 0.000000000B -0.90 9.70 0.000000000 0.000000000B 0.00 -5.70 0.000000000 0.000000000How can I sum f2 but interpretation f2 <0for example here phone2 sumf2 sumf1 A -15.3 -5.07 B -0.9 -14.6 hereA sum f2=-6.30+(-9.0)=-15.3sumf1= -5.07B sumf2= 0+(-0.9)+0=-0.9sumf1=-8.9+0+( -5.70 )=-14.6kmkmmm |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-18 : 05:30:11
|
use CASE statementSUM(CASE WHEN f2 < 0 then f2 else 0 end) KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 05:31:45
|
[code]select CASE WHEN t3.phone2 BETWEEN 111111 AND 333333 THEN 'A' WHEN phone2 BETWEEN 333333 AND 666666 THEN 'B' else 'nomre' END Phone2,SUM( CASE WHEN ISNULL(tt1.sadbr,0.0)-ISNULL(t242.qebzbr,0.0) <0 THEN ISNULL(tt1.sadbr,0.0)-ISNULL(t242.qebzbr,0.0) ELSE 0 END) as f2,SUM( CASE WHEN ISNULL(t2.debt,0.0)-isnull(t1.old_debt,0.0) <0 THEN ISNULL(t2.debt,0.0)-isnull(t1.old_debt,0.0) ELSE 0 END) as f1,SUM(ISNULL(p.mny,0)) as abser_ode,SUM(ISNULL(d.av_sad,0)) as avansid_odefrom @table3 as t3left join (select ph,SUM(qebzbr) as qebzbr FROM @t242 group by ph) as t242 ON t242.ph= t3.phone2left join (select phone,SUM(sadbr) as sadbr FROM @t1 group by phone) as tt1 ON tt1.phone= t3.phone2left join (select phone1,SUM(debt) as debt FROM @table2 group by phone1) as t2 ON t2.phone1 = t3.phone2left join (select phone,SUM(old_debt) as old_debt from @table1 group by phone) as t1 ON t1.phone = t3.phone2left 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.phone2left 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.phone2GROUP BY CASE WHEN t3.phone2 BETWEEN 111111 AND 333333 THEN 'A' WHEN phone2 BETWEEN 333333 AND 666666 THEN 'B' else 'nomre' END [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pascal_jimi
Posting Yak Master
167 Posts |
Posted - 2013-06-18 : 05:33:09
|
thank you khtankmkmmm |
|
|
pascal_jimi
Posting Yak Master
167 Posts |
Posted - 2013-06-18 : 05:35:36
|
thank you visakh16kmkmmm |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 05:44:16
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|