CODEWITHSUNDEEP
sqlsql-server
Chen
Chen

Reputation: 181

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

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.

Upvotes: 3

Views: 259

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

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;

Upvotes: 1

Sanpas
Sanpas

Reputation: 1180

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.

Upvotes: 2

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30555

your problem is your subquery does not relate with your main query (they are not joined). Everytime your subquery sums static time range instead of specific time related with your main query.

select 
  date, 
  ( select count(*) from innerTbl where dt between '01.01.2018' and '01.01.2019') static_sum
from t

is not same with

select 
  mdt, 
  ( select count(*) from innerTbl where dt = t.mdt) joined_table_sum
from t

Upvotes: 1

Related Questions