October 17, 2014 at 8:31 am
I have an procedure that uses a cursor to decrement a total in one table based on value in another. I am trying to rewrite this to be set based but having some issues getting the correct values.
one table will have a total amount available and the other table will have line items for the amount to decrement (think amount left on gift card). If the total amount remaining is less than the line item amount then line item amount should be set to total amount available with total amount set to zero.
this last part is where I am having trouble.
here is example table
CREATE TABLE qamt(id INT, amt INT);
CREATE TABLE qtot(id INT, tot INT);
INSERT INTO qamt
VALUES(1,10),
(1,20),
(1,30),
(2,90),
(2,10),
(3,150),
(4,20),
(4,40),
(4,50),
(4,70);
INSERT INTO qtot
VALUES(1,100),
(2,100),
(3,100),
(4,100);
this is what I have so far
WITH cte AS(SELECT a.id,a.amt AS amt,b.tot
FROM qamt a
INNER JOIN qtot b
ON a.id = b.id)
SELECT id,
CASE WHEN tot < amt THEN tot ELSE amt END AS amt,
CASE WHEN tot-SUM(amt) OVER(PARTITION BY id ORDER BY id ROWS unbounded preceding) >= 0
THEN tot-SUM(amt) OVER(PARTITION BY id ORDER BY id ROWS unbounded preceding)
ELSE 0 END AS tot
FROM cte;
and this is what I want
idamttot
11090
12070
13040
29010
2100
31000 --only 100 could be applied to amt
42080
44040
4400 --only 40 could be applied to amt
4700
base tables
qtot
id tot
1 40
2 0
3 0
4 0
qamt
id amt
110
120
130
290
210
3100
420
440
440
470
In the end I would want to update the qamt and qtot tables. I basically want to accomplish a quirky update[/url] across multiple tables. Any help would be much appreciated. Thnaks
October 18, 2014 at 9:52 am
Quick window function solution, should be self explanatory
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.qamt') IS NOT NULL DROP TABLE dbo.qamt;
CREATE TABLE qamt(id INT, amt INT);
IF OBJECT_ID('dbo.qtot') IS NOT NULL DROP TABLE dbo.qtot;
CREATE TABLE dbo.qtot(id INT, tot INT);
INSERT INTO dbo.qamt
VALUES(1,10),
(1,20),
(1,30),
(2,90),
(2,10),
(3,150),
(4,20),
(4,40),
(4,50),
(4,70);
INSERT INTO dbo.qtot
VALUES(1,100),
(2,100),
(3,100),
(4,100);
;WITH ORDERED_TRANSACTION_SET AS
(
SELECT
QA.id
,ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS QA_RID
,QA.amt
FROM dbo.qamt QA
)
,RUNNING_TOTAL_SET AS
(
SELECT
QT.id
,QT.tot
,OTS.amt
,QT.tot - SUM(OTS.amt) OVER
(
PARTITION BY QT.id
ORDER BY OTS.QA_RID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS AC_BALANCE
FROM dbo.qtot QT
OUTER APPLY ORDERED_TRANSACTION_SET OTS
WHERE QT.id = OTS.id
)
SELECT
RTS.id
,RTS.tot
,RTS.amt
,CASE
WHEN RTS.AC_BALANCE >= 0 THEN RTS.AC_BALANCE
ELSE 0
END AS RTS_BALANCE
,RTS.AC_BALANCE
FROM RUNNING_TOTAL_SET RTS;
Results
id tot amt RTS_BALANCE AC_BALANCE
----------- ----------- ----------- ----------- -----------
1 100 10 90 90
1 100 20 70 70
1 100 30 40 40
2 100 90 10 10
2 100 10 0 0
3 100 150 0 -50
4 100 20 80 80
4 100 40 40 40
4 100 50 0 -10
4 100 70 0 -80
October 20, 2014 at 10:30 am
October 20, 2014 at 10:36 am
Eirikur Eiriksson (10/18/2014)
Quick window function solution, should be self explanatory😎
Eirikur, do you ever do anything slowly? 😛
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 20, 2014 at 10:50 am
Phil Parkin (10/20/2014)
Eirikur Eiriksson (10/18/2014)
Quick window function solution, should be self explanatory😎
Eirikur, do you ever do anything slowly? 😛
According to the better half, yes:-P
😎
June 24, 2016 at 3:52 am
Dear Friend,
Please give me same sql query for sql server 2005
Regards
V.S.Satheesh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply