SQL Query - Balance by date

[gj88888]

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.

[fikiwan]

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 .....

[gj88888]

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.

[jaseervp@ymail.com]

.select trunc(Date_), sum(Amount) from TRANSACTIONSgroup by trunc(Date_)

Edited July 15, 2013 by jaseervp@ymail.com

[gj88888]

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 July 15, 2013 by gj88888

[justsomeguy]

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.