CODEWITHSUNDEEP
sql-servert-sqlwindow-functions
Sasan
Sasan

Reputation: 614

How to check a specific range value is filled in SQL with Window functions?

I am working on a project in which we should evaluate suppliers and in this database I have this table EvaluationGrade:

+------+---------------------+------------+-----------+
| Id   | EvaluationMethodId  | FromScore  | ToScore   |                       
+------+---------------------+------------+-----------+
| 1    |       2             |     1      |    20     |
| 2    |       2             |     21     |    50     |
| 3    |       2             |     51     |    70     |
| 4    |       2             |     71     |    100    |
| 5    |       3             |     1      |    20     |
| 6    |       3             |     31     |    40     |
+------+---------------------+------------+-----------+

This table categorize scores and I am gonna be sure for EvaluationMethodId=2 scope values fill 1 to 100 (just like sample above).

I am looking for something like this:

+---------------------+------------+
| EvaluationMethodId  |    Sum     |                      
+---------------------+------------+
|       2             |     100    |
|       3             |     30     |
+---------------------+------------+

This is the way I attempted:

WITH myUpdate
AS (SELECT emg.Id,emg.EvaluationMethodId,
           SUM(emg.ToGrade - emg.FromGrade) + 1 AS SumScope
    FROM generalsup.EvaluationMethodGrading emg
    GROUP BY emg.Id,emg.EvaluationMethodId)
SELECT myUpdate.EvaluationMethodId, SUM(myUpdate.SumScope) AS SumScopeAll
FROM myUpdate
GROUP BY myUpdate.EvaluationMethodId;

But I use window function that put less overhead on server.

Upvotes: 0

Views: 59

Answers (1)

forpas
forpas

Reputation: 164224

Since there is no case of overlaps in the scores, you can do it with group by EvaluationMethodId and sum():

select EvaluationMethodId, sum(ToScore - FromScore + 1) [Sum]
from EvaluationMethodGrading 
group by EvaluationMethodId

See the demo.
Results:

> EvaluationMethodId | Sum
> -----------------: | --:
>                  2 | 100
>                  3 |  30

Upvotes: 3

Related Questions