Results 1 to 4 of 4

Thread: tricky query

  1. #1
    Join Date
    May 2008
    Posts
    5

    Unanswered: tricky query

    I am working on a query that continues to elude me. I have a report that details revenue by a "parent client" as recorded by the "child client" Right now I am able to query the individual child client revenues correctly. What I am trying to do is sum the revenue for the parent client. The problem is the revenue for a child client can be recorded for multiple sales people (effectively causing a double dipping of the revenue) Here is my current query. Can anyone help me out?

    SELECT DISTINCT
    MGI.MSTR_CLIENT,
    SUBSTR(BAL.RC_BR_YR_SX,1,18) CLIENT,
    BRI.HOME_REGION,
    BAL.PRODUCT,
    CAST(BAL.REV_PERIOD_1/1000 AS DECIMAL(13,2)) REVENUE,
    SUM(CAST(BAL.REV_PERIOD_1/1000 AS DECIMAL(13,2))) SUM_REV
    FROM
    COMM.MSTR_GROUP_INFO MGI
    LEFT JOIN COMM.BALANCE_INFO BAL
    ON MGI.REV_CLIENT = BAL.REV_CLIENT
    LEFT JOIN COMM.BRANCH_INFO BRI
    ON SUBSTR(BAL.RC_BR,11,4) = BRI.BRANCH
    WHERE
    BAL.YEAR = '2008'
    GROUP BY
    MGI.MSTR_CLIENT,
    SUBSTR(BAL.RC_BR_YR_SX,1,18),
    BRI.HOME_REGION,
    BAL.PRODUCT,
    CAST(BAL.REV_PERIOD_1/1000 AS DECIMAL(13,2))
    ORDER BY
    MGI.MSTR_CLIENT,
    SUBSTR(BAL.RC_BR_YR_SX,1,18),
    BRI.HOME_REGION,
    BAL.PRODUCT,
    CAST(BAL.REV_PERIOD_1/1000 AS DECIMAL(13,2))

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you clarify a bit what you mean by all this? Imagine that we, the readers of your post, have nu clue of what your business rules are or how your system is build up.

    Give the DLL scripts of the most important tables, with some data in it, define FK's so we can figure out how those tables relate to each other, ... Leave all non-essential tables out of it.

    For the data given, give the results you expect to receive with your query.

    I did notice this:
    Code:
    LEFT JOIN COMM.BRANCH_INFO BRI
    ON SUBSTR(BAL.RC_BR,11,4) = BRI.BRANCH
    This induces poor performance.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you say you can get all the child client data in a query correctly, so what about nesting that and joining with the parent table to give you the summation. Soemthing like:


    Select parent, sum(a.sub_total)
    from parent_table pt
    ,(select parent, sub_total
    from child
    where ........) as a
    where pt.parent = a.parent

  4. #4
    Join Date
    May 2008
    Posts
    5
    Thanks for your reply both of you. I have actually given up on this query. It seems (according to the developer) that the data is much to tricky to do what I am trying with out a very very robust query. So he is helping me out. Thanks again though.

Posting Permissions

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