T-SQL operation(sumproduct) - 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

T-SQL operation(sumproduct)

Author

Topic

sajitha
Starting Member

10 Posts

Posted - 2014-03-26 : 06:04:15

From the below given table, how can I find out TotalCost ie,
Sum ( TableB.WCost * [ Table B. Price + ( Table C. Time * Table D. Cost ) ] ) if the condition is:- Date is between 10/04/2014 AND 11/04/2014 AND Category = categoryA AND Eid =2 AND Pid =2 AND Fid =1

The expected TotalCost is 1379.902 ((2.236 + 14.716 + 576.95 + 786) = 1379.902 )

[ 23.6 + ( 2 * 100 ) ] * 0.01 = 2.236
[ 67.9 + ( 1.5 * 200 ) ] * 0.04 = 14.716

[ 100.78 + ( 1.30 * 100 ) ] * 2.5 = 576.95

[ 445 + ( 1.5 * 140 ) ] * 1.2 = 786

Table A Table B Table C Table D

AID BID CID DID
Date AID AID Cost
Category Price Time
Eid WCost DID
Fid
Pid

Table A

AID Date Category Eid Fid Pid

1 10/04/2014 categoryA 2 1 2
2 10/04/2014 categoryB 2 1 2
3 10/04/2014 categoryC 2 1 2
4 24/04/2014 categoryA 3 2 1

Table B

BID AID Price WCost

1 1 23.6 0.01
2 1 67.9 0.04
3 1 100.78 2.5
4 2 445 1.2

Table C

CID AID Time DID
1 1 2 1
2 1 1.5 2
3 1 1.30 1
4 2 1.5 3

Table D
DID Cost

1 100
2 200
3 140
4 123

Thank you in advance

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-26 : 07:39:29

Without having the data I cant test but something along the lines of this...to add additional fields you would need to include fields after the select separated by comms...this would only return the TotalCost.

Select Sum(b.WCost * b.Price + c.Time * d.Cost) as TotalCost
From Tableb b
Join Tablec c on b.BID = c.CID
Join Tabled d on b.BID = d.DID
Join Tablea a on b.BID = a.AID
Where a.Date Between '2014-04-10' And '2014/04/11'
And a.Category = 'categoryA'
And a.Eid = 2
And a.Pid = 2
And a.Fid = 1

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources