Calculate Difference

Sorry, I know that I'm very troublesome
I will like to create a view that stores the difference between 2 Integer
columns from 2 different table. Is there a function to perform this?
And I wrote a SQL statement as below,
Select CustID, ReqNo, 'Total'=Sum(AcceptedQty)
From Delivery
Group By CustID, ReqNo
Will this return me the total accepted quantity of each combination of
custid and reqno? It's working fine currently, but I just need a
confirmation~~
Thank you.Hi
You post is not clear what you are trying to achieve check out
http://www.aspfaq.com/etiquett_e.asp?id=5006 on how to post.
To use two tables you will need to use a JOIN. You can find out more about
JOINs in books online for example the "Join Fundamentals" topic or at
http://msdn.microsoft.com/library/d...r />
_610z.asp
Select O.CustID, O.ReqNo, Sum(O.OrderedQty) AS [TotalOrdered],
ISNULL(Sum(D.AcceptedQty),0) AS [TotalDelivered], Sum(O.OrderedQty) -
ISNULL(SUM(D.AcceptedQty),0) AS [Outstanding]
From Orders O
LEFT JOIN Delivery D O.CustId = D.CustID AND O.ReqNo = D.ReqNo
Group By O.CustID, O.ReqNo
As Orders may not have been delivered the the Delivered tables is joined
using using an outer join. The use of ISNULL makes the sum 0 if there are no
entries in delivery.
John
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:75378B2B-8975-47A0-B069-3A84D8819699@.microsoft.com...
> Sorry, I know that I'm very troublesome
> I will like to create a view that stores the difference between 2 Integer
> columns from 2 different table. Is there a function to perform this?
> And I wrote a SQL statement as below,
> Select CustID, ReqNo, 'Total'=Sum(AcceptedQty)
> From Delivery
> Group By CustID, ReqNo
> Will this return me the total accepted quantity of each combination of
> custid and reqno? It's working fine currently, but I just need a
> confirmation~~
> Thank you.|||Well a View will not "store" anything...
If you simply want to calculate and display the difference between two
columns in two different tables, use a join, but you have to tell SQL which
row(s) in each table to get the column valuesfrom, and, if there are ore tha
n
one row in each table, which one on tableA should be used to subtract from
which one in Table B. i.e., how to "Join" the two tables...
Assuming you know how to Join the 2 tables
Just refer to the two columns with their TableNames...
Select TableA.ColName - TableB.ColumnName
From TableA Join TableB
In TableB.ForeignKeyColumn = TableA.PrimaryKeyColumn
"wrytat" wrote:

> Sorry, I know that I'm very troublesome
> I will like to create a view that stores the difference between 2 Integer
> columns from 2 different table. Is there a function to perform this?
> And I wrote a SQL statement as below,
> Select CustID, ReqNo, 'Total'=Sum(AcceptedQty)
> From Delivery
> Group By CustID, ReqNo
> Will this return me the total accepted quantity of each combination of
> custid and reqno? It's working fine currently, but I just need a
> confirmation~~
> Thank you.|||Thank you for your directions and Sorry... I'll try to be clearer...
Ok, let's say the total quantity of product we delivered (Delivered
Quantity) to our customer is less than or more than the quantity the custome
r
requested (Requested Quantity). If the Total Delivered Quantity (as goods ca
n
be delivered in more than 1 trip) is more than Requested Quantity minus the
Allowance (i.e. the quantity of shortage or excess that is still acceptable
to the customer), the order is considered completed.
I have 3 tables that stores these three information:
The Product table, that stores details of each product, contains the
Allowance of the product (as Allowance). The Primary Keys are CustID and
Comp# (component number).
The DelReq table, that stores the details of each delivery order requested
by customers, keeps the Requested Quantity (as ReqQty). The Primary Keys are
CustID and ReqNo (Request ID).
The DelDetail table, that stores the details of the outcome of each delivery
trip, keeps the Delivered Quantity (as AcceptedQty). The Primary Keys are
CustID, ReqNo, SC# (Sales Confirmation Number), and Item# (different number
for each delivery).
So now, I want to do a processing to find out which delivery requests have
already been completed and then push them into an archive. Firstly, I need t
o
find out the total Delivered Quantity of each request. And I also need to
find out the difference between the Requested Quantity and the Allowance, so
that finally I can compare the total Delivered Quantity with the difference.
Thus, I thought of this:
Create View VW_TotalAccepted
AS
SELECT DelDetail.CustID, DelDetail.ReqNo, Sum(DelDetail.AcceptedQty) AS
[Total], (DelReq.ReqQty-Product.Allowance) AS [AcceptableQty]
FROM DelDetail, DelReq, Product
GROUP BY DelDetail.CustID, DelDetail.ReqNo
WHERE DelDetail.CustID = DelReq.CustID AND
DelDetail.ReqNo = DelReq.ReqNo AND
Product.CustID = DelReq.CustID AND
Product.Comp# = DelReq.Comp#
I know this is a bit complicated and I'm not very good in explaining the
problem. Sorry.
I'm not sure whether this is right, and will there be any problem with the
efficiency and performance of the server. Will the above help me achieve wha
t
I want?
And is using JOIN better than specifying join condition in the WHERE clause?
If so, I have to join 3 tables...
Create View VW_TotalAccepted
AS
SELECT DelDetail.CustID, DelDetail.ReqNo, Sum(DelDetail.AcceptedQty) AS
[Total], (DelReq.ReqQty-Product.Allowance) AS [AcceptableQty]
FROM DelReq JOIN DelDetail ON
(DelDetail.CustID = DelReq.CustID
AND DelDetail.ReqNo = DelReq.ReqNo),
DelReq JOIN Product ON
(Product.CustID = DelReq.CustID
AND Product.Comp# = DelReq.Comp#)
GROUP BY DelDetail.CustID, DelDetail.ReqNo
Is this correct? Looks strange...
>
>