Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:36 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.