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
 select sum problem

Author  Topic 

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-12-10 : 02:40:40
declare @test table
(id int not null identity(1,1),
phone int,
yanvar int,
dekabr int,
noyabr int,
oktyabr int
)

insert @test
(phone,yanvar,dekabr,noyabr,oktyabr)
select
11,0,-2,-3,0
union all
select
111,-1,0,-1,0
union all
select
222,-1,-2,0,-5
union all
select
333,-1,-2,-3,0
union all
select
444,-1,-2,-3,-4


select *from @test



id phone yanvar dekabr noyabr oktyabr
----------- ----------- ----------- ----------- ----------- -----------
1 11 0 -2 -3 0
2 111 -1 0 -1 0
3 222 -1 -2 0 -5
4 333 -1 -2 -3 0
5 444 -1 -2 -3 -4





it is the duty Monthly Phones

need to calculate what numbers eats past debts

for example

Number 11 no debts

Number 111 -1$ 1 month

Number 222 -1 + (-2) = -3 $ of debt 2 month

Number 333 -1 + (-2 ) +( -3 )=-6 $ of debt 3 month

Number 444 -1 + (-2) + (-3) + (-4) = -10 $ of debt 4 month



Results and fit in a table


phone 1 month 2 month 3 month 4 month

111 -1 0 0 0
222 0 -3 0 0
333 0 0 -6 0
444 0 0 0 -10

http://sql-az.tr.gg/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-10 : 02:54:45
[code]select id, phone,
[1 month] = case when yanvar <> 0
and dekabr = 0
then yanvar
else 0
end,
[2 month] = case when yanvar <> 0
and dekabr <> 0
and noyabr = 0
then yanvar + dekabr
else 0
end,
[3 month] = case when yanvar <> 0
and dekabr <> 0
and noyabr <> 0
and oktyabr = 0
then yanvar + dekabr + noyabr
else 0
end,
[4 month] = case when yanvar <> 0
and dekabr <> 0
and noyabr <> 0
and oktyabr <> 0
then yanvar + dekabr + noyabr + oktyabr
else 0
end
from @test
where yanvar <> 0[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-12-10 : 02:57:23
thank very much khtan

you is wonderful

http://sql-az.tr.gg/
Go to Top of Page
   

- Advertisement -