[SQL - ORACLE 9i] lastige group by

[Verwijderd]

Ik ben bezig met een query die een jaar in bepaalde periodes moet opkappen.

De periodes zijn afhankelijk van
1) tewerkstellingslocatie (bepaalde rechten voor de eindperiode hangen af van de plaats van tewerkstelling)
2) werkformule (part time of full time krijgen andere berekeningen)
3) statutaire groep (verschillende groepen hebben verschillende rechten)


ik heb in ORACLE bijvoorbeeld dit:

SQL:

WITH twps AS (--first collect the possible workpattern periods.
   SELECT twp.per_id
      , twp.twp_id
      , GREATEST(twp.work_pattern_date_from, :p_date_from) date_from
      , LEAST(NVL(twp.work_pattern_date_to, :p_date_to) 
            , :p_date_to) date_To
      , twp.twt_id
      , twp.flexitime_flag    
      , twt.tar_week_hours_duration_pct
      , twt.ttc_cnt, twt.ttc_oth_id_unit_type
     FROM work_patterns twp
    INNER JOIN work_pattern_Types twt on twt.twt_id = twp.twt_id
    WHERE twp.per_id = :p_per_id
      AND twp.date_to IS NULL -- go and see the final situation!!
) 
-- AND then get the intersects of the workpattern periods with the carreer.
-- statutory link changes can have impact on the calculation
SELECT twp.per_id
      , twp.twp_id
      , GREATEST(twp.date_from, slk.slk_beg_date) intersect_from
      , LEAST(twp.date_to 
            , LEAST(NVL(slk.slk_end_date, :p_date_to), :p_date_to)
            ) intersect_to
      , twp.twt_id
      , twp.flexitime_flag    
      , twp.tar_week_hours_duration_pct
      , twp.ttc_cnt, twp.ttc_oth_id_unit_type
      , slk.slk_cd, skg.skg_id
  FROM twps twp   
 INNER JOIN career_statutory_links slk
       ON slk.per_id = twp.per_id
      AND NVL(slk.slk_end_date, :p_date_to) >= twp.date_from
      AND slk.slk_beg_date <= twp.date_to  
      AND slk.cet_seq_nbr_to IS NULL--has to be the latest version...
 INNER JOIN REF_SLK_GROUP_ITEMS skgi 
            ON skgi.slk_cd = slk.slk_cd
 INNER JOIN SP2_REF_STATUTORY_LINK_GROUPS skg 
            ON skg.skg_id = skgi.skg_id
           AND skg.time_flag = 'Y'

Die haalt me eerst de 'werkformules' op voor dat jaar.
Adhv de periodes van de werkformules wordt gelinkt met de carière om de statutaire groep te bepalen in de periode. Daar haal ik dan de verschillende periodes uit.
Dat werkt goed... de periodes zijn duidelijk onderscheiden.
Ik zou ook direct daarna hetzelfde principe kunnen toepassen voor de tewerkstellingslocaties die ook opgeslagen zitten in de DB.

MAAR!!!

je kan dan opeenvolgende periodes krijgen met
1) hetzelfde type werkformule
2) dezelfde statutengroep (omdat het statuut zelf gewijzigd is, maar de groep niet).
en later zelfs
3) dezelfde tewerkstellingslocatie omdat de persoon van JOB verandert is maar nog steeds een job in (bijvb) Brussel heeft.

Dus heb ik meer periodes met dezelfde eigenschappen en berekeningswijze.

Op zich geen probleem.
maar de rechten (verlof) worden pro rata berekend op die periode.

jaarlijks verlof geeft voor sommige situaties recht op:
2 dagen voor een volledige maand
2 dagen voor een begonnen maand

terwijl voor andere recht types/situaties
2 dagen voor een volledige maand
1 dag voor alles minder dan een halve maand
1.5 dag voor alles méér dan een halve maand (maar geen volledige).

Dus als de periodes opgekapt zijn zouden de rechten verkeerd berekend worden.

vb:

code:

DATE_FROM   DATE_TO   WORK_PATTERN_TYPE    STATUTE_GROUP    LOCATION_GROUP
-----------------------------------------------------------------------------------------------------------------------------------------------
01/01/2006       21/03/2006     1 (full time)                        5                             1 (EU)
22/03/2006       11/06/2006     1 (full time)                        5                             1 (EU)
12/06/2006       11/11/2006     1 (full time)                        5                             2 (non-EU)
12/11/2006       31/12/2006     1 (full time)                        5                             1 (EU)

dus de eerste 2 hebben dezelfde situatie (maar het contract van die persoon is vernieuwd of zijn statuut is gewijzigd binnen dezelfde groep of ... (nog enkele andere opties).
Voor Maart (maand 3) zou hij dan 2*2 dagen verlof krijgen terwijl het er maar 2 mogen zijn (het geheel moet gezien worden als één periode).

Ik kan geen group by met min(date_from) en max(date_from) doen aangezien ik dan een periode voor het hele jaar ga hebben (situatie 1 en situatie 4 zijn dezelfde => 01/01 - 31/12).


Ik heb reeds geprobeerd om een 'helper package' te creëren die de linkgroup of locationgroep bijhoudt en bij de WHERE een reset van de inhoud van die package deed.
MAAR, die WHERE wordt niet op het einde uitgevoerd maar afhankelijk van het explain plan (en dus van DB statistics).
Maar dan voegde ik aan elke rij dit toe:
HELPER_PACKAGE.is_same_as_previous(linkgroup)

die me een number teruggeeft dat oploopt. Maar het loopt enkel op als previous NOT NULL is en niet gelijk aan de gegeven parameter.
Maar op het einde van de SQL moet ik de bijgehouden variabelen van die package resetten en dat lukt al niet door het EXPLAIN PLAN.


Ik vrees dat het onmogelijk is, maar wou toch een laatste gok wagen en zet mijn joker in voor de (ORACLE) SQL goeroes hier.

Misschien even ook vermelden dat dit allemaal in een PL/SQL package plaatsvind aangezien die rechtenberekening teveel data manipuleert/creeert/wijzigt om vanuit JAVA te doen.

[justmental]

Waarom wil je dit in SQL oplossen?
Zo te zien ga je het aansluitend zijn van de periodes controleren in PL/SQL en dat weer in SQL gebruiken

Of moet er soms een cursor terug gegeven worden aan java?

[Verwijderd]

justmental schreef op donderdag 26 oktober 2006 @ 10:10:
Waarom wil je dit in SQL oplossen?
Zo te zien ga je het aansluitend zijn van de periodes controleren in PL/SQL en dat weer in SQL gebruiken

Of moet er soms een cursor terug gegeven worden aan java?

Omdat SQL mijn sterkste kant is.
Ik ben JAVA programmer die (door gebrek aan resources) PL/SQL moet schrijven.
Momenteel zit die PL/SQL vol zware SQL met dan de bijhorende INSERT/UPDATE queries maar gebeurt er vrij weinig spannend in de PL/SQL code omdat ik mezelf daar niet genoeg in vertrouw.
Het gaat niet terug naar JAVA want dat zou teveel keer heen en weer java<=>BD betekenen.

Ook hoopte ik performanter te zijn via 1 SQL.
Maar ik ben ondertussen al verschilende tables aan het inlezen en met for loops overlaps aan het zoeken en distinct periods aan een andere table toevoegen. Ik vind dat een lastig gedoe, in JAVA maak je dan een PERIOD object met overlaps, intersect en andere methoden die je code proper en leesbaar maken maar PL/SQL lijkt me die mogelijkheid niet te geven (ik heb géén pl/sql (of gelijk welke) cursus gekregen, heb enkel 1 boek gelezen van Feurestein, dus mijn kennis is relatief klein).


EDIT en de periodes moeten voor mij nie taansluitend zijn.
Als die persoon geen job heeft in die periode krijgt hij ook geen rechten. Dus kunnen er gaps in zitten.
Ik wil wel mooi de periodes zoals ik ze nodig heb uit 1 query halen maar dat gaat niet met een eenvoudige GROUP BY.

[ Voor 10% gewijzigd door Verwijderd op 26-10-2006 10:16 . Reden: verduidelijking ]

[Verwijderd]

hmm, om te bewijzen dat het eventueel wel zou kunnen :

SQL:

WITH hier AS (
select jos.*, level lvl--, DECODE(PRIOR rlg_id, rlg_id, PRIOR seq, seq) priorSEQ
,SUBSTR(SYS_CONNECT_BY_PATH(jos_id, '.'),2) suivi -- chop of the starting '.'   !!
from (
SELECT jos.jos_id, jos.date_from, jos.date_to, job.cty_cd_assign, jos.job_id, jos.per_id
, rlgi.rlg_id-- decode to BMT beaumont usa or BRU belgium
 from job_occupation_statuses jos
inner join jobs job on job.job_id = jos.job_id
inner join crf_cities cty on cty.cty_cd = job.cty_cd_assign
inner join ref_location_group_items rlgi 
      on (rlgi.cnt_cd = cty.cnt_Cd
         OR rlgi.cty_cd = cty.cty_Cd)
     AND rlgi.rlg_id IN (1,2)
where jos.per_id = 63498   -- 47890
  ANd jos.date_from <= :p_date_to
  AND NVL(jos.date_to, :p_date_from) >= :p_date_from
ORDER BY jos.date_from ASC
) jos
connect by prior date_to = date_from -1
                 AND PRIOR rlg_id = rlg_id
--               AND prior 
start with 1=1--1!= DECODE(SIGN(rownum - 2000), -1, 0, 1)
), periods AS (select jos_id, datE_from, date_to, rlg_id
 , max(lvl) lvl
from hier 
/*WHERE (INSTR(suivi, '.') = 0
       OR lvl>0)*/
group by jos_id, rlg_id, datE_from, date_to
) select MIN(periods.date_from), MAX(periods.date_to), periods.rlg_id, SUBSTR(suivi, 
        1
        , DECODE(
                INSTR(suivi,'.',2)
                , 0
                ,length(suivi)
                ,INSTR(suivi,'.',2)-1
                )
        ) 
  from periods
inner join (
select jos.*, level lvl--, DECODE(PRIOR rlg_id, rlg_id, PRIOR seq, seq) priorSEQ
,SUBSTR(SYS_CONNECT_BY_PATH(jos_id, '.'),2) suivi -- chop of the starting '.'   !!
from (
SELECT jos.jos_id, jos.date_from, jos.date_to, job.cty_cd_assign, jos.job_id, jos.per_id
, rlgi.rlg_id-- decode to BMT beaumont usa or BRU belgium
 from job_occupation_statuses jos
inner join jobs job on job.job_id = jos.job_id
inner join crf_cities cty on cty.cty_cd = job.cty_cd_assign
inner join ref_location_group_items rlgi 
      on (rlgi.cnt_cd = cty.cnt_Cd
         OR rlgi.cty_cd = cty.cty_Cd)
     AND rlgi.rlg_id IN (1,2)
where jos.per_id = 63498   -- 47890
  ANd jos.date_from <= :p_date_to
  AND NVL(jos.date_to, :p_date_from) >= :p_date_from
ORDER BY jos.date_from ASC
) jos
connect by prior date_to = date_from -1
                 AND PRIOR rlg_id = rlg_id
--               AND prior 
start with 1=1--1!= DECODE(SIGN(rownum - 2000), -1, 0, 1)
) hier on periods.jos_id = hier.jos_id
  AND periods.lvl = hier.lvl
group by periods.rlg_id,
SUBSTR(suivi, 
        1
        , DECODE(
                INSTR(suivi,'.',2)
                , 0
                ,length(suivi)
                ,INSTR(suivi,'.',2)-1
                )
        )  

waarbij ik opeenvolgende joboccupations hierarchisch connect.
Dan enkel de uiteinden overhoudt.
De ID van de eerste joboccupation van de opeenvolging van identieke periodes toevoeg aan elke rij
En dan de min(date_from) en max(date_to) uitvoer.

Het enige probleem is dat eeuwige copy pasten omdat ORACLE niet intelligent genoeg is om de structuur van die recursieve queries uit te vogelen

wees gerust, de implementatie zal toch in PL/SQL gebeuren

[ Voor 8% gewijzigd door Verwijderd op 26-10-2006 14:02 . Reden: verduidelijking ]

[Siliakus]

Echt, als ik enigzins inzicht had in de terminologie die je hanteert.. werkformules, statuengroepen etc.. Misschien kan je daar eerst wat meer over uitwijden? Is makkelijker om commentaar te geven als de beredenering achter je plsql block duidelijk is

[Verwijderd]

Siliakus schreef op donderdag 26 oktober 2006 @ 22:22:
Echt, als ik enigzins inzicht had in de terminologie die je hanteert.. werkformules, statuengroepen etc.. Misschien kan je daar eerst wat meer over uitwijden? Is makkelijker om commentaar te geven als de beredenering achter je plsql block duidelijk is

dit stuk code moet het aantal dagen verlof per jaar berekenen voor iemand.
Het gaat om verlof voor ambtenaren.

ambtenaren hebben een werkformule.
fulltime (100% van de week), parttime, ...
De rechten moeten pro rata berekend worden tov het type workpattern. (een werkweek van 75% geeft ook maar recht op 75% van de verlofdagen die je normaal in die periode zou krijgen).

ambtenaren hebben ook een statuut. De rechten hangen af van dat statuut. (een bepaald type ambtenaar krijgt 2 dagen per maand, anderen 1.5 dagen per maand, ...).

De ambtenaar heeft ook een plaats van tewerkstelling (persoon > joboccupation > job en de job heeft een stadscode voor de plaats van tewerkstelling). Iemand die buiten de EU werkt krijgt meer verlof per maand dan iemand anders.

Van alle mogelijk periodes uit die 3 eigenschappen van de ambtenaar moeten de periodes die een verschillende berekening geven gehaald worden. Voor deze periode wordt dan een gedeeltelijke berekening gedaan. De som van de rechten van die periodes vormen dan de jaarlijkse rechten.

=> de eigenschappen nodig voor dit deel van de berekening:

DATE_FROM DATE_TO statuutgroep locationgroep workpatterntype

en ik moet alles hergroeperen zodat de periodes echt verschil geven in berekening van rechten.

Dit ter verduidelijking.

Maar je hoeft er je hoofd niet echt meer over te breken (tenzij je er net als ik een uitdaging in ziet ).

De SQL zou echter niet te onderhouden (laat staan lezen) zijn, dus heb ik nu verschillende periodes in PL/SQL tables geladen en ben ik nu de groupering en intersecties aan het maken.
Het komt wel in orde