arithmetics - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

arithmetics

Author

Topic

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-04 : 22:46:31

Hi,
Im currently having three tables, "ALD","Daily" and "Changes".
In ALD i have columns "TesterID, EngTime and Product".
In Daily i have columns "TesterID,Hours_given".
In Changes i have columns "TesterID,Delta"
The TesterID for the three tables are the same.

What i want to do is, I need to grab the Hours_given AND TesterID from Daiy and substract Engtime from ALD and display the results in Delta according to the respective TesterID.

Example

TesterID Hours_given EngTime Delta
abc 2 3 -1
fgt 0.6 0.4 0.2

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-04 : 22:52:24

[Code]
--Test the results
Select daily.TesterID, hours_given, engtime, hours_given-engtime as delta
FROM daily inner join ALD on daily.TesterID = ALD.TesterID

--to update Changes

Update Changes
Set delta = hours_given-engtime
FROM daily inner join ALD on daily.TesterID = ALD.TesterID
inner join Changes on daily.TesterID = Changes.TesterID

--to insert the records into Changes
Insert Into Changes (TesterID, Delta)
Select daily.TesterID, hours_given-engtime
FROM daily inner join ALD on daily.TesterID = ALD.TesterID
[/code]

Poor planning on your part does not constitute an emergency on my part.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-05 : 01:36:18

Thank you so much for the reply. It helped me alot.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-12 : 04:08:11

Hi,
I need some help here. The above command will grab the data from table A and insert into table B once we execute the command right.
What if I want to grab the data from table A and insert into table B whenever there is a change or update in table A? Meaning every time table A updated table B should be automatically updated. How to do that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 04:20:17

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-03-12 : 04:34:00

How do I insert data into table B from table A and want the data in table B to be updated automatically whenever table A is updated?

I have try the following but it is useful only if i want to insert once.

Insert Into Changes (TesterID, Delta)
Select daily.TesterID, hours_given-engtime
FROM daily inner join ALD on daily.TesterID = ALD.TesterID

Help..

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-03-12 : 08:49:15

trigger should help

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-12 : 16:03:27

FYI this extended into a more complicated issue.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98588

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources