SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arithmetic Operation on MySQL

    Hi, I'd like to compute for the running balance of a customer. If the customer pays something then that value will be deducted on its current running balance. Attached is a figure and my query.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    here's your query, no need to attach a text file:
    Code:
    SELECT patientfees.entryid
         , patientfees.entrydate
         , servicefees.servicedescription
         , patientfees.amount
      FROM patientfees 
    INNER 
      JOIN servicefees 
        ON servicefees.servicecode = patientfees.servicecode
     WHERE patientfees.patientid = '2008-0001' 
       AND patientfees.entrydate >= '04/20/2008' 
    UNION ALL
    SELECT entryid
         , paymentdate
         , description
         , amount 
      FROM paymentmade 
     WHERE patientid = '2008-0001'
    ORDER 
        BY entrydate DESC
    you do realize that '04/20/2008' is not a valid mysql date, right?

    regarding your problem to calculate a running balance from the data produced by this UNION query, my advice is to do this in your application layer
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you sir, I've already done it on my application layer. However, my query is needed in order for me to create a report using a crystal report.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, do it in your application layer, and then store the results back into the database as a table, then use SELECT * FROM table for your crystal report

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

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    since this is mysql, you can take advantage of user-defined variables:
    Code mysql:
    SELECT patientfees.entryid
         , patientfees.entrydate
         , servicefees.servicedescription
         , patientfees.amount
         , @rb = @rb + patientfees.amount AS running_balance
      FROM patientfees 
    INNER 
      JOIN servicefees 
        ON servicefees.servicecode = patientfees.servicecode
     WHERE patientfees.patientid = '2008-0001' 
       AND patientfees.entrydate >= '04/20/2008' 
    UNION ALL
    SELECT entryid
         , paymentdate
         , description
         , amount 
      FROM paymentmade 
     WHERE patientid = '2008-0001'
    ORDER 
        BY entrydate DESC
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that works across a UNION query?

    verrrry interrrresting!
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where does @rb comes from? Is that a variable that comes from my application layer?

    I am very sorry for that question sir.

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    oh crap, i forgot about the UNION. need to make a small change:
    Code mysql:
    SELECT *
         , @rb = @rb + amount AS running_balance
      FROM (SELECT patientfees.entryid
                 , patientfees.entrydate
                 , servicefees.servicedescription
                 , patientfees.amount
                 , 
              FROM patientfees
            INNER
              JOIN servicefees
                ON servicefees.servicecode = patientfees.servicecode
             WHERE patientfees.patientid = '2008-0001'
               AND patientfees.entrydate >= '04/20/2008'
             UNION ALL
            SELECT entryid
                 , paymentdate
                 , description
                 , amount
              FROM paymentmade
             WHERE patientid = '2008-0001'
            ORDER
                BY entrydate DESC) AS dt
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sirGE View Post
    Where does @rb comes from? Is that a variable that comes from my application layer?
    no, it's a mysql variable. it's automatically initialized to 0 upon first use.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  10. #10
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am very sorry for the late. I had a headache for few days. Okey. Now I see...thank you.

  11. #11
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way, how am I going to declare that this topic is resolved? Thanks to you gurus.

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    we don't do that here.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •