CODEWITHSUNDEEP
mysqlcalculated-columns
Electronic Circuit
Electronic Circuit

Reputation: 335

How to create table + calculate cost involve 2 columns

I want to calculate cost by the phase but it doesn't work my data is as follows. I am using this command to calculate cost 

SELECT Daily.dayno, activity.class, activity.phase,
    activity.duration/sum(activity.duration)*daily.daycost as cost
from Daily INNER JOIN
(
    Select activity.dailyuid, sum(activity.duration) duration
    group by dailyUID
) Activity
on activity.dailyuid=daily.dailyuid
group by class, phase

Obviously the above query is not working. What i want is to calculate cost by phase for example

Phase SS1 in Dailyuid DD1 = 4/12*1000 where 4 is total hours for SS1 and 12 is total hours of dailyuid DD1 and 1000 is the cost for dailyuid DD1.

Another example is Phase ST2 = 8/12*1000 IH=10.6/11.6*2000 and so on

Daily Table

DailyUID | Dayno | Daycost |
---------|-------|---------|
DD1      | 1     | 1000    | 
DD2      | 2     | 2000    |
DD3      | 3     | 3000    |

Activity Table 

DailyUID | CLASS | PHASE | Duration |
-----------------------------------
DD1      | TS    | SS1   | 3        |
DD1      | TS    | SS1   | 1        |
DD1      | TS    | ST2   | 2        |
DD1      | P     | ST2   | 6        |
DD2      | P     | IH1   | 6.6      |
DD2      | U     | IH1   | 4        |
DD2      | TS    | IH2   | 1        |
DD3      | TU    | SC1   | 7        |
DD3      | P     | SC2   | 8        |
DD3      | U     | CMPLT | 3        |

Upvotes: 1

Views: 96

Answers (2)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

I think I managed to do it using SUM() OVER (...):

Setup:

-- drop table Daily
create table Daily
(
   DailyUID VARCHAR(6),
   DayNo INT,
   DayCost NUMERIC(18, 2)
)
go

insert into Daily values ('DD1', 1, 1000),
    ('DD2', 2     , 2000),
    ('DD1', 3     , 3000)
go

-- drop table ActivityTable
create table ActivityTable
(
    DailyUID VARCHAR(6),
    Class VARCHAR(2),
    Phase VARCHAR(6),
    Duration NUMERIC(5, 1)
)
go

insert into ActivityTable values 
('DD1'   , 'TS'    , 'SS1'   , 3        ),
('DD1'   , 'TS'    , 'SS1'   , 1        ),
('DD1'   , 'TS'    , 'ST2'   , 2        ),
('DD1'   , 'P'     , 'ST2'   , 6        ),
('DD2'   , 'P'     , 'IH1'   , 6.6      ),
('DD2'   , 'U'     , 'IH1'   , 4        ),
('DD2'   , 'TS'    , 'IH2'   , 1        ),
('DD3'   , 'TU'    , 'SC1'   , 7        ),
('DD3'   , 'P'     , 'SC2'   , 8        ),
('DD3'   , 'U'     , 'CMPLT' , 3        )
GO

Script: (works only in SQL Server, see MySql version below)

    ;WITH Sums AS (
        SELECT DISTINCT AT.DailyUID, AT.Phase, 
            SUM(AT.Duration) OVER (PARTITION BY AT.Phase, AT.DailyUID) AS PhaseDuration,
            SUM(AT.Duration) OVER (PARTITION BY AT.DailyUID) AS TotalDuration
        FROM ActivityTable AT
    )
    SELECT S.Phase, S.DailyUID, S.PhaseDuration, S.TotalDuration, S.PhaseDuration * 1.0 / S.TotalDuration * 1000
    FROM Sums S

So, grouping is done in `Sums` CTE by phase and day first and then by day only.

[edit - make it work for MySql]

SELECT S.Phase, S.DailyUID, S.PhaseDuration, S.TotalDuration, S.PhaseDuration * 1.0 / S.TotalDuration * 1000
FROM (
    SELECT DISTINCT ATOut.DailyUID, ATOut.Phase, PD.PhaseDuration, TD.TotalDuration
    FROM ActivityTable ATOut
        JOIN (SELECT AT.Phase, AT.DailyUID, SUM(AT.Duration) PhaseDuration FROM ActivityTable AT GROUP BY Phase, DailyUID) PD 
            ON PD.Phase = ATOut.Phase AND PD.DailyUID = ATOut.DailyUID
        JOIN (SELECT AT.DailyUID, SUM(AT.Duration) TotalDuration FROM ActivityTable AT GROUP BY DailyUID) TD 
            ON TD.DailyUID = ATOut.DailyUID
) S

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270443

If I understand correctly, you need to pre-aggregate the tables before joining. One additional aggregation is necessary:

select ap.phase,
       (ap.duration / ad.duration) * d.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;

SQL Fiddle is here.

Upvotes: 1

Related Questions