SQL QUERY TO GET SUM OF COLUMNS BETWEEN DATES AS SUBQUERY

Question

I have to create a query that shows whether the employees has taken what kind of leave on time card.

select papf.person_number,
atrb.attribute_category element,
atrb.measure measure_hours,
rec.start_Date,
rec.end_Date
from per_all_people_F papf,
hwm_tm_rec rec,
fusion.hwm_tm_rep_atrbs atrb,
fusion.hwm_tm_rep_atrb_usages ausage,
hwm_tm_statuses status
where 1=1
       AND atrb.tm_rep_atrb_id = ausage.tm_rep_atrb_id
       AND ausage.usages_source_id = rec.tm_rec_id
       AND ausage.usages_source_version = rec.tm_rec_version
        AND status.tm_bldg_blk_id = REC.tm_rec_id
       AND status.tm_bldg_blk_version = REC.tm_rec_version
       AND REC.tm_rec_type IN ( 'RANGE', 'MEASURE' )
       and papf.person_number = '101928'
       AND Trunc (status.date_to) = To_date ('31/12/4712', 'DD/MM/YYYY')
       and atrb.attribute_category in( 'Overtime','Regular Pay', 'Double_Time')
       and Trunc (sh21.start_time) between trunc(:P_From_Date) and trunc(:P_To_Date)
   

This gives me the output like between p_from_date- 01-Jan-2021 and p_to_date- 31-Jul-2021 as -

  Person_Number          Element         measure_hours        Start_Date        end_date
   101928                Overtime           10                  10-Jan-2021     10-Jan-2021
   101928                Overtime           8                   09-Jul-2021     09-Jul-2021
   101928                Regular Pay        10.9                23-Jan-2021     24-jan-2021 
   101928                Regular Pay        4.1                 01-Jun-2021     01-Jun-2021
   101928                Double_Time        34                  02-Feb-2021     04-Feb-2021
    
   
  

Now want to tweak the query so as to give the output like -

 Person_Number          Overtime_measure_hours          Regular_Measure_hours        Others_code        Others_measure   
   101928                   18                          15                            Double_Time         34
     

i.e. Overtime_measure_hours, Regular_Measure_hours and Others_measure should have the sum of these values.

Is there a way to tweak my query without using subqueries ? Or how can i do this in the most effective way ?

Answer 1

Maybe something like this, using conditional aggregation to pivot.

But I'm not sure about that cross join.

SELECT papf.person_number
, SUM(CASE WHEN atrb.attribute_category = 'Overtime' THEN atrb.measure measure_hours END) AS Overtime_measure_hours
, SUM(CASE WHEN atrb.attribute_category LIKE 'Regular P%' THEN atrb.measure measure_hours END) AS Regular_Measure_hours
, MAX(CASE WHEN atrb.attribute_category NOT IN ('Overtime','Regular Pay') THEN atrb.attribute_category END) AS Others_code
, SUM(CASE WHEN atrb.attribute_category NOT IN ('Overtime','Regular Pay') THEN atrb.measure measure_hours END) AS Others_measure
FROM hwm_tm_rec AS rec
CROSS JOIN per_all_people_F AS papf
JOIN fusion.hwm_tm_rep_atrb_usages AS ausage
  ON ​ausage.usages_source_id = rec.tm_rec_id
​ AND ausage.usages_source_version = rec.tm_rec_version
JOIN fusion.hwm_tm_rep_atrbs AS atrb
  ​ON atrb.tm_rep_atrb_id = ausage.tm_rep_atrb_id
JOIN hwm_tm_statuses AS status
  ON status.tm_bldg_blk_id = rec.tm_rec_id
​ AND status.tm_bldg_blk_version = rec.tm_rec_version
WHERE 1=1
  AND papf.person_number = '101928'
  AND rec.tm_rec_type IN ('RANGE', 'MEASURE') 
  AND TRUNC(status.date_to) = TO_DATE('31/12/4712', 'DD/MM/YYYY')
  AND atrb.attribute_category IN ('Overtime','Regular Pay', 'Double_Time')
  AND TRUNC(sh21.start_time) BETWEEN TRUNC(:P_From_Date) AND TRUNC(:P_To_Date)
GROUP BY papf.person_number
ORDER BY papf.person_number