Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    8

    Unanswered: construct interco elim query

    Hi,

    I have an interco details table shown below where AR means account receivables and AP means account payables:
    --------------------------------------------
    organization account interco currency amount
    a1 AR b1 USD 10
    a1 AR b2 USD 11
    a1 AR b3 USD 12
    b1 AP a1 USD 15
    b1 AP a2 USD 18
    c1 AR a1 USD 20
    --------------------------------------------

    I would like to form a dynamic query where it can display the interco elimination relation:
    --------------------------------------------
    organization account interco currency amount
    a1 AR b1 USD 10
    b1 AP a1 USD 15
    --------------------------------------------

    Can anyone here help me to construct the query? Your assistance is greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    Fallowing rules of normalization you should put your Accounts Payable and Accounts Receivable in separate tables.

    As for the Dynamic part, what do you mean? Do you want to ask a question before the query is run? If so you will need some kind of ODBC client to form the question. Is this going to be a Web App or an application programmed in something like VB, C++, or some .NET app?

    For now I will just give you a Static Query assuming:

    Field1
    a1
    Field2
    AR
    Field2
    b1
    Field4
    USD
    Field5
    10

    field names as above and that they are VarChar fields. You really should provide your schema for us to be more of a help. Here is the SQL...

    Select * From YourTableNameHere
    Where (Field1 = 'a1') or (Field3 = 'a1')
    Last edited by AceOmega; 10-04-11 at 17:28.

  3. #3
    Join Date
    Apr 2010
    Posts
    8
    Hi AceOmega,

    Sorry for the lack of info.

    There will be no question prompt to the user before the query is run.

    My logic here is to eliminate both transactions for AR and AP accounts for 2 companies.

    From my example; if there is an AR transaction between a1 and b1, then show the record which is 10 USD. Apart from that, if there is an AP transaction between b1 and a1, then show the record which is 15 USD. Then I will calculate the elimination which is -5 USD. IF one of the accounts are display, then the elimination value will be either 10 USD or 15 USD.

    But there are lots of organizations and interco jumbled up from the source table which is why I need to sort the record before calculating the elimination.

    Thanks.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't fully understand your problem. What do you mean by "interco"? "Indicators of Territorial Cohesion", "International Code of Signals", ...?

    This code will give you the result that you gave in your first post.

    I summarised all amounts for the same organization, account, interco and currency.
    It puts all corresponding AR-AP's and with the same currency on one line and calculates the "elimination".

    I have no idea what the rules for "IF one of the accounts are display, then the elimination value will be either 10 USD or 15 USD." are. Do you mean those records (organization, currency, 'AR') that cannot be linked with another record (interco, currency, 'AP')?
    Code:
    WITH CTE
    AS
    (SELECT organization, account, interco, currency, SUM(amount) as amount
    FROM #DaTable
    GROUP BY organization, account, interco, currency
    )
    SELECT T1.organization, T1.account, T1.interco, T1.currency, SUM(T1.amount) as amount,
    	T2.organization as ORG, T2.account as ACC, T2.interco as intco, T2.currency as curr, SUM(T2.amount) as amt, SUM(T1.amount - T2.amount) as elemination
    FROM CTE as T1
    	INNER JOIN CTE as T2 ON
    		T1.interco = T2.organization AND
    		T1.organization = T2.interco AND
    		T1.currency = T2.currency AND
    		T1.account = 'AR' AND
    		T2.account = 'AP'
    GROUP BY T1.organization, T1.account, T1.interco, T1.currency, 
    	T2.organization, T2.account, T2.interco, T2.currency
    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

Posting Permissions

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