Running balance calculation
-
Hi all,
I'm interested in creating a query that would calculate the balance of customers with an account open on the 1st of each month.
CREATE TABLE dbo.Customers (
CustomerID INT PRIMARY KEY,
AccountTypeDescription VARCHAR(50),
AccountOpenedDate DATETIME,
AccountClosedDate DATETIME
)It isn't as simple as a running total of accounts opened, as the dates when accounts are closed need to be considered too, which provides the balance of open accounts, by account type. Can this could be done in one succinct query?
Thanks
- piet_dj - Monday, October 2, 2017 9:50 AM
This table structure is not the best & should be split into at least two, if not three (if accounts can be opened or closed more than once), tables.
Having said that, what parameters will the query receive? Start and End dates, perhaps?
Does your desired output look like
Date, AccountTypeDescription, Count
?The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Yes I agree, the structure is not ideal (I can't change it), however an account does only have one opening and closing date per CustomerID.
In terms of desired output, it would be something like this eventually (once pivoted), displaying the total 'balance' of customer accounts which are remaining open, at each month start:
Some a table containing; Date, Account Type, Count is exactly right.
- piet_dj - Monday, October 2, 2017 10:42 AMYes I agree, the structure is not ideal, however there an account does only have one opening and closing date per CustomerID.
In terms of desired output, it would be something like this, displaying the total 'balance' of customer accounts which are remaining open, at each month start:
These numbers look more like counts of the number of accounts than balance figures. Running totals, either COUNT or SUM, can be achieved using the Window functions for SUM and COUNT, using an OVER clause, PARTITION BY Account Type ORDER BY ReportDate ROWS UNBOUNDED PRECEDING.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Sorry it isn't the account (monetary) balance i'm referring to, it's a balancing figure, calculated by:
[Number of Accounts Opened] - [Number of Account Closed] = [Remaining Number of Accounts Open].
This for each month, considering the dates of open/closure.
I could do a running total query for Accounts opened per month, and another for Accounts closed, and then a third to balance the two, but I'm sure there is a better method.
- piet_dj - Monday, October 2, 2017 11:05 AM
Here's a basic query that might lead in the right direction:
CREATE TABLE #Customers (
CustomerID int PRIMARY KEY,
AccountTypeDescription varchar(50),
AccountOpenedDate date,
AccountClosedDate date
);
INSERT INTO #Customers (CustomerID, AccountTypeDescription, AccountOpenedDate, AccountClosedDate)
SELECT CustomerID, AccountTypeDescription, AccountOpenedDate, AccountClosedDate
FROM (
VALUES (1, 'Standard', '2017-01-01', NULL),
(2, 'Standard', '2017-01-02', NULL),
(3, 'Standard', '2017-01-03', NULL),
(4, 'Premium', '2017-01-04', '2017-01-06'),
(5, 'Elite', '2017-01-05', NULL),
(6, 'Elite', '2017-01-06', NULL),
(7, 'Elite', '2017-01-07', NULL)
) AS X (CustomerID, AccountTypeDescription, AccountOpenedDate, AccountClosedDate);WITH ALL_DATES AS (
SELECT TOP (7) DATEADD(day, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '2017-01-01') AS THE_DATE
FROM sys.all_objects
),
ACCOUNT_TYPES AS (SELECT DISTINCT AccountTypeDescription AS [Account Type]
FROM #Customers
),
RAW_DATA AS (SELECT X.[Account Type], X.THE_DATE,
SUM(X.AccountsOpened) OVER(PARTITION BY X.[Account Type] ORDER BY X.THE_DATE ROWS UNBOUNDED PRECEDING) AS AccountsOpened
FROM (
SELECT T.[Account Type],
D.THE_DATE,
(
SELECT COUNT(DISTINCT C.CustomerID)
FROM #Customers AS C
WHERE C.AccountTypeDescription = T.[Account Type]
AND C.AccountOpenedDate = D.THE_DATE
) -
(
SELECT COUNT(DISTINCT C.CustomerID)
FROM #Customers AS C
WHERE C.AccountTypeDescription = T.[Account Type]
AND C.AccountClosedDate = D.THE_DATE
) AS AccountsOpened
FROM ALL_DATES AS D, ACCOUNT_TYPES AS T
GROUP BY T.[Account Type],
D.THE_DATE
) AS X
)
SELECT [Account Type],
ISNULL([2017-01-01], 0) AS [01/01/2017],
ISNULL([2017-01-02], 0) AS [01/02/2017],
ISNULL([2017-01-03], 0) AS [01/03/2017],
ISNULL([2017-01-04], 0) AS [01/04/2017],
ISNULL([2017-01-05], 0) AS [01/05/2017],
ISNULL([2017-01-06], 0) AS [01/06/2017],
ISNULL([2017-01-07], 0) AS [01/07/2017]
FROM RAW_DATA
PIVOT (MAX(AccountsOpened) FOR THE_DATE IN ([2017-01-01], [2017-01-02], [2017-01-03], [2017-01-04], [2017-01-05], [2017-01-06], [2017-01-07])) AS PVT
ORDER BY [Account Type] DESC;DROP TABLE #Customers;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Monday, October 2, 2017 12:46 PMIf an account is opened well before the reporting range and closes in or after the reporting range, I'm thinking that it still needs to be included as an active account even if neither date is in the reporting range but straddling it. I modified one row in the data setup and copied your code. Run it and see the anomaly that appears.
CREATE TABLE #Customers (
CustomerID int PRIMARY KEY,
AccountTypeDescription varchar(50),
AccountOpenedDate date,
AccountClosedDate date
);
INSERT INTO #Customers (CustomerID, AccountTypeDescription, AccountOpenedDate, AccountClosedDate)
SELECT CustomerID, AccountTypeDescription, AccountOpenedDate, AccountClosedDate
FROM (
VALUES (1, 'Standard', '2017-01-01', NULL),
(2, 'Standard', '2017-01-02', NULL),
(3, 'Standard', '2017-01-03', NULL),
(4, 'Premium', '2016-01-04', '2017-01-06'), --Changed Start Date here
(5, 'Elite', '2017-01-05', NULL),
(6, 'Elite', '2017-01-06', NULL),
(7, 'Elite', '2017-01-07', NULL)
) AS X (CustomerID, AccountTypeDescription, AccountOpenedDate, AccountClosedDate)
;
WITH ALL_DATES AS
(SELECT TOP (7) DATEADD(day, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '2017-01-01') AS THE_DATE
FROM sys.all_objects
),
ACCOUNT_TYPES AS (SELECT DISTINCT AccountTypeDescription AS [Account Type]
FROM #Customers
),
RAW_DATA AS (SELECT X.[Account Type], X.THE_DATE,
SUM(X.AccountsOpened) OVER(PARTITION BY X.[Account Type] ORDER BY X.THE_DATE ROWS UNBOUNDED PRECEDING) AS AccountsOpened
FROM (
SELECT T.[Account Type],
D.THE_DATE,
(
SELECT COUNT(DISTINCT C.CustomerID)
FROM #Customers AS C
WHERE C.AccountTypeDescription = T.[Account Type]
AND C.AccountOpenedDate = D.THE_DATE
) -
(
SELECT COUNT(DISTINCT C.CustomerID)
FROM #Customers AS C
WHERE C.AccountTypeDescription = T.[Account Type]
AND C.AccountClosedDate = D.THE_DATE
) AS AccountsOpened
FROM ALL_DATES AS D, ACCOUNT_TYPES AS T
GROUP BY T.[Account Type],
D.THE_DATE
) AS X
)
SELECT [Account Type],
ISNULL([2017-01-01], 0) AS [01/01/2017],
ISNULL([2017-01-02], 0) AS [01/02/2017],
ISNULL([2017-01-03], 0) AS [01/03/2017],
ISNULL([2017-01-04], 0) AS [01/04/2017],
ISNULL([2017-01-05], 0) AS [01/05/2017],
ISNULL([2017-01-06], 0) AS [01/06/2017],
ISNULL([2017-01-07], 0) AS [01/07/2017]
FROM RAW_DATA
PIVOT (MAX(AccountsOpened) FOR THE_DATE IN ([2017-01-01], [2017-01-02], [2017-01-03], [2017-01-04], [2017-01-05], [2017-01-06], [2017-01-07])) AS PVT
ORDER BY [Account Type] DESC;DROP TABLE #Customers;--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) -
Damned forum software whacked the formatting on half the code. I tried to fix it but it's not buying it. Apologies there.
--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) - piet_dj - Monday, October 2, 2017 10:42 AMYes I agree, the structure is not ideal (I can't change it), however an account does only have one opening and closing date per CustomerID.
In terms of desired output, it would be something like this eventually (once pivoted), displaying the total 'balance' of customer accounts which are remaining open, at each month start:
Some a table containing; Date, Account Type, Count is exactly right.
Just to verify... What format are those dates? DD/MM/YYYY? Also, please see the first link in my signature line below for how to post some readily consumable data, which will also clear up the date thing if you do it according to the write up.
--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) -
Not completely like you requested, but having values for column names makes it hard to retrieve the data for your client software, so i suggest you pick fixed column names instead as in this example:
CREATE TABLE #Customers (
CustomerID int PRIMARY KEY,
AccountTypeDescription varchar(50),
AccountOpenedDate date,
AccountClosedDate date
);
INSERT INTO #Customers (CustomerID, AccountTypeDescription, AccountOpenedDate, AccountClosedDate)
SELECT CustomerID, AccountTypeDescription, AccountOpenedDate, AccountClosedDate
FROM (
VALUES (1, 'Standard', {d '2017-01-01'}, NULL),
(2, 'Standard', {d '2017-02-01'}, NULL),
(3, 'Standard', {d '2017-03-01'}, NULL),
(4, 'Premium', {d '2017-04-01'}, {d '2017-06-01'}),
(5, 'Elite', {d '2017-05-01'}, NULL),
(6, 'Elite', {d '2017-06-01'}, NULL),
(7, 'Elite', {d '2017-07-01'}, NULL)
) AS X (CustomerID, AccountTypeDescription, AccountOpenedDate, AccountClosedDate);with cteNumbers as (
select row_number() over (order by (select null)) as n
from sys.syscolumns sc
)
select
c.AccountTypeDescription,
sum(case p.n when 6 then 1 else 0 end) as [month - 6],
sum(case p.n when 5 then 1 else 0 end) as [month - 5],
sum(case p.n when 4 then 1 else 0 end) as [month - 4],
sum(case p.n when 3 then 1 else 0 end) as [month - 3],
sum(case p.n when 2 then 1 else 0 end) as [month - 2],
sum(case p.n when 1 then 1 else 0 end) as [last month]
from (
select
n.n,
dateadd(month, -n.n + datediff(month,0, s.dt), 0) as startDate,
dateadd(day, 1, dateadd(month, -n.n + datediff(month,0, s.dt), 0)) as endDatefrom (
select /*getdate()*/{d '2017-07-01'}
) s (dt)
cross join cteNumbers n
where n.n <= 6
) p
inner join #customers c on (/*c.AccountOpenedDate is null or */c.AccountOpenedDate <= p.StartDate) and (c.AccountClosedDate is null or c.AccountClosedDate >= p.endDate)
group by
c.AccountTypeDescription
order by
c.AccountTypeDescription desc
Also your table definition allows for AccountOpenedDate to be NULL, so I left in comments a suggestion that would interpret rows having a NULL AccountOpenedDate as an account opened at a date before our time scale.edit: Just re-read your requirements and you stated that you need the number of accounts open on each 1st of the month. Not during the entire month. So I changed the row with the end date to include only accounts open on the first day of each month.
- Jeff Moden - Monday, October 2, 2017 8:25 PM
Firstly, thanks Steve for your input, I like your CTE solution.I've just stumbled across the issue Jeff has described when running the code, yes the account information from previous periods does need to be considered in order to provide the balance for the reporting period. I guess that hidden requirement complicates things significantly.
Jeff Moden - Monday, October 2, 2017 9:51 PMpiet_dj - Monday, October 2, 2017 10:42 AMYes I agree, the structure is not ideal (I can't change it), however an account does only have one opening and closing date per CustomerID.In terms of desired output, it would be something like this eventually (once pivoted), displaying the total 'balance' of customer accounts which are remaining open, at each month start:
Some a table containing; Date, Account Type, Count is exactly right.
Just to verify... What format are those dates? DD/MM/YYYY? Also, please see the first link in my signature line below for how to post some readily consumable data, which will also clear up the date thing if you do it according to the write up.
The format is DD/MM/YYYY in my image, so it represents each month start date. Apologies, it's been some time since I've visited the forum and my posting skills are rusty.
Pete
- piet_dj - Monday, October 2, 2017 11:05 AM
Aren't you overcomplicating this? Can't you simply count the accounts which are open on the first of each month? Like this:
SELECT
AccountTypeDescription,
[2016-11-01] = SUM(CASE WHEN WhereDate = '2016-11-01' THEN 1 ELSE 0 END),
[2016-12-01] = SUM(CASE WHEN WhereDate = '2016-12-01' THEN 1 ELSE 0 END),
[2017-01-01] = SUM(CASE WHEN WhereDate = '2017-01-01' THEN 1 ELSE 0 END),
[2017-02-01] = SUM(CASE WHEN WhereDate = '2017-02-01' THEN 1 ELSE 0 END),
[2017-03-01] = SUM(CASE WHEN WhereDate = '2017-03-01' THEN 1 ELSE 0 END),
[2017-04-01] = SUM(CASE WHEN WhereDate = '2017-04-01' THEN 1 ELSE 0 END),
[2017-05-01] = SUM(CASE WHEN WhereDate = '2017-05-01' THEN 1 ELSE 0 END),
[2017-06-01] = SUM(CASE WHEN WhereDate = '2017-06-01' THEN 1 ELSE 0 END),
[2017-07-01] = SUM(CASE WHEN WhereDate = '2017-07-01' THEN 1 ELSE 0 END),
[2017-08-01] = SUM(CASE WHEN WhereDate = '2017-08-01' THEN 1 ELSE 0 END),
[2017-09-01] = SUM(CASE WHEN WhereDate = '2017-09-01' THEN 1 ELSE 0 END),
[2017-10-01] = SUM(CASE WHEN WhereDate = '2017-10-01' THEN 1 ELSE 0 END)
FROM #Customers
CROSS APPLY (
SELECT WhereDate = CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-n,0) AS DATE)
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) v (n)
) x
WHERE AccountOpenedDate <= WhereDate AND (AccountClosedDate IS NULL OR AccountClosedDate > WhereDate)
GROUP BY AccountTypeDescription“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - ChrisM@Work - Tuesday, October 3, 2017 2:05 AM
That's the ticket. Nice and simple. Of course, the next question will end up being how to make it dynamic.
@piet_dj , there's an article on how to make keep it simple, like Chris did, and convert it to being dynamic. It won't be "one succinct query", however. Here's the article...
http://www.sqlservercentral.com/articles/Crosstab/65048/If you decide to make it so that any account with any activity during a particular month is what you really need to count, then please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/105968/I'd also recommend getting out of the habit of storing NULLs for end dates so that you don't need to use an OR in the WHERE clause. If you store or default a DATETIME column to "9999" (easy to remember) , it will resolve to 9999-01-01, which is far enough into the future to never fall out of favor, establishes a standard, leaves headroom for date calculations, and makes it so that you don't need OR in your WHERE clauses.
You should also get back into the habit of posting readily consumable data with your question, which you still haven't done. 😉
--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) - Jeff Moden - Monday, October 2, 2017 8:25 PM
It was only intended to get the original poster on the path toward a solution. I knew going in that there was lot more to worry about than what my query was going to cover, but the inherent time limits associated with doing this during work hours meant leaving some things at least partially undone, and leaving that to the original poster to fill in the gaps. I also figured you'd likely chime in, and of course, you did. The dates being "euro-style" (so to speak) wasn't something I picked up on right away, but by the time I knew about it, it was already too late to mess with my code. And no biggy on the code formatting... ran into that recently, and did manage to figure out how to stop that from happening. You have to be sure your code isn't being pasted in directly from SSMS, as that actually has formatting in it, and while we can now "get away with it", that doesn't always work to just let this site remove the formatting. Thus I now paste into Notepad and then recopy and paste into the site.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Tuesday, October 3, 2017 6:44 AM
I pasted the code from NotePad. They're never recovered from the "improvements" made when they changed the forum software. There are some great things they've added, like the ability to drag graphics on screen but the code windows still have some major problems.
Heh... absolutely understood on the working-hours thing. That's also why I've been referring folks to articles or just making a suggestion... I've not had the time to post code because I don't have the time to do the testing I normally do before posting... especially when the OP doesn't post readily consumable data. Thank you for helping the OP especially in the area of creating some readily consumable data.
On this given problem, it wasn't real clear even in subsequent posts what the OP was really after especially since the words "Running Balance" instead of "What's currently active" were used. It was a point of confusion. I made a guess and the guess turned out to be correct. If I didn't come across that way, my most sincere apologies. The written word isn't always friendly especially if I'm typing during a coffee depraved stated. 😉
--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)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply