do calculation base on 2 fields from 2 different tables

[vinhtang]

Hi,
What I have to do is to calculate the total rate for each code (in table 2) and insert that total rate field in that same table (2). I've never written any SQL codes except for simple select statements. Any help is greatly appreciated.

Table1:
CD Rate
001 1.5
002 1.25
003 0.5

Table2:
Code Desc
ABC 001 002
DEF 003 001 002
GHI 003

 

[bborissov]

Not tested

DECLARE @Table1 TABLE (CD varchar(3), Rate Numeric(10,4))
DECLARE @Table2 TABLE (Code varchar(3), Desc1 varchar(200), Rate Numeric(10,4))
INSERT INTO @Table1 VALUES ('001',1.5)
INSERT INTO @Table1 VALUES ('002',1.25)
INSERT INTO @Table1 VALUES ('003',0.5)

INSERT INTO @Table2 VALUES ('ABC','001 002', 0 )
INSERT INTO @Table2 VALUES ('DEF','003 001 002', 0 )
INSERT INTO @Table2 VALUES ('GHI','003', 0 )

UPDATE Table2 SET Table2.Rate = Table1.Rate
FROM @Table2 Table2
INNER JOIN (SELECT Table2.Code, SUM(Table1.Rate) AS Rate
                  FROM @Table2 Table2
                  LEFT JOIN @Table1 Table1 ON CHARINDEX(Table1.CD, Table2.Desc1)> 0
                  GROUP BY Table2.Code) Table1
ON Table2.Code = Table1.Code



SELECT * from @Table2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[vinhtang]

Thanks a lot for your quick response. I forgot to tell you that both my tables have thousands of record. In that case I guess I can not use what you suggested. I have to use some kind of loop to loop through and match the records right ? I'm completely unfamiliar with SQL statements, so please help me out.

Thank you.

 

[bborissov]

This is just a code what I used for testig. Let me rewrite the code with some comments

--- Begin testing preparation block
--- You don't need these lines
DECLARE @Table1 TABLE (CD varchar(3), Rate Numeric(10,4))
DECLARE @Table2 TABLE (Code varchar(3), Desc1 varchar(200), Rate Numeric(10,4))
INSERT INTO @Table1 VALUES ('001',1.5)
INSERT INTO @Table1 VALUES ('002',1.25)
INSERT INTO @Table1 VALUES ('003',0.5)

INSERT INTO @Table2 VALUES ('ABC','001 002', 0 )
INSERT INTO @Table2 VALUES ('DEF','003 001 002', 0 )
INSERT INTO @Table2 VALUES ('GHI','003', 0 )
--- End testing preparation block


--- Actual code
--- You need only to change names of @Table2 and @Table1
--- with actual table names
--- Make a good BACKUP first

UPDATE Table2 SET Table2.Rate = Table1.Rate
FROM @Table2 Table2
INNER JOIN (SELECT Table2.Code, SUM(Table1.Rate) AS Rate
                  FROM @Table2 Table2
                  LEFT JOIN @Table1 Table1 ON CHARINDEX(Table1.CD, Table2.Desc1)> 0
                  GROUP BY Table2.Code) Table1
ON Table2.Code = Table1.Code

SELECT * from @Table2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

The more records your tables have the more important it is to use a set-based process such as Borislav suggested. You want to forget the idea of ever looping through records to do an update, insert or delete. This is a poor practice which results in extremely slow systems. NOw when you get to the millions of records, you may want to update in chunks to imporve speed but you still wouldn;t want to do one record at a time. Replaceing a cursorbased approach to data changes can save hours even days of processing time.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[vinhtang]

Million thanks. All my servers are down so I haven't able to test the codes yet. I'll let you know the result as soon as I know. I notice 1 thing though. Since my table 2 does not have field "rate" yet, I still have to include this line right ?

DECLARE @Table2 TABLE (Rate Numeric(10,4))

Anyway, I'm sure I can figure that part out. Again, thank you very much.

 

[bborissov]

vinhtang,
Can you post the actianl table structures? I think this way will be easier to rewrite the code. By the way with
DECLARE @Table2 TABLE (Rate numeric (10,4))
you didn't add a field in your table2, this statement declare a table variable named @Table2 with following structure:
Rate numeric (10,4). Also keep in mind that I use code above ONLY for testing and didn;t realy care about the length of the field and its type. maybe for your purposes you need a field with different type and length. If you are REALY sure that the field can't exceed 999.99 minutes you don't need numeric (10,4) as field length you need numeric(6,2). That will save huge amount of space for your database depending of how many records you have in that table. 4 bytes for every one of million of records

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

actianl = actual

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[vinhtang]

Stupid me!
Is it ok that I add a column "rate" to the table2 first and then use your update statement ?

 

[vinhtang]

Oh I forgot to attach the table struture.

Length Allow Nulls Data Type Column Name
5 char mill_cd
1 (check) varchar vac_flg
2 (check) char city_flg
1 (check) varchar spcl_dist
9 (check) decimal tot_mill
100 (check) varchar dscr
4 varchar coll_cd
4 (check) int seq

 

[vinhtang]

Hi I have a syntax error return on line

GROUP BY Table2.Code) Table1

and I notice it has 1 single ")" I tried a couple things there but none works since I do not fully understand the codes yet. Sorry for taking too much of your time.

 

[vinhtang]

Sorry! please forget about my previous email. The error was actually from INNER JOIN down.
This is what I have:
Table1: lu_mill_cd
Table2: lu_tax_dist.curr
I manually added field 'total_rate' to 'lu_mill_cd' table

***my code***

UPDATE lu_mill_cd
SET lu_mill_cd.total_rate = lu_tax_dist.curr
FROM lu_mill_cd
INNER JOIN (SELECT lu_mill_cd.mill_cd,
SUM(lu_tax_dist.curr)AS total_rate
FROM lu_mill_cd
LEFT join lu_tax_dist ON
CHARINDEX(lu_tax_dist.curr, lu_mill_cd.dscr)>0
GROUP BY lu_mill_cd.mill_cd) lu_tax_dist
SELECT * FROM lu_mill_cd