SQL get all columns within a specific row who's sum is bigger than given value

Question

I'm working with an existing Table (cannot modify it) in SQL 2014

Simplified (example) table structure:

CREATE TABLE dbo.T1
(
    sYear int NOT NULL,
    m1 decimal(18, 2) NOT NULL,
    m2 decimal(18, 2) NOT NULL,
    m3 decimal(18, 2) NOT NULL,
    m4 decimal(18, 2) NOT NULL,
    m5 decimal(18, 2) NOT NULL,
    m6 decimal(18, 2) NOT NULL,
    m7 decimal(18, 2) NOT NULL,
    m8 decimal(18, 2) NOT NULL,
    m9 decimal(18, 2) NOT NULL,
    m10 decimal(18, 2) NOT NULL,
    m11 decimal(18, 2) NOT NULL,
    m12 decimal(18, 2) NOT NULL
)

The table represents a "monthly budget" or what's remaining thereof, where m1 to m12 represent the months

So Sample Data for the row would look something like this:

Insert into dbo.T1 
Values( 2017,1000,1000,1000,1000,1000,1000,1000,1000,1000,1000,1000,1000)

Now my task is this, if a user wants to spend an amount in a specific month, I need to remove the spend amount from that specific months remaining budget, providing it has sufficient funds. If not I need to remove form subsequent months until the full value of the spend has been removed.

I've tried using Case statements and summing columns, but it became ridiculously long....too many cases. So what I ended up doing is to create a temp table where each column from the original table becomes a row & then summing those row vlaues until I've reached the Spend Amount.. (used this answer as an example of how to achieve this:Select running total until specific SUM is reached)

Still not convinced that's the best way for my scenario, so does anyone know of a way to sum across columns of a specific row or of any other/ better way of achieving this?

(1) Tag your question with the database you are using. (2) Provide sample data and desired results. (3) Why are you putting values across columns, instead of in multiple rows? — Gordon Linoff, Apr 24 '18 at 17:30
If you cannot change the structure of the table, I think the solution should be implemented in your application side, rather than in SQL. Use SQL to retrieve and update values only. Are you using Sybase? — The Impaler, Apr 24 '18 at 17:52
Apologies, just realised I forgot to add which db...It's SQL 2014 ( Edited the post now) — Marsh, Apr 24 '18 at 18:45
@Gordon Linoff Thanks for response (1) Apologies, just realised I forgot to add which db...It's SQL 2014...Edited the original post (2) Tried to explain is a best I could & did provide sample data, the result would simply be the subtraction of the value from the provided columns (3) I have to work with an existing db, so cannot modify the structure — Marsh, Apr 24 '18 at 18:52
If this question gets 100 comments all saying "OMG fix your schema what the heck!" would that be compelling enough for the folks that own the database to fix the schema? Because I could totally comment that if it would help OP. — JNevill, Apr 24 '18 at 19:06
Can you create views and work on them? If so, you might wrap that table in a view and put some trigger on it carrying the carries from column to column. — sticky bit, Apr 25 '18 at 00:07
@JNevill ...hahaha, I totally agree, but this is a very old db , & I would have to cvompletely redesign the entire db to fix all the issues....The client don't have the time, nor wants to spend the money doing it, so I'm stuck with it. — Marsh, Apr 25 '18 at 09:19

score 0 · Accepted Answer · answered Apr 25 '18 at 09:28

Ok so this is my solution...It works as I said in the question, but I'm not convinced it is the most efficient way of doing this

DECLARE @Spend DECIMAL(18,2) = 1200.70


DECLARE @Extract TABLE (rowId int primary key, Mnth int, Amount Decimal )

insert INTO @Extract SELECT  1, 9 , m9 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 2, 10 , m10 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 3, 11 , m11 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 4, 12 , m12 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 5, 1 , m1 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 6, 2 , m2 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 7, 3 , m3 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 8, 4 , m4 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 9, 5 , m5 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 10, 6 , m6 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 11, 7 , m7 FROM T1 WHERE sYear=2017
insert INTO @Extract SELECT 12, 8 , m8 FROM T1 WHERE sYear=2017

Declare @sMonth int = 10, @MaxRow int, @MinRow int

Declare @Res TABLE (rowid int, mth int, mName NVARCHAR(3), v decimal,  s decimal) 
INSERT INTO @Res
SELECT
    rowid, Mnth,'m'+ CAST(Mnth AS NVARCHAR(3)), Amount, 
    SUM(Amount) OVER (ORDER BY rowId RANGE UNBOUNDED PRECEDING)
  FROM @Extract --where Mnth >= @sMonth --OR Mnth< @sMonth





-- This one can be used if we use remaining budget from previous months !!-----------------------------

SELECT TOP(1) @MaxRow = rowid
FROM @Res WHERE (s >= @Spend) 
ORDER BY rowid , s Desc;


SELECT rowid, mth,mName, v, s
FROM @Res WHERE (rowid <= @MaxRow) 
ORDER BY rowid; 

---------------------------------------------------------------------------------------------------------------


-- This one only uses Budget from the specified month onward, regrdless if there is still money remaining in the Prev month-----------------

-- SELECT @MinRow = rowid
-- FROM @Extract WHERE (Mnth = @sMonth) 

-- SELECT TOP(1) @MaxRow= rowid
-- FROM @Res WHERE (s >= @Spend) 
-- ORDER BY rowid,s  Desc;


-- SELECT rowid, mth, v, s
-- FROM @Res WHERE (rowid >= @MinRow )AND (rowid <= @MaxRow)
-- ORDER BY rowid; 

--------------------------------------------------------------------------------------------------------------------------------------------------------




-- Loop & Update ------------------------------------------------------------------------------------------------------------
Declare @rid INT = (SELECT MIN(rowid) FROM @Res), @Diff decimal(18,2), @sqlX   NVARCHAR(1000), @colName AS nVARCHAR(3);

while @rid is not null
begin

   SELECT @colName = mName , @Diff = CASE WHEN s <= @Spend THEN 0 ELSE (s- @Spend ) end FROM @Res where rowid = @rid

   -- PRINT @colName
   -- PRINT @Diff

   SELECT @sqlX = 'Update T1 SET T1.[' + @colName + '] = ' + CAST(@Diff AS NVARCHAR(max)) + ' where sYear = 2017 '



  -- PRINT @sqlX

 EXEC sp_executesql @sqlX


    select @rid = min( rowid ) FROM @Res where rowid > @rid AND (rowid <= @MaxRow) 
end



SELECT * FROM T1

Forgot to mention ...They use September as the start month, that's why I had to order the months the way they are... — Marsh, Apr 25 '18 at 10:02

SQL get all columns within a specific row who's sum is bigger than given value

1 Answers1