Need help with a sql query

Question

Need help with a sql query

aars0716

Valued Member

Points: 63
More actions
March 29, 2022 at 1:45 am

#4007387

I've data in the following format:
I need to calculate backlog for each day. My desired result set should look like below:

the formula for "BKLG" for 3/16 is INF-OTF. However, from 3/17 onwards it is (INF+previous day BKLG)-OTF.
Would really appreciate if someone can give me ideas on how to achieve the results using T-SQL.
Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Welcome!

Good explanation of the problem! Very helpful. Since you're new, please read this article. It's just a user's manual of sorts for here. Forum Etiquette: How to post data/code on a forum to get the best help

That said, on to the problem. The trick is to use a windowing function, because it allows you to create running totals. And the "design" of the formula to solve your problem is this:

RunningTotal(INF) - RunningTotal(OTF) ... (that's not a real function... I'm just trying to explain it).

use tempdb;
go

/* create the table to hold our data */
CREATE TABLE #FlowData (
Inflow_date DATE PRIMARY KEY,
INF INT NOT NULL,
OTF INT NOT NULL);
GO

/* add enough records to see if the formulas work */
INSERT INTO #FlowData VALUES 
('3/16/2022', 1868, 573),
('3/17/2022', 1495, 1579),
('3/18/2022', 1205, 1280),
('3/19/2022', 863, 837);

/* write the actual query 
Backlog = running SUM(inflows) - running sum(outflows)
*/
SELECT Inflow_date
, INF
, OTF
/* rt = Running Total */
, rt_INF = SUM(inf) OVER (ORDER BY Inflow_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
, rt_OTF = SUM(otf) OVER (ORDER BY Inflow_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
, Backlog = SUM(inf) OVER (ORDER BY Inflow_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 
SUM(otf) OVER (ORDER BY Inflow_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM #FlowData;

you might want to look up Windowing Functions, because that's key to solving the problem. (Kathi Kellenberger wrote a really good book on it, but there are articles out on the internet too)

Hope this helps!

Pieter

aars0716 Valued Member Points: 63 More actions · Answer 2

This is excellent, thank you so much Pieter.

It works like a charm. Thank you once again.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Now to understand it!

Maybe this article will help:

Introduction to T-SQL Window Functions - Simple Talk (red-gate.com)