get the difference of two attributes in two tables
-
I have a table called "Issue" and table called "Receive" and both have same attributes they are "code,curr_val,date,coin_note,no_of_items,amount,operator_id,officer_id"And also code ,curr_val, and date are superkeys. data types are char(8),datetime,char(1)if t is a coin c,if it is a note n,money,char(8),char(8) I'm using sql server 2000 I want to get the difference of currency values (ex no of $10notes,$50 notes).In other words I want to get the difference of Items in the "no_of items"attribute. I tried toget notes value and no of notes items in receive table. >SELECT curr_val,count(*)AS Items ,SUM (amount)AS Amount_Rs FROM RECEIVE AS R WHERE coin_note='n'GrOUP BY curr_val I tried toget notes values and no of notes items in issue table >SELECT curr_val,count(*)AS Items ,SUM (amount)AS Amount_Rs FROM ISSUE AS I WHERE coin_note='n'GrOUP BY curr_val . What I want is the difference betveen the items values of R and I with their currency value please help ASAP Thanx Thilani
-
I have a table called "Issue" and table called "Receive" and both have same attributes they are "code,curr_val,date,coin_note,no_of_items,amount,operator_id,officer_id"And also code ,curr_val, and date are superkeys. data types are char(8),datetime,char(1)if t is a coin c,if it is a note n,money,char(8),char(8) I'm using sql server 2000 I want to get the difference of currency values (ex no of $10notes,$50 notes).In other words I want to get the difference of Items in the "no_of items"attribute. I tried toget notes value and no of notes items in receive table. >SELECT curr_val,count(*)AS Items ,SUM (amount)AS Amount_Rs FROM RECEIVE AS R WHERE coin_note='n'GrOUP BY curr_val I tried toget notes values and no of notes items in issue table >SELECT curr_val,count(*)AS Items ,SUM (amount)AS Amount_Rs FROM ISSUE AS I WHERE coin_note='n'GrOUP BY curr_val . What I want is the difference betveen the items values of R and I with their currency value please help ASAP Thanx Thilani
00thilani wrote:
both have same attributes
Attributes? Do you mean "columns"?
00thilani wrote:
superkeys
I am unfamiliar with that term. Do you mean "primary key"? Or perhaps "foreign key"? Perhaps this will work:
SELECT
curr_val,
Items_Rs - Items_Is AS items_difference,
Amount_Rs - Amount_Is AS difference_amount
FROM (
SELECT curr_val, count(*) AS Items_Rs, SUM(amount) AS Amount_Rs
FROM RECEIVE
WHERE coin_note='n'
GROUP BY curr_val
) AS Received
INNER JOIN (
SELECT curr_val, count(*) AS Items_Is, SUM(amount) AS Amount_Is
FROM ISSUE
WHERE coin_note='n'
GROUP BY curr_val
) AS Issued
ON Issued.curr_val = Received.curr_val
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog