Results 1 to 13 of 13

Thread: Subquery Help

  1. #1
    Join Date
    Mar 2014
    Posts
    18

    Question Unanswered: Subquery Help

    Trying to re-work the query below such that the date range in payhistory.datepaid used in the inner queries references the same date range as legal_ledger.datepaid in the outer query. Would like to accomplish this query with just one date range input. Any ideas?

    Code:
    SELECT
       attorney.Name AS Attorney,
       attorney.State AS State,
      SUM(legal_ledger.debitamt)*(-1) AS 'CC Spent',
      SUM(legal_ledger.creditamt) AS 'CC Recovered',
      (SUM(legal_ledger.debitamt)*(-1)) + SUM(Legal_Ledger.CreditAmt) AS 'CC Net Only',
      (SELECT SUM(paid9) FROM payhistory
          WHERE  payhistory.AttorneyID = attorney.attorneyID
    	     AND datepaid BETWEEN '02/01/2012' AND '02/28/2012'
    	     ) AS 'Int Recovered',
    	((SUM(legal_ledger.debitamt)*(-1)) + SUM(Legal_Ledger.CreditAmt) + 
    	((SELECT SUM(paid9) FROM payhistory
          WHERE  payhistory.AttorneyID = attorney.attorneyID
    	     AND datepaid BETWEEN '02/01/2012' AND '02/28/2012'))) AS 'Net Total'
       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, attorney.AttorneyId, attorney.State
       ORDER BY attorney.State, attorney.Name

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One solution would be to make the query a table valued function with the dates as arguments.

    Another solution would be to create another table that would contain the date ranges and join that into your existing query instead of using the date constants.

    Keep in mind that if your legal_ledger.ItemDate column has a DATE datatype, then things will behave as you expect. If the column is either a DATETIME or DATETIME2, then you'll miss all of the data after 2012-02-28 00:00:01.001 because it is greater than 2012-02-28. This is not what most people expect when they first try to write date driven queries.

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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How's this?
    Code:
    DECLARE @start_date date = '2012-02-01'
          , @end_date   date
    ;
    
    SET @end_date = DateAdd(mm, 1, @start_date);
    
    SELECT attornetid
         , attoryney
         , "state"
         , cc_spent
         , cc_recovered
         , cc_spent + cc_recovered As cc_net_only
         , int_recovered
         , cc_spent + cc_recovered + int_recovered As net_total
    FROM   (
            SELECT attorney.attorneyid
                 , attorney.name Aa attorney
                 , attorney."state" As "state"
                 , Coalesce(attorney_payhistory.int_recovered, 0) As int_recovered
                 , Sum(legal_ledger.debitamt) * -1 As cc_spent
                 , Sum(legal_ledger.creditamt) As cc_recovered
            FROM   legal_ledger
             INNER
              JOIN master
                ON master.number = legal_ledger.accountid
             INNER
              JOIN attorney
                ON attorney.attorneyid = master.attorneyid
             INNER
              JOIN payhistory
                ON payhistory.number = legal_ledger.accountid
             LEFT
              JOIN (
                    SELECT attorneyid
                         , Sum(paid9) As int_recovered
                    FROM   payhistory
                    WHERE  datepaid >= @start_date
                    AND    datepaid <  @end_date
                    GROUP
                        BY attorneyid
                   ) As attorney_payhistory
                ON attorney_payhistory.attorneyid = attorney.attorneyid
            WHERE  itemdate >= @start_date
            AND    itemdate <  @end_date
            GROUP
                BY attorney.attorneyid
                 , attorney.name
                 , attorney."state"
                 , attorney_payhistory.int_recovered
          ) As another_subquery
    ORDER
        BY "state"
         , name
    ;
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2014
    Posts
    18
    Thank you all so much! This is extremely helpful.

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    As gvee showed, whenever I have scripts that need to reference the same value (such as a date or multiplier or some factor) multiple times and I need that value easily changeable, I use a variable. That way you only have to change it in one place.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You failed completely. Temporal data should use ISO-8601 formats. You failed again. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on SQL forums.

    You did not know that SQL has a monadic minus. You did not know to use double quotes to name results. And you used the term “Master” in RDBMS? No, that is mag tapes and network database terminology. Why do you think “number” is a clear, precise data element name which can change by magic into a different data element?

    Putting scalar subqueries in a SELECT list will destroy performance. It cannot be optimized, even in theory.

    We do not put debits and credits in separate columns; that was how they did it in Renaissance Italy when they had no zero or concept of negative numbers.

    The (corrected) expression “SUM(L.credit_amt) - SUM(L.debit_amt) AS cc_net_only_tot” should have been done in the presentation layer. Remember the basic concepts of an tiered architecture?

    But even this wrong!

    We do not put debits and credits in separate columns; that was how they did it in Renaissance Italy when they had no zero or concept of negative numbers.

    You have serious design problems which will destroy performance. I made guesses, but you are not paying me and other people do.

    Want to try again? Do you have the power to throw out this garbage? Here is whewre I stopped to go to bed:

    SELECT A.attorney_name, A.state_code,
    -SUM(L.debit_amt) AS cc_spent_tot, -- no
    SUM(L.credit_amt) AS cc_recovery_tot, -- no
    (SELECT SUM(paid9) FROM Pay_History –- total nightmare
    WHERE P.attorney_id = A.attorney_id
    AND payment_date BETWEEN '2012-02-01' AND '2012-02-28' ) AS something_recovery_amt_tot,

    FROM Legal_Ledger AS L
    Master AS M, --- non-RDBMS!
    Attorneys AS A,
    Pay_History AS P,
    WHERE item_date BETWEEN '2012-02-01' AND '2012-02-28'
    AND L.account_nbr = M.account_nbr
    AND M.attorney_id = A.attorney_id
    AND P.account_nbr = L.account_nbr
    GROUP BY A.attorney_name, A.attorney_id, A.state_code;

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'll take issue with several of Celko's points.

    Quote Originally Posted by Celko View Post
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You failed completely. Temporal data should use ISO-8601 formats. You failed again. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on SQL forums.
    First and most important, you're making unwarranted assumptions about the reader... While your expectations make perfect sense for a student in one of your classes (which are usually post-graduate), they are completely unfounded in a public place like DBForums. Just like you wouldn't expect someone starting their education in grade school to behave the same way as someone extending their expertise with post graduate work, you can't expect a newcomer to databases and SQL to have or use the skills that normally take years to teach.

    Quote Originally Posted by Celko View Post
    ...And you used the term “Master” in RDBMS? No, that is mag tapes and network database terminology. Why do you think “number” is a clear, precise data element name which can change by magic into a different data element?
    In SQL Server, a name is a name... There are a fixed set of rules that are required for Database Identifiers. While your observations do make sense to me and I agree with the sentiment, those are personal preferences but they aren't requirements of the language.

    Quote Originally Posted by Celko View Post
    We do not put debits and credits in separate columns; that was how they did it in Renaissance Italy when they had no zero or concept of negative numbers.
    You are a great mathematician, and to a mathematician a debit is just a negative credit so your comment makes sense. I worked with accountants for decades, and to a classically trained accountant debits and credits are as distinct as oranges and aircraft carriers. There are many subtleties and nuances that are carried by the debit and credit entries, and to a bookkeeper or a mere mortal it is just a bunch of numbers... To an accountant, they are much like a musical score and the differences are expressive.

    You and I agree on many points... Old war horses tend to have similar views on many subjects. This is a point where your mathematical training actually leads you astray, and causes you to give what I see as incorrect advice.

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

  8. #8
    Join Date
    Mar 2014
    Posts
    18
    You are correct, Pat. About 90% of what he said in that last message was Greek to me!

    Far as the database structure goes.... I didn't design it. No one in my company did. We use commercial software designed by their own engineers, and no one in our company regardless of education or seniority has access to change the structure of the database, which would also involved changing the source code of our main software. It's totally proprietary, and that's part of what makes my job as challenging as it is... we have to work around the wonky design structure. Yes, including the table called "master" and the field called "number". No, they're not very descriptive and I never would have named them as such.

    I really do appreciate all the help from you and so many other supportive folks on here. Learning more and more every day!

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I didn't design it. No one in my company did. We use commercial software designed by their own engineers, and no one in our company regardless of education or seniority has access to change the structure of the database..
    This is funny. Next week, I am going to Georgia to re-do a system that is even worse than this. They have used GUIDs for pointer chains and magical "id" keys. The GUIDs are kept as character strings. My favorite is the two tables used to keep "Email"; one is the email type (it is always 1, but the type table has two GUIDs) and the other has the actual email address, three GUIDs and some unused columns. But the actual email is too short and has no validation (hense, lots of garbage).
    Last edited by Celko; 03-31-14 at 11:45. Reason: typo

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Heck, if you want compare horror stories, I worked with a database once that used the FLOAT datatype as surrogate keys. Including decimals.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    You win!

    Heck, if you want compare horror stories, I worked with a database once that used the FLOAT data type as surrogate keys. Including decimals.
    That beats anything I can remember. I got GUIDs, random numbers, etc. but nothing with epsilon neighborhood problems on top of validation and verification woes.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...then there was the client that had the "no stored procedures rule"...so all their sql code was kept in XML files on a shared resource. The application would open the xml file, find the sql code it needed, and execute it on the server....most of the time.

    Because stored procedures are bad, m'kay?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    Because stored procedures are bad, m'kay?
    Extracting SQL Statements by an application, then submitting the SQL to execute on the database is a REALLY bad idea for many reasons.

    While there could be some case for doing this, I have a very fertile imagination and I can't construct any scenario where I would recommend doing this! For those readers that aren't familiar with SQL Server, blindman is poking fun at this process...

    There are reasons to code SQL into an application, and reasons to use stored procedures. Complicated business logic or frequently changing SQL Statements belong in the database as a stored procedure. Simple SQL Statements (especially when tied to the GUI) usually work better when coded into the application. If you don't have a clear answer for whether to code the SQL into the application or create a stored procedure, use stored procedures

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

Posting Permissions

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