December 10, 2013 at 12:45 am
declare @test-2 table
(id int not null identity(1,1),
phone int,
yanvar int,
dekabr int,
noyabr int,
oktyabr int
)
insert @test-2
(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-2
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
December 10, 2013 at 6:19 am
;WITH cte (phone,[month],value) AS (
SELECT phone,
CASE WHEN dekabr = 0 THEN 1
WHEN noyabr = 0 THEN 2
WHEN oktyabr = 0 THEN 3
ELSE 4
END,
CASE WHEN dekabr = 0 THEN yanvar
WHEN noyabr = 0 THEN yanvar+dekabr
WHEN oktyabr = 0 THEN yanvar+dekabr+noyabr
ELSE yanvar+dekabr+noyabr+oktyabr
END
FROM @test-2
WHERE yanvar <> 0
)
SELECT phone,
SUM(CASE WHEN [month]=1 THEN value ELSE 0 END) AS [1 month],
SUM(CASE WHEN [month]=2 THEN value ELSE 0 END) AS [2 month],
SUM(CASE WHEN [month]=3 THEN value ELSE 0 END) AS [3 month],
SUM(CASE WHEN [month]=4 THEN value ELSE 0 END) AS [4 month]
FROM cte
GROUP BY phone
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply