Jump to content

SQL Query - Balance by date


gj88888
 Share

Recommended Posts

Hi, could someone please help me with a SQL query.

 

I have a table called TRANSACTIONS. In this table there are 3 fields Date, Payee & Amount.

 

I am trying to create a query that will give the balance at every single day of the year.

 

I have tried to do this with a table called DATES with dates in and link it, but I can not get this to work.

 

 

Thanks in advance.

Link to comment
Share on other sites

You can do like this ....

 

SELECT TRANSACTIONS. *, DATES.*

FROM TRANSACTIONS ,DATES

WHERE TRANSACTIONS.Date = DATES.Date;

maybe its the best we can get more detail your field .....

Link to comment
Share on other sites

Hi,

 

I need to have a running balance. This would not give a running balance right.

 

So the output of the query should have a balance column. In this column would be the sum of all transactions on this date and previous dates.

 

Even if there are no transactions in the table for a certain date, the query should display the balance.

Link to comment
Share on other sites

Hi,

This doesn't seem to work. Maybe if I add some more detail it will help.

 

So I have a table like this called TRANSACTIONS

Date Payee Amount

01/01/2013 PTRP Ltd -1000.25

01/01/2013 HGJD Ltd -5986.56

02/01/2013 DUU AG 1000

05/01/2013 HH PLC -1000

 

I created a table called DATES that simple had all the dates of the year (I thought this might be the easiest way to do it, but maybe I do not need this table.

Date

01/01/2013

02/01/2013

03/01/2013

04/01/2013

05/01/2013

06/01/2013

 

I want to create a query that would deliver this output.

 

Date Balance

01/01/2013 -6986.81

02/01/2013 -5986.81

03/01/2013 -5986.81

04/01/2013 -5986.81

05/01/2013 -6986.81

06/01/2013 -6986.81

 

Thanks!

Edited by gj88888
Link to comment
Share on other sites

That's a lot of work for the database, it doesn't have a "running total", for each line it would need to do all of the addition. It would be better to get the list of transactions and actually do a running total with another language, it would be less work for the database.

 

Otherwise, you might be able to select all of the dates and do a left join on the transactions table, although I'm not quite sure how to have it sum everything that is on or before the date from the current row.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...