June 9, 2016 at 8:50 am
Hi,
I have a table where we hold the balance on a weekly basis.
I want to be able to show the balance on a daily basis based on the transaction values.
I've tried a number of techniques, windowing functions , quirky update but I seem to be getting nowhere.
Any help would be much appreciated.
Script to create the data, Below.
CREATE TABLE [dbo].[Balances](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionDate] [date] NULL,
[AccountNUmber] [int] NULL,
[Balance] [numeric](17, 2) NULL,
[TransactionValue] [numeric](17, 2) NULL
) ON [PRIMARY]
INSERT INTO Balances(TransactionDate,AccountNUmber,Balance,TransactionValue)
VALUES
('2016-01-01',349,NULL,NULL),
('2016-01-02',349,NULL,NULL),
('2016-01-03',349,67.04,90.32),
('2016-01-04',349,NULL,-23.12),
('2016-01-05',349,NULL,NULL),
('2016-01-06',349,NULL,NULL),
('2016-01-07',349,NULL,-134.40),
('2016-01-08',349,NULL,NULL),
('2016-01-09',349,NULL,NULL),
('2016-01-10',349,-0.16,90.32),
('2016-01-11',349,NULL,NULL),
('2016-01-12',349,NULL,-23.12),
('2016-01-13',349,NULL,NULL),
('2016-01-14',349,NULL,NULL),
('2016-01-15',349,NULL,NULL),
('2016-01-16',349,NULL,NULL),
('2016-01-17',349,67.04,90.32),
('2016-01-18',349,NULL,NULL),
('2016-01-19',349,NULL,-23.12),
('2016-01-20',349,NULL,NULL),
('2016-01-21',349,NULL,NULL),
('2016-01-22',349,NULL,NULL),
('2016-01-23',349,NULL,NULL),
('2016-01-24',349,134.24,90.32),
('2016-01-25',349,NULL,NULL),
('2016-01-26',349,NULL,-23.12),
('2016-01-27',349,NULL,NULL),
('2016-01-28',349,NULL,NULL),
('2016-01-29',349,NULL,NULL),
('2016-01-30',349,NULL,NULL),
('2016-01-31',349,201.44,90.32),
('2016-02-01',349,NULL,NULL),
('2016-02-02',349,NULL,-23.12),
('2016-02-03',349,NULL,NULL),
('2016-02-04',349,NULL,-268.80),
('2016-02-05',349,NULL,NULL),
('2016-02-06',349,NULL,NULL),
('2016-02-07',349,-0.16,90.32),
('2016-02-08',349,NULL,NULL),
('2016-02-09',349,NULL,-23.12),
('2016-02-10',349,NULL,NULL),
('2016-02-11',349,NULL,NULL),
('2016-02-12',349,NULL,NULL),
('2016-02-13',349,NULL,NULL),
('2016-02-14',349,67.04,90.32),
('2016-02-15',349,NULL,NULL),
('2016-02-16',349,NULL,-23.12),
('2016-02-17',349,NULL,NULL),
('2016-02-18',349,NULL,NULL),
('2016-02-19',349,NULL,NULL),
('2016-02-20',349,NULL,NULL),
('2016-02-21',349,134.24,90.32),
('2016-02-22',349,NULL,NULL),
('2016-02-23',349,NULL,-23.12),
('2016-02-24',349,NULL,NULL),
('2016-02-25',349,NULL,NULL),
('2016-02-26',349,NULL,NULL),
('2016-02-27',349,NULL,NULL),
('2016-02-28',349,201.44,90.32),
('2016-02-29',349,NULL,NULL),
('2016-03-01',349,NULL,-23.12),
('2016-03-02',349,NULL,NULL),
('2016-03-03',349,NULL,-268.80),
('2016-03-04',349,NULL,NULL),
('2016-03-05',349,NULL,NULL),
('2016-03-06',349,-0.16,90.32),
('2016-03-07',349,NULL,NULL),
('2016-03-08',349,NULL,-23.04),
('2016-03-09',349,NULL,NULL),
('2016-03-10',349,NULL,NULL),
('2016-03-11',349,NULL,NULL),
('2016-03-12',349,NULL,NULL),
('2016-03-13',349,67.12,90.32)
June 9, 2016 at 9:00 am
It looks like the piece you might be missing is a calendar table to make sure that you have all of the dates instead of just the dates where there are transactions.
You said that you've tried a lot of things, it's usually best to start with what you've tried.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 9, 2016 at 9:27 am
This is what I understood from what you posted, or at least it makes some logic to me. I agree with Drew that you might want to use a calendar table to check that you're not missing any dates.
SELECT b.*,
m.Balance + ISNULL(SUM(TransactionValue) OVER( PARTITION BY AccountNumber ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
FROM [dbo].[Balances] b
CROSS APPLY( SELECT TOP (1) Balance
FROM dbo.Balances i
WHERE b.AccountNUmber = i.AccountNumber
AND i.Balance IS NOT NULL
ORDER BY TransactionDate) m;
June 9, 2016 at 9:56 am
Perfect thanks
that was actually just a snapshot.
I used CTE when creating the balances table and left joined to the source.
I believe I've Included all the dates thanks
June 9, 2016 at 11:04 am
This actually might perform better, but it requires that the earliest balance not be NULL, which is not true of your sample data.
SELECT b.*,
FIRST_VALUE(m.Balance) OVER( PARTITION BY AccountNumber ORDER BY TransactionDate ROWS UNBOUNDED PRECEDING )
+ ISNULL(SUM(TransactionValue) OVER( PARTITION BY AccountNumber ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
FROM [dbo].[Balances] b
Drew
PS: "ROWS UNBOUNDED PRECEDING" is a shortcut for "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW".
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 10, 2016 at 3:24 am
Yeah,
It was the fact the first balance could possibly be null that was causing me the issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply