I have a column with different values for absences -

abs_table

Query :

SELECT WHEN absence_type IN ('FLT', 'JUR', 'BRV', 'LOD', 'PAID') THEN 
            regular_measure_hours 
            - SUM(VALUE) OVER (PARTITION BY person_number value 
                               ORDER BY person_number) 
        ELSE regular_measure_hours,
       person_number
  FROM abs_table

If the absence_type is in any of these- ('FLT','JUR','BRV','LOD','PAID') then i need to subtract the sum of only those "Value" column with the regular_measure_hours

The sum over in the above column is not giving me the correct result in most cases.

Desired output :

How can i tweak the sum over partition to get the right results

CodePudding user response：

You don't need analytic SUM() function but conditional aggregation while taking NVL() into account in order to replace NULL values with zero such as

SELECT SUM(CASE
         WHEN absence_type IN ('FLT', 'JUR', 'BRV', 'LOD', 'PAID') THEN          
          NVL(regular_measure_hours,0) -NVL(value,0) 
       END) AS regular_measure_hours,
       person_number
  FROM abs_table
 GROUP BY person_number

Demo

CodePudding user response：

With your single-table data model (assuming this is your real model), which is pretty absurd, you need an aggregation like this:

select person_number,
       sum(regular_measure_hours) -
         nvl(sum(case when absence_type in ('FLT', 'JUR', 'BRV', 'LOD', 'PAID') 
                      then value end), 0) as adjusted_hours
from   abs_table
group  by person_number
;

CodePudding user response：

Group by the PERSON_NUMBER, and just for those 5 types.

SELECT
 PERSON_NUMBER, 
 SUM(regular_measure_hours)
 - SUM(CASE 
       WHEN Absence_type IN ('FLT','JUR','BRV','LOD','PAID')
       THEN VALUE
       ELSE 0
       END) as regular_measure_hours
FROM abs_table
WHERE (Absence_type IN ('FLT','JUR','BRV','LOD','PAID')
   OR regular_measure_hours IS NOT NULL) 
GROUP BY PERSON_NUMBER
ORDER BY PERSON_NUMBER