How to get running balance for missing week

Question

Post reply

How to get running balance for missing week

adhikari707

SSChasing Mays

Points: 639
More actions
December 4, 2015 at 3:30 pm

#399426

I have situation where if there is no transaction during a week then show the previous week balance.
Below is the situation what i am taking about
suppose i have date table that have weekenddates
Product table with productID , Location, Weekenddate , TransVol,RunningVol
Product A with Location 1 is having transaction for Weekenddate 11/01/2015,11/08/2015 ,11/22/2015
but is not having any transaction for weekenddate 11/15/2015 and 11/29/2015 so in that case just carry forward the last week running balance
with 0 TransVol.
Is this possible without using loops ?
Any help would be greatly appreciated
Date table
***********************
WeekendDate
-----------
11/01/2015
11/08/2015
11/15/2015
11/22/2015
11/29/2015
Product Table
---------------------
Product Location WeekendDate TransVol RunningVol
-------------------------------------------------------------------------
A 1 11/01/2015 50 50
A 1 11/08/2015 75 125
A 1 11/22/2015 -25 100
A 2 11/22/2015 25 25
B 1 11/15/2015 25 25
End Result
**********
Product Location WeekendDate TransVol RunningVol
--------------------------------------------------------------------------
A 1 11/01/2015 50 50
A 1 11/08/2015 75 125
A 1 11/15/2015 0 125
A 1 11/22/2015 -25 100
A 1 11/29/2015 0 100
A 2 11/22/2015 25 25
A 2 11/29/2015 0 25
B 1 11/15/2015 25 25
B 1 11/22/2015 0 25
B 1 11/29/2015 0 25

Viewing 11 posts - 1 through 11 (of 11 total)

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

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 1

try this...

IF OBJECT_ID('tempdb..#Dates', 'U') IS NOT NULL

DROP TABLE #Dates;

CREATE TABLE #Dates (

WeekendDate DATE

);

INSERT #Dates (WeekendDate) VALUES

('11/01/2015'),

('11/08/2015'),

('11/15/2015'),

('11/22/2015'),

('11/29/2015');

IF OBJECT_ID('tempdb..#Product', 'U') IS NOT NULL

DROP TABLE #Product;

CREATE TABLE #Product (

Product CHAR(1),

Location TINYINT,

WeekendDate DATE,

TransVol INT,

RunningVol INT

);

INSERT #Product (Product,Location,WeekendDate,TransVol,RunningVol) VALUES

('A', 1, '11/01/2015', 50, 50),

('A', 1, '11/08/2015', 75, 125),

('A', 1, '11/22/2015', -25, 100),

('A', 2, '11/22/2015', 25, 25),

('B', 1, '11/15/2015', 25, 25);

--SELECT * FROM #Dates d;

--SELECT * FROM #Product p;

WITH

Prod AS (

SELECT

p.Product,

MIN(p.WeekendDate) AS BegDate

FROM

#Product p

GROUP BY

p.Product

),

ProdDates AS (

SELECT

d.WeekendDate,

p.Product

FROM

Prod p

JOIN #Dates d

ON p.BegDate <= d.WeekendDate

)

SELECT

pd.WeekendDate,

pd.Product,

p.Location,

COALESCE(p.TransVol, 0) AS TransVol,

p.RunningVol,

SUM(p.TransVol) OVER (PARTITION BY pd.Product ORDER BY pd.WeekendDate ROWS UNBOUNDED PRECEDING) AS NewRunningTotal

FROM

ProdDates pd

JOIN #Dates d

ON pd.WeekendDate = d.WeekendDate

LEFT JOIN #Product p

ON pd.Product = p.Product

AND pd.WeekendDate = p.WeekendDate

;

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 2

here's a different option...

IF OBJECT_ID('tempdb..#Dates', 'U') IS NOT NULL

DROP TABLE #Dates;

CREATE TABLE #Dates (

WeekendDate DATE

);

INSERT #Dates (WeekendDate) VALUES

('11/01/2015'),

('11/08/2015'),

('11/15/2015'),

('11/22/2015'),

('11/29/2015');

IF OBJECT_ID('tempdb..#Product', 'U') IS NOT NULL

DROP TABLE #Product;

CREATE TABLE #Product (

Product CHAR(1),

Location TINYINT,

WeekendDate DATE,

TransVol INT,

RunningVol INT

);

INSERT #Product (Product,Location,WeekendDate,TransVol,RunningVol) VALUES

('A', 1, '11/01/2015', 50, 50),

('A', 1, '11/08/2015', 75, 125),

('A', 1, '11/22/2015', -25, 100),

('A', 2, '11/22/2015', 25, 25),

('B', 1, '11/15/2015', 25, 25);

--SELECT * FROM #Dates d;

--SELECT * FROM #Product p;

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

IF OBJECT_ID('tempdb..#QuirkySmear', 'U') IS NOT NULL

DROP TABLE #QuirkySmear;

WITH

Prod AS (

SELECT

p.Product,

MIN(p.WeekendDate) AS BegDate

FROM

#Product p

GROUP BY

p.Product

),

ProdDates AS (

SELECT

d.WeekendDate,

p.Product

FROM

Prod p

JOIN #Dates d

ON p.BegDate <= d.WeekendDate

)

SELECT

pd.WeekendDate,

pd.Product,

p.Location,

COALESCE(p.TransVol, 0) AS TransVol,

p.RunningVol

INTO #QuirkySmear

FROM

ProdDates pd

JOIN #Dates d

ON pd.WeekendDate = d.WeekendDate

LEFT JOIN #Product p

ON pd.Product = p.Product

AND pd.WeekendDate = p.WeekendDate

;

CREATE UNIQUE CLUSTERED INDEX ix_QM ON #QuirkySmear (Product, WeekendDate, Location);

DECLARE @RT INT;

UPDATE qs SET

@RT = qs.RunningVol = COALESCE(qs.RunningVol, @RT)

FROM

#QuirkySmear qs

OPTION(MAXDOP 1);

SELECT * FROM #QuirkySmear qs;

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Jason A. Long (12/4/2015)
here's a different option...
IF OBJECT_ID('tempdb..#Dates', 'U') IS NOT NULL
DROP TABLE #Dates;
CREATE TABLE #Dates (
WeekendDate DATE
);
INSERT #Dates (WeekendDate) VALUES
('11/01/2015'),
('11/08/2015'),
('11/15/2015'),
('11/22/2015'),
('11/29/2015');
IF OBJECT_ID('tempdb..#Product', 'U') IS NOT NULL
DROP TABLE #Product;
CREATE TABLE #Product (
Product CHAR(1),
Location TINYINT,
WeekendDate DATE,
TransVol INT,
RunningVol INT
);
INSERT #Product (Product,Location,WeekendDate,TransVol,RunningVol) VALUES
('A', 1, '11/01/2015', 50, 50),
('A', 1, '11/08/2015', 75, 125),
('A', 1, '11/22/2015', -25, 100),
('A', 2, '11/22/2015', 25, 25),
('B', 1, '11/15/2015', 25, 25);
--SELECT * FROM #Dates d;
--SELECT * FROM #Product p;
------------------------------------------------------
IF OBJECT_ID('tempdb..#QuirkySmear', 'U') IS NOT NULL
DROP TABLE #QuirkySmear;
WITH
Prod AS (
SELECT
p.Product,
MIN(p.WeekendDate) AS BegDate
FROM
#Product p
GROUP BY
p.Product
),
ProdDates AS (
SELECT
d.WeekendDate,
p.Product
FROM
Prod p
JOIN #Dates d
ON p.BegDate <= d.WeekendDate
)
SELECT
pd.WeekendDate,
pd.Product,
p.Location,
COALESCE(p.TransVol, 0) AS TransVol,
p.RunningVol
INTO #QuirkySmear
FROM
ProdDates pd
JOIN #Dates d
ON pd.WeekendDate = d.WeekendDate
LEFT JOIN #Product p
ON pd.Product = p.Product
AND pd.WeekendDate = p.WeekendDate
;
CREATE UNIQUE CLUSTERED INDEX ix_QM ON #QuirkySmear (Product, WeekendDate, Location);
DECLARE @RT INT;
UPDATE qs SET
@RT = qs.RunningVol = COALESCE(qs.RunningVol, @RT)
FROM
#QuirkySmear qs
OPTION(MAXDOP 1);
SELECT * FROM #QuirkySmear qs;

Heh - I wonder which one's faster. 😉 To be safe, add this to the update:

WITH (INDEX (0), TABLOCK)

The INDEX(0) tells it to use the clustered index. The TABLOCK prevents it from doing lock escalation to get to the point of TABLOCK; it just starts out with one.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

adhikari707 SSChasing Mays Points: 639 More actions · Answer 4

Thanks Jason , i will try this and let you know about the results

🙂

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 5

Ed Wagner (12/4/2015)
Heh - I wonder which one's faster. 😉 To be safe, add this to the update:
WITH (INDEX (0), TABLOCK)
The INDEX(0) tells it to use the clustered index. The TABLOCK prevents it from doing lock escalation to get to the point of TABLOCK; it just starts out with one.

Hard to say which is faster without a larger sample set... With the current set, they are both within a couple ms of each other, without any consistency as to which one comes in first.

I agree with TABLOCKX suggestion on the "quirky update" method.

Not so sure about the INDEX(0) hint though... I had it in my head that INDEX(1) that tells the optimizer to use the clustered index...

So I did a little digging and found a relevant article by Iztik Ben-Gan (Ordered UPDATE and Set-Based Solutions to Running Aggregates)

BTW, as an aside, the hint index = 0 doesn’t instruct the optimizer to do something in clustered index order, rather, just do a table scan (or clustered index scan with no order guaranteed). To instruct the optimizer to perform an activity in clustered index order you would normally use index = 1.

Of course, the point is probably mute, considering that the whole point of the article is to say, "don't use the quirky update method at all".

Considering that (in the absence of a more conclusive test bench) there isn't a clear performance winner and the warning offered by the article, I'd probably just stick to the 1st option.

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 6

adhikari707 (12/4/2015)
Thanks Jason , i will try this and let you know about the results
🙂

No problem. Keep us posted. 🙂

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

Both Index(0) and Index(1) tell SQL Server to use the clustered index, if there is one.

Index(0) will force a scan and is the slower of the two options. Index(1) allows a seek and range scan, which is the faster of the two options, even if the whole table does need to be scanned.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

adhikari707 SSChasing Mays Points: 639 More actions · Answer 8

Hi Jason

i tried it but its missing one row for product A Location 1 for 2015-11-29

And for location for missing week its showing null , how to display location in place of NULL ?

In this scenario Product,Location and WeekendDate make up an unique transaction.

2015-11-01A15050

2015-11-08A175125

2015-11-15ANULL0125

2015-11-22A1-25100

2015-11-22A22525

2015-11-29ANULL025

2015-11-15B12525

2015-11-22BNULL025

2015-11-29BNULL025

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 9

Sorry... Missed the requirement to to include Location in the partitioning.

Try the following...

IF OBJECT_ID('tempdb..#Dates', 'U') IS NOT NULL

DROP TABLE #Dates;

CREATE TABLE #Dates (

WeekendDate DATE

);

INSERT #Dates (WeekendDate) VALUES

('11/01/2015'),

('11/08/2015'),

('11/15/2015'),

('11/22/2015'),

('11/29/2015');

IF OBJECT_ID('tempdb..#Product', 'U') IS NOT NULL

DROP TABLE #Product;

CREATE TABLE #Product (

Product CHAR(1),

Location TINYINT,

WeekendDate DATE,

TransVol INT,

RunningVol INT

);

INSERT #Product (Product,Location,WeekendDate,TransVol,RunningVol) VALUES

('A', 1, '11/01/2015', 50, 50),

('A', 1, '11/08/2015', 75, 125),

('A', 1, '11/22/2015', -25, 100),

('A', 2, '11/22/2015', 25, 25),

('B', 1, '11/15/2015', 25, 25);

--SELECT * FROM #Dates d;

--SELECT * FROM #Product p;

WITH

Prod AS (

SELECT

p.Product,

p.Location,

MIN(p.WeekendDate) AS BegDate

FROM

#Product p

GROUP BY

p.Product,

p.Location

),

ProdDates AS (

SELECT

p.Product,

p.Location,

d.WeekendDate

FROM

Prod p

JOIN #Dates d

ON p.BegDate <= d.WeekendDate

)

SELECT

pd.WeekendDate,

pd.Product,

pd.Location,

COALESCE(p.TransVol, 0) AS TransVol,

p.RunningVol,

SUM(p.TransVol) OVER (PARTITION BY pd.Product, pd.Location ORDER BY pd.WeekendDate ROWS UNBOUNDED PRECEDING) AS NewRunningTotal

FROM

ProdDates pd

JOIN #Dates d

ON pd.WeekendDate = d.WeekendDate

LEFT JOIN #Product p

ON pd.Product = p.Product

AND pd.Location = p.Location

AND pd.WeekendDate = p.WeekendDate

;

adhikari707 SSChasing Mays Points: 639 More actions · Answer 10

thanks Jason ... its working 🙂

will keep you posted about the results i am getting once i will start loading against DW.