March 25, 2014 at 11:07 pm
From the below given tables, 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
[ 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
(2.236 + 14.716 + 576.95 + 786) = 1379.902
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 4 3 1
3 10/04/2014 categoryC 3 6 4
4 24/04/2014 categoryA 2 2 3
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
1100
2200
3140
4123
Thanks in advance
March 26, 2014 at 12:48 pm
Hi
I don't think the result you have posted can result from the data you have posted. Also have a read of this article[/url] to assist with getting the best help.
The condition ensures that only AID 1 gets reported on. Guessing at the joins, this means that BID and CID 4 will not be included as they are joined to AID 2.
This shows what I mean
--Sample Data
WITH
TableA AS (
SELECT * FROM (VALUES
(1, CAST('20140410' AS DATETIME), 'categoryA', 2, 1, 2),
(2, CAST('20140410' AS DATETIME), 'categoryB', 4, 3, 1),
(3, CAST('20140410' AS DATETIME), 'categoryC', 3, 6, 4),
(4, CAST('20140424' AS DATETIME), 'categoryA', 2, 2, 3)
) AS TA(AID, [Date], Category, Eid, Fid, Pid)
),
TableB AS (
SELECT * FROM (VALUES
(1, 1, 23.6, 0.01),
(2, 1, 67.9, 0.04),
(3, 1, 100.78, 2.5),
(4, 2, 445, 1.2)
) AS TB(BID, AID, Price, WCost)
),
TableC AS (
SELECT * FROM (VALUES
(1, 1, 2, 1),
(2, 1, 1.5, 2),
(3, 1, 1.30, 1),
(4, 2, 1.5, 3)
) AS TC(CID, AID, [Time], DID)
),
TableD AS (
SELECT * FROM (VALUES
(1,100),
(2,200),
(3,140),
(4,123)
) AS TD(DID,Cost)
)
-- Actual Query
SELECT a.*,b.BID, c.CID, d.DID, b.WCost * ( b.Price + (c.Time * d.Cost ) ), b.WCost, b.Price, c.Time, d.Cost
FROM TableA a
INNER JOIN TableB b ON a.AID = b.AID
INNER JOIN TableC c ON a.AID = c.AID and b.BID = c.CID -- Guessing this is the join condition
INNER JOIN TableD d ON c.DID = d.DID
WHERE [Date] between '20140410' AND '20140411' AND Category = 'categoryA' AND Eid =2 AND Pid =2 AND Fid =1
Anyhow this should help you get close to what you want
SELECT a.AID, SUM( b.WCost * ( b.Price + (c.Time * d.Cost ) ) )
FROM TableA a
INNER JOIN TableB b ON a.AID = b.AID
INNER JOIN TableC c ON a.AID = c.AID and b.BID = c.CID -- Guessing this is the join condition
INNER JOIN TableD d ON c.DID = d.DID
WHERE [Date] between '20140410' AND '20140411' AND Category = 'categoryA' AND Eid =2 AND Pid =2 AND Fid =1
GROUP BY a.AID
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply