probelem sum - SQL Server Forums

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

probelem sum

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_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

Phone2 f2 f1 abser_ode avansid_ode
------ ------- ---- - ------------------- -----------------
A -6.30 -5.07 1.450000000 5.580000000
A 1.00 9.70 0.560000000 0.000000000
A -9.00 0.00 0.000000000 0.000000000
B 1.10 -8.90 0.000000000 0.000000000
B -0.90 9.70 0.000000000 0.000000000
B 0.00 -5.70 0.000000000 0.000000000

How can I sum f2 but interpretation f2 <0

for example here

phone2 sumf2 sumf1

A -15.3 -5.07

B -0.9 -14.6

here

A
sum f2=-6.30+(-9.0)=-15.3
sumf1= -5.07

B
sumf2= 0+(-0.9)+0=-0.9
sumf1=-8.9+0+( -5.70 )=-14.6

kmkmmm

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-18 : 05:30:11

use CASE statement


SUM(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_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
GROUP 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-18 : 05:33:09

thank you khtan

kmkmmm

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-06-18 : 05:35:36

thank you
visakh16

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 05:44:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources