Friday, January 8, 2016

Calculating Cost within a table SQL

CREATE TABLE Table1
(`DayUID` varchar(4), `DayCost` int, `PCODE` varchar(2), `CCODE` varchar(5), `Duration` int)
;

INSERT INTO Table1
(`DayUID`, `DayCost`, `PCODE`, `CCODE`, `Duration`)
VALUES
('DDD1', 2000, 'TS', 'SS1', 3),
('DDD1', 2000, 'TS', 'SS1', 1),
('DDD1', 2000, 'TS', 'ST2', 2),
('DDD1', 2000, 'P', 'ST2', 6),
('DDD2', 3000, 'P', 'IH1', 6.6),
('DDD2', 3000, 'U', 'IH1', 4),
('DDD2', 3000, 'TS', 'IH2', 1),
('DDD3', 1200, 'TU', 'SC1', 7),
('DDD3', 1200, 'P', 'SC2', 8),
('DDD3', 1200, 'U', 'CMPLT', 3)
;

Calculating cost with round value to no decimal point/places using sub-query SQL

select a.DayUID, a.CCODE, round(SUM(a.Duration) / MIN(b.Duration) * MIN(a.DayCost), 0)
from Table1 a
join (select DayUID, sum(Duration) Duration from Table1 group by DayUID) b on a.DayUID = b.DayUID
group by DayUID, CCODE;

ROUND(SUM(a.Duration) / MIN(b.Duration) * MIN(a.DayCost) * 100) / 100

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...