how to multiply value from first table with second table

Question

Hi TABLE A ID Name Rate Reference Id 1 a 5 1 2 b 4 1 3 c 2 2 4 d 3 2 TABLE B tID Rate Reference Id date 1 2 1 01/01/2010 2 3 2 02/02/2014 Now from the TABLE B get Value of Rate and multiply it with Rate of TABLE A which having TABLE BA.Reference Id = TABLE B .Reference Id. The Result Should be like this ID Name Rate Reference Id 1 a (5*2) = 7 1 2 b (4*2) = 8 1 3 c (2*3) = 6 2 4 d (3*3) = 9 2 Thanks

Vikrant More · Answer

Kiresh in the Result should be for Rate did you mean this 5*2 = 10 ? script for this as below IF OBJECT_ID('TEMPDB..#TableA') IS NOT NULL DROP TABLE TEMPDB..#TableA IF OBJECT_ID('TEMPDB..#TableB') IS NOT NULL DROP TABLE TEMPDB..#TableB CREATE TABLE #TableA ( ID INT IDENTITY(11) NAME CHAR(1) RATE INT ReferenceID INT ) CREATE TABLE #TableB ( tID INT IDENTITY(11) RATE INT ReferenceID INT date date ) INSERT INTO #TableA VALUES('a'51)('b'41)('c'22)('d'32) INSERT INTO #TableB VALUES(21'2010-01-01')(32'2014-02-02') SELECT A.IDA.NAME'('+CONVERT(CHAR(1)A.RATE)+'*'+CONVERT(CHAR(1)B.RATE)+') = '+ CONVERT(CHAR(2)A.RATE * B.RATE) AS RATEA.ReferenceID FROM #TableA A JOIN #TableB B ON A.ReferenceID = B.ReferenceID

Jignesh Trivedi · Answer

Hi Consider both table 's Rate is decimal / float type. SELECT A.ID A.Name (A.RATE * B.RATE) as RATE from TABLEA a INNER JOIN TABLEB b ON a.ReferenceId = b.ReferenceId hope this will help you.

N Vinodh · Answer

ckeck out this link it may help you http://stackoverflow.com/questions/21266054/multiplying-column-by-weight-in-another-table-requiring-a-second-select-and-summ mark if accepted as answer

ID	Name	Rate	Reference Id
1	a	(5*2) = 7	1
2	b	(4*2) = 8	1
3	c	(2*3) = 6	2
4	d	(3*3) = 9	2