Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2014
    Posts
    18

    Question Unanswered: Sum not working as intended

    Greetings!

    I'm trying to create a query showing amount of court costs spent, court costs recovered, net court costs (difference between two previous columns), and sum of total interest received from each attorney's office we use.

    Attorney name, court costs (CC) spent, CC recovered, and net CC are all working correctly, however court costs paid/spent are stored in one table (legal_ledger) and interest received is stored in another (payhistory). Here's the query I'm currently working with:

    Code:
    SELECT
    	attorney.Name AS Attorney,
    	SUM(legal_ledger.debitamt)*(-1) AS Spent,
    	SUM(legal_ledger.creditamt) AS Recovered,
    	(SUM(legal_ledger.debitamt)*(-1))+SUM(Legal_Ledger.CreditAmt) AS NetCC,
    	(SELECT SUM(paid9) FROM payhistory
    		WHERE
    			datepaid BETWEEN '02/01/2012' AND '02/28/2012') AS Interest
    FROM
    	Legal_Ledger
    	INNER JOIN master ON Legal_Ledger.AccountID = master.number
    	INNER JOIN attorney ON master.AttorneyID = attorney.AttorneyId
    	INNER JOIN payhistory ON payhistory.number = Legal_Ledger.AccountID
    WHERE 
    	ItemDate BETWEEN '02/01/2012' AND '02/28/2012'
    GROUP BY attorney.Name
    This query gives me the name of the attorney, CC Spent, CC Recovered, and Net CC, however the problem is with the interest column. I want the interest column to show the amount of interest received from each of the attorneys' offices individually, however instead it shows the same dollar amount for all attorneys when I run the query above. When I query the interest for each of the attorneys individually, I find one of the attorneys had interest posted, and the rest had $0.00 (which is accurate). It appears the interest column is querying total interest paid for all attorneys and returning that total for each individual attorney.

    I can get the correct interest amounts by running this query:

    Code:
    SELECT attorney.Name, SUM(payhistory.paid9) 
    FROM payhistory
    INNER JOIN attorney ON payhistory.AttorneyID = attorney.AttorneyId
    WHERE 
    	datepaid BETWEEN '02/01/2012' AND '02/28/2012'
    GROUP BY attorney.Name
    ... which returns correct data except when I put it in the larger query above.

    Does anyone have any ideas? I am using MS SQL Server 2008. Version info:

    Microsoft SQL Server Management Studio - 10.0.5500.0
    Microsoft Analysis Services Client Tools - 10.0.5500.0
    Microsoft Data Access Components (MDAC) - 6.1.7601.17514
    Microsoft MSXML - 3.0 4.0 6.0
    Microsoft Internet Explorer - 8.0.7601.17514
    Microsoft .NET Framework - 2.0.50727.5466
    Operating System - 6.1.7601


    Any advice would be most appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT
       attorney.Name AS Attorney
    ,  SUM(legal_ledger.debitamt)*(-1) AS Spent
    ,  SUM(legal_ledger.creditamt) AS Recovered
    ,  (SUM(legal_ledger.debitamt)*(-1))+SUM(Legal_Ledger.CreditAmt) AS NetCC
    ,  (SELECT SUM(paid9) FROM payhistory
          WHERE  payhistory.AttorneyID = attorney.attorneyID
    	     AND datepaid BETWEEN '02/01/2012' AND '02/28/2012') AS Interest
       FROM
          Legal_Ledger
          INNER JOIN master
    	     ON Legal_Ledger.AccountID = master.number
          INNER JOIN attorney
    	     ON master.AttorneyID = attorney.AttorneyId
          INNER JOIN payhistory
    	     ON payhistory.number = Legal_Ledger.AccountID
       WHERE  ItemDate BETWEEN '02/01/2012' AND '02/28/2012'
       GROUP BY attorney.Name
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2014
    Posts
    18
    So many criss-crosses in this query. I figured it was something simple that I was missing!

    Thank you so very much!

  4. #4
    Join Date
    Mar 2014
    Posts
    18

    Question Subquery Help

    Does anyone know how I might change the subqueries such that payhistory.datepaid references the same date range as legal_ledger.itemdate shown in the WHERE clause in the outer query? Ultimately I want to run this with only one date range input if at all possible.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I followed up in your other thread.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Pat Phelan View Post
    Code:
    SELECT
       attorney.Name AS Attorney
    ,  SUM(legal_ledger.debitamt)*(-1) AS Spent
    ,  SUM(legal_ledger.creditamt) AS Recovered
    ,  (SUM(legal_ledger.debitamt)*(-1))+SUM(Legal_Ledger.CreditAmt) AS NetCC
    ,  (SELECT SUM(paid9) FROM payhistory
          WHERE  payhistory.AttorneyID = attorney.attorneyID
    	     AND datepaid BETWEEN '02/01/2012' AND '02/28/2012') AS Interest
       FROM
          Legal_Ledger
          INNER JOIN master
    	     ON Legal_Ledger.AccountID = master.number
          INNER JOIN attorney
    	     ON master.AttorneyID = attorney.AttorneyId
          INNER JOIN payhistory
    	     ON payhistory.number = Legal_Ledger.AccountID
       WHERE  ItemDate BETWEEN '02/01/2012' AND '02/28/2012'
       GROUP BY attorney.Name
    -PatP
    I wonder why joining payhistory was neccesary?
    Because, the payhistory was used in scalar subquery in select clause.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing the full schema, I can't say why payhistory was joined at the outermost level and as a subquery. I assumed that the join served to limit the rows returned and that the subquery was for a different period. If Tonkuma's assumption is correct and both instances of payhistory are semantically the same, then the whole query needs to be reviewed by someone with access to the entire schema to ensure that there is no missing or superfluous data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Mar 2014
    Posts
    18
    Quote Originally Posted by Pat Phelan View Post
    Without knowing the full schema, I can't say why payhistory was joined at the outermost level and as a subquery. I assumed that the join served to limit the rows returned and that the subquery was for a different period. If Tonkuma's assumption is correct and both instances of payhistory are semantically the same, then the whole query needs to be reviewed by someone with access to the entire schema to ensure that there is no missing or superfluous data.

    -PatP
    Actually, the outermost payhistory join was left over from earlier attempts at the same query.... I'm still very new to querying so learning as I go. Maybe one day I'll be good enough to help others with their questions.

    After reading Tonkuma's post, I commented out that last join and the query worked without issue so I have since removed it.

    Now the query works as expected however it is not including costs spent or received on cases that have been unassigned from the attorney. I would expect this query to show one line in the results where attorney name is NULL... but it doesn't. It just skews the resulting data by omitting those costs.

    Any ideas how I could ensure those are in the results?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Consider this to be purely experimental, but try it and see if the results are correct:
    Code:
    SELECT
       attorney.Name AS Attorney
    ,  SUM(legal_ledger.debitamt)*(-1) AS Spent
    ,  SUM(legal_ledger.creditamt) AS Recovered
    ,  Coalesce(SUM(Legal_Ledger.CreditAmt), 0)
    -     Coalesce(SUM(legal_ledger.debitamt), 0) AS NetCC
    ,  SUM(payhistory.paid9) AS Interest
       FROM
          Legal_Ledger
          INNER JOIN master
    	     ON  master.number = Legal_Ledger.AccountID
          LEFT JOIN payhistory
    	     ON payhistory.number = Legal_Ledger.AccountID
          LEFT JOIN attorney
    	     ON  attorney.AttorneyId = master.AttorneyID
       WHERE  Legal_Ledger.ItemDate BETWEEN '02/01/2012' AND '02/28/2012'
          AND payhistory.datepaid   BETWEEN '02/01/2012' AND '02/28/2012'
       GROUP BY attorney.Name
       ORDER BY attorney.Name
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Mar 2014
    Posts
    18
    This is definitely closer to what I need, and I think it gets me on the right track.

    When I ran this query, it returned a list of some of the attorneys (not all, oddly enough) and it included a row with NULL in the attorney name. The NULL attorney row figures did not include all the figures I would expect to see. Dollar amount there is still short. Dollar amounts for the other attorneys listed int he query were significantly short as well.

    I'm puzzled why some of the attorneys would have been excluded from the query results, though. Very odd!

    I have not used COALESCE before, so this will be quite helpful, but I suspect COALESCE needs to be in a different place in the query since it's master.attorneyID that throws the query off with NULL values.

    I tried adding this to the WHERE clause:

    Code:
          AND ((master.AttorneyID IS NOT NULL) OR (master.AttorneyID IS NULL AND (Legal_Ledger.DebitAmt>0 OR Legal_ledger.creditamt>0)))
    ... but didn't appear to have any effect. Not sure how to use COALESCE to include values from legal_ledger when attorneyID is null.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ooof! Brain fart on my part! Please try:
    Code:
    DECLARE  @Start     DATETIME = '2012-02-01'
    DECLARE  @Stop      DATETIME = DateAdd(month, 1, @Start)
    
    SELECT
       attorney.Name AS Attorney
    ,  SUM(legal_ledger.debitamt)*(-1) AS Spent
    ,  SUM(legal_ledger.creditamt) AS Recovered
    ,  Coalesce(SUM(Legal_Ledger.CreditAmt), 0)
    -     Coalesce(SUM(legal_ledger.debitamt), 0) AS NetCC
    ,  SUM(payhistory.paid9) AS Interest
       FROM
          Legal_Ledger
          INNER JOIN master
    	     ON  master.number = Legal_Ledger.AccountID
          LEFT JOIN payhistory
    	     ON payhistory.number = Legal_Ledger.AccountID
          LEFT JOIN attorney
    	     ON  attorney.AttorneyId = master.AttorneyID
       WHERE  ( @Start <= Legal_Ledger.ItemDate
                 AND Legal_Ledger.ItemDate < @stop)
          AND (  payhistory.datepaid IS NULL
             OR  @Start <= payhistory.datepaid
             AND payhistory.datepaid < @Stop)
       GROUP BY attorney.Name
       ORDER BY attorney.Name
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Based on this comment:
    I tried adding this to the WHERE clause:


    Code:
    AND ((master.AttorneyID IS NOT NULL) OR (master.AttorneyID IS NULL AND (Legal_Ledger.DebitAmt>0 OR Legal_ledger.creditamt>0)))... but didn't appear to have any effect. Not sure how to use COALESCE to include values from legal_ledger when attorneyID is null.
    Should you have a left outer join to the Master table? I would think that all attornets in the Legal_Ledger would have to first exist in the Master table, but I have gotten in trouble by thinking in the past. Guess you have to listen to Yoda.
    Dave

Posting Permissions

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