How to return ratio/rate group by date using sql server

Question

How to return records in forms of rate/ratio group by date using SQL server? For e.g.,:

Date      PassingRate(%)
2019-01-01        50
2019-01-10        78
2019-02-03        90

The table properties are

DeviceId       - string
Eventtime      - long, it's timestamp
MTemperature1  - float
Mtemperature2  - float
lowdetergent   - 0 or 1
lowdryer       - 0 or 1
interrupted    - 0 or 1

I need to select a timestamp range, for example, from 0000000000 to 9999999999. There are multiple records each day, the SQL should filter data and retrieve me processed data that meet certain standard

Below is my query clause:

SELECT CONVERT(date, DATEADD(S, EventTime + 8 * 3600, '1970-01-01 00:00:00')) AS Date,
       CONVERT(decimal(18, 2),
               (SELECT COUNT(*)
                FROM testTbl
                WHERE lowdetergent = 0
                  AND lowdryer = 0
                  AND MTemperature1 >= 0
                  AND MTemperature2 >= 0
                  AND interrupted = 0
                  AND DeviceId = 'test1'
                  AND EventTime >= 0000000000
                  AND EventTime <= 9999999999) * 100.0 / (SELECT COUNT(*)
                                                          FROM testTbl
                                                          WHERE DeviceId = 'test1'
                                                            AND EventTime >= 0000000000
                                                            AND EventTime <= 9999999999)) AS PassingRate
FROM testTbl
GROUP BY CONVERT(date, DATEADD(S, EventTime + 8 * 3600, '1970-01-01 00:00:00'))
ORDER BY Date;

The result of my query clause:

          DATE                       PASSINGRATE
2019-01-21T00:00:00.0000000              25.00
2019-01-22T00:00:00.0000000              25.00
2019-02-12T00:00:00.0000000              25.00

As you can see, the "PassingRate" column seems to be overall rate, not daily rate.

Answer 1

If i understand your wall attempt and your excepted output i think this query can respond :

SELECT CONVERT(date, DATEADD(S, EventTime + 8 * 3600, '1970-01-01 00:00:00')) AS Date,
       CONVERT(decimal(18, 2),SUM(CASE WHEN  lowdetergent = 0
                  AND lowdryer = 0
                  AND MTemperature1 >= 0
                  AND MTemperature2 >= 0
                  AND interrupted = 0
                  AND DeviceId = 'test1'THEN 1 ELSE 0 END) / SUM (CASE WHEN DeviceId = 'test1' THEN 1 ELSE 0 END))
FROM testTbl
GROUP BY CONVERT(date, DATEADD(S, EventTime + 8 * 3600, '1970-01-01 00:00:00'))
ORDER BY Date;

Please provide some mock (sample) data and structure off table for any other help.

Answer 2

The simplest method might be to use avg():

SELECT CONVERT(date, DATEADD(S, EventTime + 8 * 3600, '1970-01-01 00:00:00')) AS Date,
       AVG(CASE WHEN lowdetergent = 0 AND lowdryer = 0 AND MTemperature1 >= 0 AND MTemperature2 >= 0 AND

                     interrupted = 0 AND DeviceId = 'test1' AND EventTime >= 0000000000 EventTime <= 9999999999
                THEN 100.0
                WHEN DeviceId = 'test1' AND EventTime >= 0000000000 AND EventTime <= 9999999999
                THEN 0
           END) AS PassingRate
FROM testTbl
GROUP BY CONVERT(date, DATEADD(S, EventTime + 8 * 3600, '1970-01-01 00:00:00'))
ORDER BY Date;

This can probably be simplified to:

SELECT CONVERT(date, DATEADD(S, EventTime + 8 * 3600, '1970-01-01 00:00:00')) AS Date,
       AVG(CASE WHEN lowdetergent = 0 AND lowdryer = 0 AND MTemperature1 >= 0 AND MTemperature2 >= 0 AND interrupted = 0
                THEN 100.0
                ELSE 0 
           END) AS PassingRate
FROM testTbl
WHERE DeviceId = 'test1' AND EventTime >= 0000000000 AND EventTime <= 9999999999
GROUP BY CONVERT(date, DATEADD(S, EventTime + 8 * 3600, '1970-01-01 00:00:00'))
ORDER BY Date;