Sharath Sharath - 3 months ago 16
SQL Question

Calculate the percentage of success of each measurement values in SQL

I have 3 database tables with sample data given below

Meas_id - integer(Foreign keyed to Measurement.meas_id)
Tool_id - integer(Foreign keyed to Events.Machine_id)
Processdate- Timestamp with timezone (UTC)
CreatedDate- Timestamp with timezone (UTC)


Readings

Meas_id Tool_id Status Processdate
1 13 Completed 2016-01-01 01:34:11
1 28 Failed 2016-01-01 08:37:11
1 54 Failed 2016-01-02 16:04:12
1 32 Completed 2016-01-04 07:13:11
1 39 Completed 2016-01-04 14:14:14
1 12 Completed 2016-01-05 22:10:09
1 9 Completed 2015-12-28 13:11:07
1 17 Completed 2016-01-25 13:14:11
1 27 Completed 2016-01-15 14:15:16
1 31 Failed 2016-01-07 16:08:04
2 113 Completed 2016-01-01 01:34:11
2 128 Failed 2016-01-01 08:37:11
2 154 Failed 2016-01-02 16:04:12
2 132 Completed 2016-01-04 07:13:11
2 139 Completed 2016-01-04 14:14:14
2 112 Completed 2016-01-05 22:10:09
2 90 Completed 2015-12-28 13:11:07
2 117 Completed 2016-01-25 13:14:11
2 127 Completed 2016-01-15 14:15:16
2 131 Failed 2016-01-07 16:08:04


Events

Meas_id Machine_id Event_Name CreatedDate
1 13 Success 2015-12-27 01:34:11
1 17 Error 2015-12-27 08:37:11
1 28 Success 2015-12-27 16:04:12
1 9 Success 2015-12-28 07:13:11
1 54 Success 2015-12-28 14:14:14
1 31 Error 2015-12-28 22:10:09
1 32 Success 2015-12-29 13:11:07
1 39 Success 2015-12-29 13:14:11
1 12 Success 2015-12-31 14:15:16
1 27 Success 2016-01-01 16:08:04
2 113 Success 2015-12-27 01:34:11
2 117 Error 2015-12-27 08:37:11
2 128 Success 2015-12-27 16:04:12
2 90 Success 2015-12-28 07:13:11
2 154 Success 2015-12-28 14:14:14
2 131 Error 2015-12-28 22:10:09
2 132 Success 2015-12-29 13:11:07
2 139 Success 2015-12-29 13:14:11
2 112 Success 2015-12-31 14:15:16
2 127 Success 2016-01-01 16:08:04


Mesurement

Meas_id Meas_name
1 Length
2 Breadth


For each measurement ‘length’ and ‘breadth’ and each day of the week, I am trying to calculate the percentage of success in the first week of 2016 for all completed measurements of tools/machines within 168 hours of thier creation date.

My Desired Output is

Measurement DayofTheWeek PercentageSuccess
Length 1 50
Length 2 0
Length 3 0
Length 4 100
Length 5 100
Length 6 0
Length 7 0
Breadth 1 50
Breadth 2 0
Breadth 3 0
Breadth 4 100
Breadth 5 100
Breadth 6 0
Breadth 7 0


I tried doing it this way but certainly missing some logic and its not working.

Select m.Meas_name,
datepart(dd, Processdate) as DayofTheWeek,
(Count(m.Meas_name)* 100 / (Select Count(Event_Name) From Events where Event_Name = 'Success')) as PercentageSuccess

FROM Readings r JOIN
Measurements m
ON r.Meas_id = m.Meas_id
JOIN Events e
ON e.Meas_id = m.Meas_id
WHERE m.Meas_name IN ('Length', 'Breadth') AND
r.Status = 'Completed' AND
e.CreatedDate >= DATEADD(hh, -168, GETDATE())
GROUP BY m.Meas_name, datepart(dd, Processdate);


Kindly provide inputs on an optimized way of achieving it.

Answer

Nice I got downvoted for a correct answer probably because my answer wasn't very clear it is kind of hard to explain so here is an edit aimed at your comment and the downvoter (whom I think was just retaliating).

Anyway, Your joining of 3 tables while valid replicates the data in your events table. Due to that the way you are counting the records will always be exaggerated and incorrect. your calculation for percentage is also happens to be backwards.

On the join it looks like you are just missing the use of the Tool_id in your join. You could try something like the following:

SELECT
    m.Meas_name
    ,DAYOFWEEK(r.ProcessDate) as DayOfTheWeek
    ,(COUNT(CASE WHEN e.Event_Name = 'Success' tHEN e.Meas_id END)/(COUNT(e.Meas_id) * 1.0)) * 100 as PercentageSuccess
FROM
    Measurements m
    INNER JOIN Events e
    ON m.Meas_id = e.Meas_id
    INNER JOIN Readings r
    ON e.Meas_id = r.Meas_id
    AND e.Machine_id = r.Tool_id
    AND r.Status = 'Completed'
    AND r.ProcessDate BETWEEN '2016-01-01' AND '2016-01-07'
WHERE
    m.Meas_name IN ('Length','Breadth')
GROUP BY
    m.Meas_name
    ,DAYOFWEEK(r.ProcessDate)

Note this is written for mysql because that is what is tagged in you post. if you actually want sql-server as your syntax suggests let me know. Also, I am guessing that you a really want to filter by processdate but if you want to filter by Event.CreateDate then put that in the ON condition of the Events join.

Comments