ZAPYTANIA SQL W CELU UZYSKANIA SUMY KOLUMN MIĘDZY DATAMI JAKO PODRZĘDNEGO

Question 1

Muszę utworzyć kwerendę, która pokazuje, czy wzięli pracownicy któreś wakacje na osi mapie.

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)

To daje mi wynik, na przykład, między p_from_date - 01-stycznia-2021 p_to_date - 31-lipca-2021, jak -

  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

Teraz chcesz skonfigurować zapytanie w taki sposób, aby wniosek był podobny -

 Person_Number          Overtime_measure_hours          Regular_Measure_hours        Others_code        Others_measure   
   101928                   18                          15                            Double_Time         34

czyli Overtime_measure_hours, Regular_Measure_hours i Others_measure muszą mieć sumę tych wartości.

Czy istnieje sposób, aby dostosować mój wniosek bez korzystania z podzapytania ? Lub jak mogę to zrobić w najbardziej efektywny sposób ?

Question 2

Może być coś takiego, korzystając z warunkowej agregację do obrotu.

Ale nie jestem pewien w tym czasie połączenia.

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

Może być coś takiego, korzystając z warunkowej agregację do obrotu.

Ale nie jestem pewien w tym czasie połączenia.

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

ZAPYTANIA SQL W CELU UZYSKANIA SUMY KOLUMN MIĘDZY DATAMI JAKO PODRZĘDNEGO

Pytanie

Najlepsza odpowiedź

W innych językach

Ta strona jest w innych językach

Popularne w tej kategorii

Najczęściej zadawane pytania w tej kategorii