4 Replies - 689 Views - Last Post: 12 October 2014 - 07:07 PM

#1 foraye   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 20-December 13

How to calculate columns based on values in 2 other tables

Posted 12 October 2014 - 06:18 AM

Hi All,

I am a beginner to SQL Server and TSQL.

I'm working on a project that get values to two tables (Base and Teach) then I need to calculate the difference between the coordinates (Offsets) and return it to my .net program which in turn writes to the robot controller.

Is my Table layout correct for what i'm trying to do?
How should I go about setting up the references and formulas for the calculated columns?

Part Numbers are unique and specified in the Params table. There are 25 ProgID(1-25) per Part number. The Base, Teach, and Offset tables will end up with entries for about 500 PartNumbers.

Here are my tables:

Params Table PK PartNumber

Base Table PK BaseCoordID ProgramID FK PartNumber BaseX BaseY BaseZ BaseRX BaseRY BaseRZ BaseFig

Teach Table PK TeachCoordID FK ProgramID FK PartNumber TeachX TeachY TeachZ TeachRX TeaxhRY TeachRZ TeachFig


Offset Table PK OffsetCoordID FK BaseCoordID FK TeachCoordID FK PartNumber FK ProgramID OffsetX OffsetY OffsetZ OffsetRX OffsetRY OffsetRZ

Is This A Good Question/Topic? 0
  • +

Replies To: How to calculate columns based on values in 2 other tables

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14088
  • View blog
  • Posts: 56,440
  • Joined: 12-June 08

Re: How to calculate columns based on values in 2 other tables

Posted 12 October 2014 - 10:01 AM

Quote

Is my Table layout correct for what i'm trying to do?

I don't know.. Why would you need third 'offset' table? Why not just use a join between base and teach, on a common key, and return the subtracted offset cord from each?
Was This Post Helpful? 1
  • +
  • -

#3 foraye   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 20-December 13

Re: How to calculate columns based on values in 2 other tables

Posted 12 October 2014 - 04:24 PM

I like your suggestion, I just don't know how to put that together...
Was This Post Helpful? 0
  • +
  • -

#4 foraye   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 20-December 13

Re: How to calculate columns based on values in 2 other tables

Posted 12 October 2014 - 06:53 PM

It looks like I've put it partially together...

I did the Join and calculated column and it works...

SELECT BaseX, TeachX,BaseY,TeachY,Basez,TeachZ, (BaseZ-TeachZ) as Offset
FROM [BarTender].[dbo].[Product_Coordinates] as  A
INNER JOIN [BarTender].[dbo].[Product_Teach_Coordinates] as B
ON A.BaseCoordID = B.TeachCoordID
Where A.DSM_PN = '20200-01'


And Just the Offsets!

SELECT (BaseZ-TeachZ) as Offset
FROM [BarTender].[dbo].[Product_Coordinates] as  A
INNER JOIN [BarTender].[dbo].[Product_Teach_Coordinates] as B
ON A.BaseCoordID = B.TeachCoordID
Where A.DSM_PN = '20200-01'

This post has been edited by foraye: 12 October 2014 - 06:56 PM

Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14088
  • View blog
  • Posts: 56,440
  • Joined: 12-June 08

Re: How to calculate columns based on values in 2 other tables

Posted 12 October 2014 - 07:07 PM

Good to hear!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1