Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Apr 2017
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Balance per month while keeping the balance of the previous month

    Good evening everyone.

    I am confronted with a concern with one of my requests, I would like to have the balance of receipts and expenses and this per month while keeping the balance of the previous month that will be added to the current month.

    Here is my request:

    Code:
    SELECT M.mvt_date, COALESCE( R.cr_ref, D.cD_ref ) AS reference, COALESCE( R.cr_motif, D.cD_motif ) AS motif, COALESCE( R.cr_montant, 0 ) AS recette, COALESCE( D.cd_montant, 0 ) AS depense, @cumul := @cumul + COALESCE( R.cr_montant, 0 ) - COALESCE( D.cd_montant, 0 ) AS cumul FROM Mouvement M CROSS JOIN ( SELECT @cumul:=0 ) tmp LEFT JOIN entree E ON M.mvt_id = E.mvt_id LEFT JOIN carnetrecette R ON E.cr_id = R.cr_id LEFT JOIN sortie S ON M.mvt_id = S.mvt_id LEFT JOIN carnetdepense D ON S.cd_id = D.cd_id WHERE month(m.mvt_date)=month(now())
    But when I do so it just makes the balance of the current month regardless of the previous month

    Can I ask for your help?

  2. #2
    Senior Coder benanamen's Avatar
    Join Date
    Oct 2015
    Posts
    1,154
    Thanks
    2
    Thanked 122 Times in 118 Posts
    Balance should be calculated on the fly with date ranges.
    To save time, lets just assume I am almost never wrong.

    The XY Problem
    The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

    "This text has been encoded with ROT26. If you can read this you must have found a backdoor. Congratulations!"

  3. #3
    New to the CF scene
    Join Date
    Apr 2017
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quite but when I put a date interval I still do not have what I want
    Here is an example of the same request with the interval
    Code:
    SELECT M.mvt_date, COALESCE( R.cr_ref, D.cD_ref ) AS reference, COALESCE( R.cr_motif, D.cD_motif ) AS motif, COALESCE( R.cr_montant, 0 ) AS recette, COALESCE( D.cd_montant, 0 ) AS depense, @cumul := @cumul + COALESCE( R.cr_montant, 0 ) - COALESCE( D.cd_montant, 0 ) AS cumul FROM Mouvement M CROSS JOIN ( SELECT @cumul:=0 ) tmp LEFT JOIN entree E ON M.mvt_id = E.mvt_id LEFT JOIN carnetrecette R ON E.cr_id = R.cr_id LEFT JOIN sortie S ON M.mvt_id = S.mvt_id LEFT JOIN carnetdepense D ON S.cd_id = D.cd_id WHERE M.mvt_date between '2015-11-01' AND '2015-11-30'
    The request still does not take into account the balance of previous month's expenditures and revenues


 

Tags for this Thread

Posting Permissions

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