gj88888 Posted July 14, 2013 Share Posted July 14, 2013 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 More sharing options...
fikiwan Posted July 14, 2013 Share Posted July 14, 2013 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 More sharing options...
gj88888 Posted July 15, 2013 Author Share Posted July 15, 2013 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 More sharing options...
jaseervp@ymail.com Posted July 15, 2013 Share Posted July 15, 2013 (edited) .select trunc(Date_), sum(Amount) from TRANSACTIONSgroup by trunc(Date_) Edited July 15, 2013 by jaseervp@ymail.com Link to comment Share on other sites More sharing options...
gj88888 Posted July 15, 2013 Author Share Posted July 15, 2013 (edited) 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted July 15, 2013 Share Posted July 15, 2013 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now