CODEWITHSUNDEEP
mysqlcalculated-columns
Electronic Circuit
Electronic Circuit

Reputation: 335

How to calculate the daily cost based on total hours?

How to calculate the cost using SQL what i want is to calculate cost for a single CCODE. In this data table the cost is belong to the day (DayUID) not a particular CCODE but we can extract that for particular CCODE base on the hours of the CCODE. The mathematical calculation should be like let's say CCODE=SS1 where the DayUID=DDD1

So Cost for SS1 basically calculate like so (4 hours / 12 hours) * 2000 = 666.67

DayUID | DayCost | PCODE | CCODE | Duration |
---------------------------------------------
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        |

Upvotes: 3

Views: 108

Answers (1)

Fabricator
Fabricator

Reputation: 12782

Get total duration by DayUID and CCODE in a subquery:

  SELECT a.DayUID, 
         a.CCODE, 
         SUM(a.Duration) / b.Duration * a.DayCost
    FROM Table1 a
    JOIN (SELECT DayUID, 
                SUM(Duration) Duration 
           FROM Table1 
       GROUP BY DayUID) b 
      ON a.DayUID = b.DayUID
GROUP BY DayUID, CCODE;

SQLFiddle

Upvotes: 1

Related Questions