October 4, 2011 at 9:13 am
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.
October 4, 2011 at 9:53 am
not sure what you mean by elimination, nor what data you wanted at the end;
to me it looks like you have to join the table agaisnt itself...
is this right?
/*
--Results
organization account interco currency amount organization account interco currency amount
------------ ------- ------- -------- ------ ------------ ------- ------- -------- ------
a1 AR b1 USD 10 b1 AP a1 USD 15
a1 AR b1 USD 10 b1 AP a2 USD 18
*/
With mySampleData(organization,account,interco,currency,amount)
AS
(
SELECT 'a1','AR','b1','USD','10' UNION ALL
SELECT 'a1','AR','b2','USD','11' UNION ALL
SELECT 'a1','AR','b3','USD','12' UNION ALL
SELECT 'b1','AP','a1','USD','15' UNION ALL
SELECT 'b1','AP','a2','USD','18' UNION ALL
SELECT 'c1','AR','a1','USD','20'
)
SELECT
T1.organization,
T1.account,
T1.interco,
T1.currency,
T1.amount,
T2.organization,
T2.account,
T2.interco,
T2.currency,
T2.amount
FROM mySampleData T1
INNER JOIN mySampleData T2
ON T1.interco = T2.organization
WHERE T1.account='AR'
AND T2.account='AP'
Lowell
October 4, 2011 at 5:24 pm
You could please try it :
T1.organization,
T1.account,
T1.interco,
T1.currency,
T1.amount
FROM mytable T1 INNER JOIN mytable t2 on T1.organizatio= T2.interco AND T2.organizatio= T1.interco
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 4, 2011 at 9:23 pm
Hi Lowell,
Join the table might be an option but the joined results shown by you is not quite correct.
--Results--
organization account interco currency amount organization account interco currency amount
------------ ------- ------- -------- ------ ------------ ------- ------- -------- ------
a1 AR b1 USD 10 b1 AP a1 USD 15
a1 AR b1 USD 10 b1 AP a2 USD 18
-----------
Based on your initial results, the first record is correct since it display the interco transaction between company a1 and b1 and vice versa and consists of AR and AP accounts. So, the elimination value will be -5 USD. The second row is not correct as it involves 3 companies; a1, a2 and b1.
Hope you get what I mean.
October 6, 2011 at 12:57 am
It is not exactly what you asked for, but isn't this what you meant to get? This gives you the total of what every company needs to pay to the other companies at the end of the month. (Thank you for the sample data Lowell).
With mySampleData(organization,account,interco,currency,amount)
AS
(
SELECT 'a1','AR','b1','USD',10 UNION ALL
SELECT 'a1','AR','b2','USD',11 UNION ALL
SELECT 'a1','AR','b3','USD',12 UNION ALL
SELECT 'b1','AP','a1','USD',15 UNION ALL
SELECT 'a1','AP','b1','USD',15 UNION ALL
SELECT 'b1','AP','a2','USD',18 UNION ALL
--SELECT 'a1','AR','c1','USD',13 UNION ALL
--SELECT 'c1','AP','a1','USD',7 UNION ALL
SELECT 'c1','AR','a1','USD',20
)
select case when t.amount > 0 then t.organization else t.interco end as organization,
'AP' as account,
case when t.amount > 0 then t.interco else t.organization end as interco,
t.currency,
abs(t.amount) as amount
from (
select case when organization > interco then organization else interco end as organization,
case when organization > interco then interco else organization end as interco,
currency,
sum(
case when organization > interco then -1 else 1 end *
case account when 'AR' then 1 else -1 end *
amount
) as amount
from MySampleData
group by case when organization > interco then organization else interco end,
case when organization > interco then interco else organization end,
currency
) t
where t.amount <> 0
order by 1, 3, 4
output:
organization account interco currency amount
------------ ------- ------- -------- -----------
a1 AP c1 USD 20
b1 AP a1 USD 10
b1 AP a2 USD 18
b2 AP a1 USD 11
b3 AP a1 USD 12
(5 row(s) affected)
Richard Rozema
edit: added output
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply