CONSULTA SQL PARA OBTER A SOMA DAS COLUNAS ENTRE DATAS COMO SUBCONSULTA

Question 1

Eu tenho que criar uma consulta que mostra se os funcionários tem levado a que tipo de licença no cartão de tempo.

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)

Isso me dá a saída, como entre p_from_date - 01-Jan-2021 e p_to_date - 31-Jul-2021 como -

  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

Agora querer ajustar a consulta, de modo a dar a saída, como -

 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 e Others_measure deve ter a soma destes valores.

Existe uma maneira de ajustar a minha consulta sem utilizar subconsultas ? Ou como eu posso fazer isso da forma mais eficaz ?

Question 2

Talvez algo como isto, usando o condicional agregação dinâmica.

Mas eu não tenho certeza sobre o que 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

LukStorms · Answer 1 · 2021-11-23T18:37:35

Talvez algo como isto, usando o condicional agregação dinâmica.

Mas eu não tenho certeza sobre o que 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

CONSULTA SQL PARA OBTER A SOMA DAS COLUNAS ENTRE DATAS COMO SUBCONSULTA

Pergunta

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria