Calculation on one query over another

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

General SQL Server Forums

New to SQL Server Programming

Calculation on one query over another

Author

Topic

runnerpaul
Starting Member

24 Posts

Posted - 2012-09-28 : 05:32:01

Hi,

Is it possible to do a calculation on one query result over another?

I have these queries:

select b.purchid, c.purchname, c.orderaccount, sum(a.amountmst), a.dimension
from ledgertrans a
inner join vendpackingslipjour b on a.voucher = b.ledgervoucher
inner join purchtable c on b.purchid = c.purchid
where 
a.accountnum = '34050'
and a.dataareaid = 'agl'
and b.dataareaid = 'agl'
and a.voucher like 'GRN-%'
and transdate < '2012-04-01'
and b.purchid = 'PO-000662'
group by b.purchid, c.purchname, c.orderaccount, a.dimension

and

select b.purchid, c.purchname, c.orderaccount, sum(a.amountmst), a.dimension
from ledgertrans a
inner join vendinvoicejour b on a.voucher = b.ledgervoucher
inner join purchtable c on b.purchid = c.purchid
where 
a.accountnum = '34050'
and a.dataareaid = 'agl'
and b.dataareaid = 'agl'
and a.voucher like 'PI-%'
and transdate < '2012-04-01'
and b.purchid = 'PO-000662'
group by b.purchid, c.purchname, c.orderaccount, a.dimension

The first query produces:

quote:
purchid purchname orderaccount amount dimension
PO-000662 POD ODO001 -45444.870000000000 BEL
PO-000662 POD ODO001 -81151.510000000000 FMT

The second produces:

purchid		purchname	orderaccount	amount			dimension
PO-000662	POD		ODO001		45444.870000000000	BEL
PO-000662	POD		ODO001		80363.180000000000	FMT

I would like to add or subtract the amount from query 2 to that of query 1 for each purchid. In this case I would expect the result to be:

quote:
purchid purchname orderaccount amount dimension
PO-000662 POD ODO001 0.000000000000 BEL
PO-000662 POD ODO001 -788.330000000000 FMT

Once I figure out how to do this I would intend to remove and b.purchid = 'PO-000662' from each query so this is performed for all purchases.

Cheers
Paul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-28 : 05:51:41

[code]; with
q1 as
(
< query 1 >
),
q2 as
(
< query 2 >
)
select < do you calculation here example : q1.col1 - q2.col2 >
from q1 inner join q2 on q1.purchid = q2.purchid [/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 10:32:37

[code]
select b.purchid, c.purchname, c.orderaccount, sum(a.amountmst), a.dimension
from ledgertrans a
inner join (
select purchid,ledgervoucher
from vendinvoicejour
where dataareaid = 'agl'
union all
select purchid,ledgervoucher
from vendpackingslipjour
where dataareaid = 'agl'
)b on a.voucher = b.ledgervoucher
inner join purchtable c on b.purchid = c.purchid
where a.accountnum = '34050'
and a.dataareaid = 'agl'
and a.voucher like 'PI-%'
and transdate < '2012-04-01'
and b.purchid = 'PO-000662'
group by b.purchid, c.purchname, c.orderaccount, a.dimension
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources