construct interco elim query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

construct interco elim query

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2011-10-04 : 11:08:16

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 11:30:01

please explain us whats logic that you applied to get the output from posted sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yingchai
Starting Member

33 Posts

Posted - 2011-10-04 : 11:39:37

Hi visakh16,

I use the following logic:
Select * from table
where organization in ('a1','b1')
and interco in ('a1','b1')

But in reality, my source interco details table contains hundreds of organization code with many interco transactions. I believe my query above is not so flexible enough as it is quite rigid in a sense...

The logic to display the records is by following the 2 rules below:
1. from organization to interco which contains AR account
2. from interco to organization which contains AP account
or
1. from organization to interco which contains AP account
2. from interco to organization which contains AR account

p/s: the problem I posted is more on accounting scenario where we need to prepare interco elimination :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 11:58:51

[code]
SELECT val1,val2 INTO #Temp
FROM
(
SELECT organization as val1,interco as val2, account ,1 AS rev
FROM table
UNION ALL
SELECT interco,organization,account ,2
FROM table
)t
WHERE account in ('AR','AP')
GROUP BY val1,val2
HAVING COUNT(DISTINCT rev)=2
AND COUNT(DISTINCT account)=2

SELECT t.*
FROM table t
INNER JOIN #Temp tmp1
On ((tmp1.val1= t.organisation
AND tmp1.val2 = t.interco )
OR (tmp1.val2= t.organisation
AND tmp1.val1 = t.interco ))
AND t.account IN ('AR','AP')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources