SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculation on one query over another
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

runnerpaul
Starting Member

24 Posts

Posted - 09/28/2012 :  05:32:01  Show Profile  Reply with Quote
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

Edited by - runnerpaul on 09/28/2012 05:34:06

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 09/28/2012 :  05:51:41  Show Profile  Reply with Quote
; 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



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/28/2012 :  10:32:37  Show Profile  Reply with Quote

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


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000