Ytukyg

I am facing issues when I try to create a case expression with a group function condition along with normal column conditions.

Table: STG

+ -------+--------+------+----------+

| Ref_ID | Actual | Paid | Reason   |

+ -------+--------+------+----------+

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 0      | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 0      | 480  | TRAINING |

| H1     | 0      | 0    |          |

| H1     | 360    | 0    |          |

| H1     | 360    | 0    |          |

+ -------+--------+------+----------+

Already the code is like below:

Explanation: If Actual hrs per day exceeds 480 then that needs to be taken as 480. ie. 8hrs/day * 60 =480.
then the sum of actual is extended 1440 then that needs to be deducted from the sum of actual hrs.

In this same query, I have to sum the all actuals along with paid (paid hrs if reason is Training)

 SELECT

    CASE WHEN (SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END))>1440 THEN

    ((SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END))-1440) ELSE 0

    END SINGLE_RATE

    FROM STG WHERE REF_ID='H1'

    GROUP BY REF_ID;

I tried to modify like below:

SELECT

    CASE WHEN (SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END)+(case when reason = 'training' then paid else 0 end ))>1440 THEN

    ((SUM(CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END)+(case when reason = 'training' then paid else 0 end ))-1440) ELSE 0

    END SINGLE_RATE

    FROM STG WHERE REF_ID='H1'

    GROUP BY REF_ID;

but getting below error:

    ORA-00979: not a GROUP BY expression

    00979. 00000 -  "not a GROUP BY expression"

    *Cause:

    *Action:

Also tried like:

SELECT

    (sum ( (CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END) + case when reason = 'training' then paid else 0 end )-1440) SINGLE_RATE

    FROM STG WHERE REF_ID='H1'

    GROUP BY REF_ID

    having sum ( (CASE WHEN (ACTUAL)>480 THEN 480 ELSE (ACTUAL) END) + case when reason = 'training' then paid else 0 end )>1440;

but the paid amount is not getting calculated. Only actual is added.

Please give your suggestions.

From your business rules, you always want to include actual in the sum() calculation but only include paid when the reason is 'training'. That rule is quite easy to express in a case statement. In addition you have a cap that actual cannot exceed 480. That rule can be satisfied with a least() function:

select ref_id

      , sum ( least(actual, 480) + case when reason = 'training' then paid else 0 end ) as tot

from stg

group by ref_id

/

To be frank I am still not clear what you are trying to achieve with the 1440; life would be easier if you had posted some sample data which covered all the cases you wanted to handle and your desired output derived from that sample data. But I am going to guess anyway:

with cte as (

    select ref_id

          , sum ( least(actual, 480) + case when reason = 'training' then paid else 0 end ) as tot

    from stg

    group by ref_id

    )

select ref_id

       case 

          when tot <= 1440 then tot

          else tot - 1440

       end as adjusted_tot

from cte

/

As before, if this does not provide the answer you want please edit your question. The clearer your requirement the more likely you are to get a timely answer.