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
 probelem sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/18/2013 :  05:25:18  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)
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)

Singapore
17626 Posts

Posted - 06/18/2013 :  05:30:11  Show Profile  Reply with Quote
use CASE statement

SUM(CASE WHEN f2 < 0 then f2 else 0 end)



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  05:31:45  Show Profile  Reply with Quote

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 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/18/2013 :  05:33:09  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
thank you khtan

kmkmmm
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/18/2013 :  05:35:36  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
thank you
visakh16

kmkmmm
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  05:44:16  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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