November 23, 2015 at 3:58 pm
I have a two columns id and col1. And I have a numeric variable called X with a value of 15000.
create table #t
(
id int,
col1 decimal(18,2)
)
go
insert into #t (id,col1)
values
(1, 5000),
(2, 1000),
(3, 10000),
(4, 12000),
(5, 300),
(6, 35000)
go
declare @X as decimal(18,2) = 15000
select
id,
col1
-- col2
from #t
drop table #t
-- I want to subtract @X and col1. But my variable @X must be reduced for each value in col1 for each next row until it reaches zero.
-- OUTPUT:
-- id col1 col2
--@X at starting point is 15000
-- 1 5000.00 0 --@X IS 10000 = 15000 - 5000(col1)
-- 2 1000.00 0 --@X IS 9000 = 10000 - 1000
-- 3 10000.00 1000.00 --@X IS 1000 = 9000 - 10000
-- 4 12000.00 12000.00
-- 5 300.00 300.00
-- 6 35000.00 35000.00
--in col2 i just put zero where col1 is substract from @X and continue for every subsequent order.
-- in 3 row value is 1000 becouse @X is that big (1000 left from col1)
November 23, 2015 at 5:48 pm
It seems you need something like "Running Totals".
_____________
Code for TallyGenerator
November 23, 2015 at 8:29 pm
This should give you what you're looking for...
-- your original test data --
IF OBJECT_ID('tempdb..#t','U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t (id INT NOT NULL ,col1 DECIMAL(18,2) NOT NULL );
GO
INSERTINTO #t
(id,col1)
VALUES
(1,5000),
(2,1000),
(3,10000),
(4,12000),
(5,300),
(6,35000);
GO
-- the actual solution --
-- put your DATA INTO a temp TABLE so that you can do a "quirky update"
IF OBJECT_ID('tempdb..#temp','U') IS NOT NULL
DROP TABLE #temp;
SELECT
id,
col1,
CAST(NULL AS DECIMAL(18,2)) AS col2
INTO
#temp
FROM
#t;
-- the temp table needs to have a culetred index
-- to control the order of the update
ALTER TABLE #t ADD PRIMARY KEY CLUSTERED (id);
DECLARE
@X AS DECIMAL(18,2) = 15000,
@c2 DECIMAL(18,2) = 0;
-- the "quirky update"
UPDATE t SET
@c2 = t.col2 = CASE WHEN t.col1 < @X THEN 0 ELSE t.col1 - @X END,
@X = CASE WHEN @x > t.col1 THEN @x - t.col1 ELSE 0 END
FROM #temp t;
-- view the results...
SELECT * FROM #temp;
The results
id col1col2
----------- ----------------------
1 5000.000.00
2 1000.000.00
3 10000.001000.00
4 12000.0012000.00
5 300.00300.00
6 35000.0035000.00
November 24, 2015 at 12:12 am
Quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#t') IS NOT NULL DROP TABLE #t;
create table #t
(
id int,
col1 decimal(18,2)
)
go
insert into #t (id,col1)
values
(1, 5000),
(2, 1000),
(3, 10000),
(4, 12000),
(5, 300),
(6, 35000)
declare @X as decimal(18,2) = 15000
SELECT
T.id
,T.col1
,CASE
WHEN SUM(T.col1) OVER
(
ORDER BY T.id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) <= @X THEN
@X - SUM(T.col1) OVER
(
ORDER BY T.id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)
ELSE T.col1
END AS col2
FROM #t T;
drop table #t;
Results
id col1 col2
--- --------- --------
1 5000.00 10000.00
2 1000.00 9000.00
3 10000.00 10000.00
4 12000.00 12000.00
5 300.00 300.00
6 35000.00 35000.00
November 24, 2015 at 3:55 am
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply