#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0

    Subtraction from multiple rows


    I am trying to calculate the remaining units. I have one table with the original amount of units and one with the contacts being used for the units.

    Ex: Authorization (start date, end date, total units)
    Contacts ( Date, units used)

    Authorization (1/1/2012, 1/30/2012, 10)
    Contacts (1/2/2012, 2)
    Contacts (1/5/2012, 3)

    it should tell me that there is 5 units remaining but it is just giving me to rows one giving 8 and one 7.

    Any thoughts?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    you've left out quite a bit of information, e.g. no self-respecting tables have just a date and a quantity

    and with only one row in the authorizations table, and two rows in the contacts table, the SQL is trivial --
    Code:
    SELECT a.totalunits
         , SUM(c.unitsused) AS unitsused
         , a.totalunits - 
           SUM(c.unitsused) AS remaining
      FROM Authorization AS a
    CROSS
      JOIN Contacts AS c  
    GROUP
        BY a.totalunits
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Originally Posted by r937
    you've left out quite a bit of information, e.g. no self-respecting tables have just a date and a quantity

    and with only one row in the authorizations table, and two rows in the contacts table, the SQL is trivial --
    Code:
    SELECT a.totalunits
         , SUM(c.unitsused) AS unitsused
         , a.totalunits - 
           SUM(c.unitsused) AS remaining
      FROM Authorization AS a
    CROSS
      JOIN Contacts AS c  
    GROUP
        BY a.totalunits
    Thank you I did leave out some information in the tables to simplify my question to get a straightforward response, which you gave me. The issue now is that my database is an access database and does not support CROSS JOINS, is there a work around that you know of?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by dennisdj
    ... is there a work around that you know of?
    sure

    change this --
    Code:
      FROM Authorization AS a
    CROSS
      JOIN Contacts AS c
    to this --
    Code:
      FROM Authorization AS a
         , Contacts AS c
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Originally Posted by r937
    sure

    change this --
    Code:
      FROM Authorization AS a
    CROSS
      JOIN Contacts AS c
    to this --
    Code:
      FROM Authorization AS a
         , Contacts AS c
    Thank you. When I tried this one it asks me to entering the units used as opposed to taking it from the contact table. Perhaps my leaving out information has impeded the correct answer. Her are the tables in their entirety...

    Authorization [id, patientID, authstartdate, authenddate,service, dianosis, totalunits, insurance, insurance id]

    Contact [contactid, patientid, servicedate, unitsused, service, paymentstatus]

    What I trying to return with the query is that ex: if the authorization table has the following

    (1, DOEJA001, 12/1/2012, 01/25/2013, H0040, 296.34, 8, Medicaid, 253-65-1234)

    And the contact table has the following:

    (1, DOEJA001, 1/1/2013, 3, H0040, Billed)
    (2, DOEJA001, 1/2/2013, 2, H0040, Billed)
    (3, DOEJA001, 4/1/2013, 3, H0040, Billed)

    It should return (hopefully):

    (PatientID, authstartdate, authenddate, service, totalunits, remaining units)
    or

    (DOEJA001, 12/1/2012, 01/25/2013, H0040, 8, 3)

    it will lead out the third one since it is outside of the authorization period.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by dennisdj
    ... it asks me to entering the units used
    which is why i never use square brackets

    you've typed a column name incorrectly

    remove all the square brackets from your query sql, and you will get an error message instead

    then check the spelling of your columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Originally Posted by dennisdj
    Thank you. When I tried this one it asks me to entering the units used as opposed to taking it from the contact table. Perhaps my leaving out information has impeded the correct answer. Her are the tables in their entirety...

    Authorization [id, patientID, authstartdate, authenddate,service, dianosis, totalunits, insurance, insurance id]

    Contact [contactid, patientid, servicedate, unitsused, service, paymentstatus]

    What I trying to return with the query is that ex: if the authorization table has the following

    (1, DOEJA001, 12/1/2012, 01/25/2013, H0040, 296.34, 8, Medicaid, 253-65-1234)

    And the contact table has the following:

    (1, DOEJA001, 1/1/2013, 3, H0040, Billed)
    (2, DOEJA001, 1/2/2013, 2, H0040, Billed)
    (3, DOEJA001, 4/1/2013, 3, H0040, Billed)

    It should return (hopefully):

    (PatientID, authstartdate, authenddate, service, totalunits, remaining units)
    or

    (DOEJA001, 12/1/2012, 01/25/2013, H0040, 8, 3)

    it will lead out the third one since it is outside of the authorization period.
    That worked! Thanks, now one last trivial question, that I should know (it has been a long time since I wrote code). Now that I have this information, how do I display it with the patient id, since it is not apart of the query itself or the aggregate function since Group by is used?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by dennisdj
    ... how do I display it with the patient id
    add the patient id to both the SELECT clause and the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    26
    Rep Power
    0
    Thanks, I was not putting it in the Group By clause!

IMN logo majestic logo threadwatch logo seochat tools logo