SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  1. #1
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)

    Is it possible to do a running balance?

    I'm currently doing a report that does a T ledger. It needs to do a running balance from a known start point. I'm trying to migrate the logic for this into SQL as much as possible and querying a running balances is something I haven't figured out...

    Code:
          Credit  Debit  Balance 
    start     --     --   100.00
    row1   10.00          110.00
    row2           20.00   90.00
    row3   10.00          100.00
    As can be seen, the balance of each row id dependent on the contents of the row that preceded it. This information is NOT precalculated at insert time nor can it be since the ledger needs to reflect changes brought by query filters that determine which rows show up. So, can this be done in SQL alone?

  2. #2
    SitePoint Addict svcghost's Avatar
    Join Date
    Oct 2010
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So would it not be possible to, upon insert, create a new record that has a Balance value of Balance + Credit or Balance - Debit depending on what was inputted? And then just also input the Debit or Credit value too. How are you getting this data? A textbox in a form??

  3. #3
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Data is out of Mysql - that text box is to show what I mean by a running balance. After making the first post I hit this in Google.

    http://www.databasejournal.com/featu...t-a-Cursor.htm

    Reading over that now, but the thread is still useful cause I'm sure this comes up from time to time.

    And writing the value at insert time is not possible, because we want the balances to change depending on search criteria. In the quick example I gave, if the user decided to reverse the order the rows are displayed the balances need to be recalculated to follow their new order on the page. This is strictly derived data.

  4. #4
    SitePoint Addict svcghost's Avatar
    Join Date
    Oct 2010
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool cool. Glad you found your answer!

  5. #5
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Not sure I have. Just reading.

  6. #6
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Making progress, but could use some help. This is what I have so far.

    Code sql:
    SET @itemBalance = 0;
     
    SELECT
    	worksheet.accountid AS id, 
    	worksheet.`type` AS `type`, 
    	worksheet.`number` AS `number`, 
    	worksheet.`name` AS `name`,
     
    	worksheet.itemid AS items_id,
    	worksheet.debit AS items_debit,
    	worksheet.credit AS items_credit,
    	(@itemBalance := @itemBalance + worksheet.amount) AS items_balance,
    	worksheet.fiscaldate AS items_fiscaldate,
     
    	worksheet.description AS items_description,
    	worksheet.receiptid AS items_receiptid,
    	worksheet.transactionid AS items_transactionid
    FROM (
    	SELECT 
    		a.`id` AS accountid, 
    		`type` AS `type`, 
    		`full_account_number` AS `number`, 
    		`name` AS `name`,
    		i.id AS itemid,
    		i.debit AS debit,
    		i.credit AS credit,
    		DATE_FORMAT(t.fiscal_date, '%c/%e/%Y') AS fiscaldate,
    		t.description AS description,
    		r.receipt_id AS receiptid,
    		t.id AS transactionid,
     
    		IF ( a.`type` IN ('REVENUE', 'ASSET', 'EQUITY'),
    			i.credit - i.debit,
    			i.debit - i.credit
    		) AS 'amount'
    	FROM gl_transaction_items i 
    		LEFT JOIN gl_accounts a ON i.glaccount_id = a.id
    		LEFT JOIN gl_transactions t ON t.id = i.gltransaction_id
    		LEFT OUTER JOIN gl_auto_post_history r ON r.transaction_id = t.id	
    	WHERE a.id = :account
    		AND t.fiscal_date >= :startDate
    		AND t.fiscal_date <= :endDate 
    		AND (t.voided = 0 OR t.voided IS NULL )
    		AND t.posted = 1
    	ORDER BY t.fiscal_date DESC 
    ) AS worksheet

    What I'm doing is running a query to get my information, then selecting from that query. As I pull that information in the @itemBalance variable is able to create a running total for my ledger. The output looks like this..

    Code:
    Credit 	Debit   	Balance
    $ 66.67 	$ 0.00 	$ 66.67
    $ 17.47 	$ 0.00 	$ 84.14
    $ 12.83 	$ 0.00 	$ 96.97
    $ 332.97 	$ 0.00 	$ 429.94
    Now comes the next part that I'm having trouble with. I need totals for the credit and debit column - and the final balance is the difference of the two. The entry of the final balance MUST match the balance displayed on the last column for the report to be valid. Yeah, I don't expect computers to fail at math - but I could fail in coding

    Anyway, normally I'd do a group by clause followed up with a sum() call - but I don't want to disturb the ability of the query to pull the individual amounts.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    So, can this be done in SQL alone?
    yes, it can, but it's often quite inefficient, and better done in the front end

    what database system is it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    mySQL

    I have something that is working

    Code sql:
    SELECT
    	worksheet.accountid AS id, 
    	worksheet.`type` AS `type`, 
    	worksheet.`number` AS `number`, 
    	worksheet.`name` AS `name`,
    	totals.credit AS totalcredit,
    	totals.debit AS totaldebit,
    	worksheet.itemid AS items_id,
    	worksheet.debit AS items_debit,
    	worksheet.credit AS items_credit,
    	(@itemBalance := @itemBalance + worksheet.amount) AS items_balance,
    	worksheet.fiscaldate AS items_fiscaldate,
     
    	worksheet.description AS items_description,
    	worksheet.receiptid AS items_receiptid,
    	worksheet.transactionid AS items_transactionid
    FROM (
    	SELECT 
    		a.`id` AS accountid, 
    		`type` AS `type`, 
    		`full_account_number` AS `number`, 
    		`name` AS `name`,
    		i.id AS itemid,
    		i.debit AS debit,
    		i.credit AS credit,
    		DATE_FORMAT(t.fiscal_date, '%c/%e/%Y') AS fiscaldate,
    		t.description AS description,
    		r.receipt_id AS receiptid,
    		t.id AS transactionid,
     
    		IF ( a.`type` IN ('REVENUE', 'ASSET', 'EQUITY'),
    			i.credit - i.debit,
    			i.debit - i.credit
    		) AS 'amount'
    	FROM gl_transaction_items i 
    		LEFT JOIN gl_accounts a ON i.glaccount_id = a.id
    		LEFT JOIN gl_transactions t ON t.id = i.gltransaction_id
    		LEFT OUTER JOIN gl_auto_post_history r ON r.transaction_id = t.id	
    	WHERE a.id = :account
    		AND t.fiscal_date >= :startDate
    		AND t.fiscal_date <= :endDate 
    		AND (t.voided = 0 OR t.voided IS NULL )
    		AND t.posted = 1
    	ORDER BY t.fiscal_date DESC 
    ) AS worksheet
    JOIN (
    	SELECT
    		glaccount_id AS accountid,
    		SUM(i.credit) AS credit,
    		SUM(i.debit) AS debit
    	FROM gl_transaction_items i 
    		LEFT JOIN gl_transactions t ON t.id = i.gltransaction_id
    	WHERE i.glaccount_id = :account
    		AND t.fiscal_date >= :startDate
    		AND t.fiscal_date <= :endDate 
    		AND (t.voided = 0 OR t.voided IS NULL )
    		AND t.posted = 1
    	GROUP BY i.glaccount_id
    ) AS totals ON totals.accountid = worksheet.accountid

    Not sure if its the most efficient setup, but it is working.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    mySQL
    thanks, i have flagged the thread to have it moved to the mysql forum

    if what you have works, i'd go with it

    did you have a concern?

    i noticed something which could be improved, if only slightly...

    when you do this --
    Code:
    FROM gl_transaction_items i 
    LEFT JOIN gl_accounts a ON i.glaccount_id = a.id
    LEFT JOIN gl_transactions t ON t.id = i.gltransaction_id
    LEFT OUTER JOIN gl_auto_post_history r ON r.transaction_id = t.id
    you are allowing for the possibility that a transaction item can have an account_id that isn't in the accounts table, or a transaction_id that isn't in the transactions table, or a transaction can have an id that isn't in the auto_post_history table

    if those cases must be allowed for, fine, otherwise i suspect the joins should be INNER JOINs instead

    also when you do this --
    Code:
    WHERE a.id = :account
    AND t.fiscal_date >= :startDate
    AND t.fiscal_date <= :endDate 
    AND (t.voided = 0 OR t.voided IS NULL )
    AND t.posted = 1
    it automatically turns two of those left outer joins into behaving exactly like inner joins anyway, so you might as well write them that way, as that can often improve performance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Still not clear on the difference between inner and outer joining, but I'll switch that up and see if it helps.

    That auto_post_history table has a weird name (I didn't name it) -- it exists to join the transaction and receipt table and only has 3 columns - transaction, receipt and its own primary key. Normally when it's joined the query joins the receipt table as well, but in this particular circumstance I don't need any data off the receipt table - just the id which I can get with that cross reference table.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    Still not clear on the difference between inner and outer joining
    check out this article -- http://articles.sitepoint.com/articl...he-from-clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    range? what range? which query?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    The query I settled on above works fine for 1 account. Now if I want to pull more than 1 account at a time.... boom.

    The sql var doesn't reinit between different accounts.

    I've given up trying to do it in SQL and am going to add it up in PHP. If anyone wants to see if they can come up with a solution I'm curious, but I gots to get something out the door.

  14. #14
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    I didn't lose enough time by doing it PHP side to justify trying to redo the code with SQL calculation - though I am curious enough to maybe do it on my own time. The PHP version pulls 2 years worth of data in under 3 minutes, which is more than fast enough for this function (only one or two users would ever run the report in question).

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if the SQL currently takes 3 minutes to return the data wihout a running total, then i have very little hope that adding a running total to the SQL will let the query finish in anywhere near acceptable times...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •