Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 11th, 2005, 02:08 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to do calculation between 2 tables

I need to minus the dollar amount in Table A from Table B when they meet the criteria. Can T-SQL do this function?

For Example, when Year, MonthID, AcNo and Pid in Table A are the same as those in Table B, the Amt in Table A needs to minus the Amt in Table B.

Table A
Year MonthID AcNo Pid Amt
2002 1 1001 1 2000
2002 1 1001 2 2500
2002 1 1001 3 3000

Table B
Year MonthID AcNo Pid Amt
2002 1 1001 1 200
2002 1 1001 2 500
2002 1 1001 3 30
  #2 (permalink)  
Old January 11th, 2005, 04:46 AM
Friend of Wrox
 
Join Date: Dec 2004
Location: Chennai, Tamil nadu, India.
Posts: 307
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Vadivel Send a message via Yahoo to Vadivel
Default

Though I am not clear with your requirement .. I guess Triggers can be of use to you.

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com
  #3 (permalink)  
Old January 13th, 2005, 09:29 PM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Rough Guidelines:

For existing data run a query something like:
UPDATE Table1
SET Table1.Amount = Table1.Amount - Table2.Amount
FROM Table1 INNER JOIN Table2
ON Table1.PID = Table2.PID
AND Table1.[Year] = Table2.[Year]
AND Table1.MonthID = Table2.MonthID
AND Table1.AcNo = Table2.AcNo
WHERE Table2.Amount IS NOT NULL

For future use a Trigger something like:
CREATE Trigger After_IU_Update_T1_From_T2
ON Table2 AFTER Insert,Update
AS IF Update(Amount)
    UPDATE Table1
    SET Table1.Amount = Table1.Amount - inserted.Amount
    FROM Table1 INNER JOIN inserted
    ON Table1.PID = inserted.PID
    AND Table1.[Year] = inserted.[Year]
    AND Table1.MonthID = inserted.MonthID
    AND Table1.AcNo = inserted.AcNo
WHERE inserted.Amount IS NOT NULL


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculation in asp mateenmohd Classic ASP Basics 4 May 2nd, 2005 03:11 AM
Calculation Grantm Access 3 February 16th, 2004 10:14 AM
calculation in VB semooth Beginning VB 6 1 December 10th, 2003 09:07 AM
calculation in VB semooth VB Databases Basics 4 October 6th, 2003 11:19 PM
calculation in VB semooth Access VBA 1 October 3rd, 2003 05:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.