SQL Query to find last balance per account at any point in time
Hi experts,
I have a table containing transactions over time, and the transactions using different bank accounts.
How d I contruct my query to tell me the balance per account at any specifc date?
My table looks like
Date Purchase Desc amount Balance Account Used
.
.
.
12/08/2017 item 1 500 1500 Halifax
14/09/2017 item 2 25.69 34.56 PayPal
16/09/2017 item 3 130 1630 Halifax
...
So If I run the query to give me my balances as at 16/09/2017 I wlll get
PayPal 14/09/2017 34.56
Halifax 16/09/2017 1630
If I get this, then I can develop it to simply give me my total balance at any specified date by summing up all last balances from every account.
I believe I need to use ROW_NUMBER () PARTITION OVER but I'm struggling with it.
This is primarily for Oracle but SQL Server (2014) also welcome.
Thanks.
I have a table containing transactions over time, and the transactions using different bank accounts.
How d I contruct my query to tell me the balance per account at any specifc date?
My table looks like
Date Purchase Desc amount Balance Account Used
.
.
.
12/08/2017 item 1 500 1500 Halifax
14/09/2017 item 2 25.69 34.56 PayPal
16/09/2017 item 3 130 1630 Halifax
...
So If I run the query to give me my balances as at 16/09/2017 I wlll get
PayPal 14/09/2017 34.56
Halifax 16/09/2017 1630
If I get this, then I can develop it to simply give me my total balance at any specified date by summing up all last balances from every account.
I believe I need to use ROW_NUMBER () PARTITION OVER but I'm struggling with it.
This is primarily for Oracle but SQL Server (2014) also welcome.
Thanks.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
(No points... just a typo)
I *think* there's a small typo. For SQL Server, the variable name "myDate" needs a "@" in front of it. Otherwise, SQL Server thinks it's the name of a CURSOR, not a variable. Try
declare @mydate datetime
set @mydate='2017-09-16'
Also, I think "@mysdate" is supposed to be "@mydate"
declare mydate datetime
set mydate='2017-09-16-
I *think* there's a small typo. For SQL Server, the variable name "myDate" needs a "@" in front of it. Otherwise, SQL Server thinks it's the name of a CURSOR, not a variable. Try
declare @mydate datetime
set @mydate='2017-09-16'
Also, I think "@mysdate" is supposed to be "@mydate"
_agx_,
Pointed out the typos that should fix the issues you mentioned.
Skipped the review before posting step.
Pointed out the typos that should fix the issues you mentioned.
Skipped the review before posting step.
ASKER
Thanks @arnold.
With tweaks, it works like a charm.
Many thanks for your quick response.
With tweaks, it works like a charm.
Many thanks for your quick response.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
I was near.
The first code with minor mod kind of works but not completely. I'm working on it. If I fail, I'll get back to you.
The send code with declared variable reports
'datetime' is not a recognized CURSOR option.
What does that mean?
Sorry I'm replying quickly otherwise I would have searched for the error.
Thanks