REQUÊTE SQL POUR OBTENIR LA SOMME DES COLONNES ENTRE LES DATES EN TANT QUE SOUS-REQUÊTE

Question 1

J'ai créer une requête qui montre que l'employé a pris ce type de congé sur la carte de temps.

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)

Cela me donne de la sortie comme entre p_from_date - 01-Jan-2021 et p_to_date - 31-Juil-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

Maintenant souhaitez modifier la requête de manière à donner à la sortie comme -

 Person_Number          Overtime_measure_hours          Regular_Measure_hours        Others_code        Others_measure   
   101928                   18                          15                            Double_Time         34

c'est à dire Overtime_measure_hours, Regular_Measure_hours et Others_measure devrait avoir la somme de ces valeurs.

Est-il un moyen de peaufiner ma requête sans l'aide de sous-requêtes ? Ou comment puis-je faire dans la manière la plus efficace ?

Question 2

Peut-être que quelque chose comme cela, à l'aide conditionnelle de l'agrégation de pivot.

Mais je ne suis pas sûr que la croix rejoindre.

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

Peut-être que quelque chose comme cela, à l'aide conditionnelle de l'agrégation de pivot.

Mais je ne suis pas sûr que la croix rejoindre.

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

REQUÊTE SQL POUR OBTENIR LA SOMME DES COLONNES ENTRE LES DATES EN TANT QUE SOUS-REQUÊTE

La question

La meilleure réponse

Dans d'autres langues

Cette page est dans d'autres langues

Populaires dans cette catégorie

Questions populaires dans cette catégorie