Saturday, January 9, 2016

Calculate cost using values from two tables sql

http://sqlfiddle.com/#!9/6cda87/1
CREATE TABLE Operation
(`Operationuid` varchar(3), `operationname` varchar(10), `owner` varchar(55))
;

INSERT INTO operation
(`Operationuid`, `operationname`, `owner`)
VALUES
('AA1', 'Cow', 'Jon Letoy'),
('AA2', 'Chicken', 'Ridikill' ),
('AA3', 'Snake', 'Mighty'),
('AA4', 'Sheep', 'The great'),
('AA5', 'Pig', 'Peon');


CREATE TABLE Activity
(`Operationuid` varchar(3), `DailyUID` varchar(10), `ActivityUID` varchar(55), `Class` varchar(3), `Phase` varchar(3), `Ops` varchar(3), `Root` varchar(3), Duration int);

INSERT INTO Activity
(`Operationuid`, `DailyUID`, `ActivityUID`, `Class`, `Phase`, `Ops`, `Root`, `Duration`)
VALUES
('AA1', 'DD1', 'AC1', 'TS', 'SS1', 'OP1', null, 12),
('AA1', 'DD1', 'AC2', 'TS', 'SS1', 'OP1', null, 2),
('AA1', 'DD1', 'AC3', 'TS', 'ST2', 'OP2', 'RR1', 3),
('AA1', 'DD2', 'AC4', 'P', 'ST2', 'OP3', null, 5),
('AA1', 'DD2', 'AC5', 'P', 'IH1', 'OP4', 'RR2', 1),
('AA1', 'DD2', 'AC6', 'U', 'IH1', 'OP5', null, 6.6),
('AA1', 'DD3', 'AC7', 'TS', 'IH2', 'OP6', null, 6),
('AA1', 'DD3', 'AC8', 'TU', 'SC1', 'OP7', null, 7),
('AA1', 'DD3', 'AC9', 'P', 'SC2', 'OP8', null, 8);


CREATE TABLE Daily
(`Operationuid` varchar(3), `DailyUID` varchar(10), `Dayno` varchar(55), `Daycost` decimal);

INSERT INTO Daily
(`Operationuid`, `DailyUID`, `Dayno`, `Daycost`)
VALUES
('AA1', 'DD1', 1, 1000),
('AA1', 'DD2', 2, 2000),
('AA1', 'DD3', 3, 3000),
('AA1', 'DD4', 4, 4000),
('AA1', 'DD5', 5, 5000);

select ap.phase,
(ap.duration / ad.duration) * daycost as cost
from (SELECT d.dailyUid, sum(daycost) as daycost
FROM Daily d
GROUP BY d.dailyuid
) d INNER JOIN
(Select a.dailyuid, sum(a.duration) as duration
from activity a
group by dailyUID
) ad
on ad.dailyuid = d.dailyuid join
(select a.dailyuid, a.phase, sum(a.duration) as duration
from activity a
group by a.dailyuid, a.phase
) ap
on ad.dailyuid = ap.dailyuid
group by phase



Variation to bring in Class column and dailynumber

select activity.class, daily.dayno, ap.phase,
(ap.duration / ad.duration) * daily.daycost as cost
from activity, daily, (SELECT d.dailyUid, sum(daycost) as daycost
FROM Daily d
GROUP BY d.dailyuid
) d INNER JOIN
(Select a.dailyuid, sum(a.duration) as duration
from activity a
group by dailyUID
) ad
on ad.dailyuid = d.dailyuid join
(select a.dailyuid, a.phase, sum(a.duration) as duration
from activity a
group by a.dailyuid, a.phase
) ap
on ad.dailyuid = ap.dailyuid
group by class, phase


Correct variation to include class columns into the query output http://sqlfiddle.com/#!9/6cda87/15

select ap.class, ap.phase,
(ap.duration / ad.duration) * daycost as cost
from (SELECT d.dailyUid, sum(daycost) as daycost
FROM Daily d
GROUP BY d.dailyuid
) d INNER JOIN
(Select a.class, a.dailyuid, sum(a.duration) as duration
from activity a
group by dailyUID
) ad
on ad.dailyuid = d.dailyuid join
(select a.class, a.dailyuid, a.phase, sum(a.duration) as duration
from activity a
group by a.dailyuid, a.phase
) ap
on ad.dailyuid = ap.dailyuid
group by ap.class, phase



To include day number into the column http://sqlfiddle.com/#!9/6cda87/20

select d.Dayno, ap.class, ap.phase,
(ap.duration / ad.duration) * daycost as cost
from (SELECT d.dayno,d.dailyUid, sum(daycost) as daycost
FROM Daily d
GROUP BY d.dailyuid
) d INNER JOIN
(Select a.class, a.dailyuid, sum(a.duration) as duration
from activity a
group by dailyUID
) ad
on ad.dailyuid = d.dailyuid join
(select a.class, a.dailyuid, a.phase, sum(a.duration) as duration
from activity a
group by a.dailyuid, a.phase
) ap
on ad.dailyuid = ap.dailyuid
group by d.dayno, ap.class, phase





http://sqlfiddle.com/#!9/cc30cc/8

select operation.operationname, ap.phase,
(ap.duration / ad.duration) * daycost as cost
from operation INNER JOIN (SELECT d.dailyUid, sum(daycost) as daycost
FROM Daily d
GROUP BY d.dailyuid
) d INNER JOIN
(Select a.dailyuid, sum(a.duration) as duration
from activity a
group by dailyUID
) ad
on ad.dailyuid = d.dailyuid join
(select a.dailyuid, a.phase, sum(a.duration) as duration
from activity a
group by a.dailyuid, a.phase
) ap
on ad.dailyuid = ap.dailyuid
group by phase

Adding operationname from the Operation table into the query output

No comments:

Post a Comment

Adjustable DC to DC step up voltage booster using LM2577 -Adj P+ chip

One simple circuit to step up voltage using LM2577 -Adj P+ chip. The schematic is from Texas Instrument and it works expected - and below is...