[oracle]automatisch nieuwe tabellen met dagen aanmaken

[Verwijderd]

Hoi,

Ik ben een tijdje bezig met SQL. Ik moet een rapport maken die de uren laat zien van de werknemers over een heel jaar. Dit moet van het jaar 2001 t/m 2004 zijn. Om nou 365 keer 4 de dagen apart in te voeren is niet te doen en de query zou dan te lang worden. (lekker traag wordt het dan )
Is er misschien een oplossing dat Oracle zelf de dagen genereert?

hier een vb'tje van mijn SQL code:

code:

select
TO_CHAR(SYSDATE, 'DD-Mon-YYYY')||to_char(sysdate, ' HH24:MI:SS') "created on", 
uren.omschrijving, uren.Dag1, uren.uren1, 
uren.Dag11, uren.uren11, 
uren.Dag12, uren.uren12, 
uren.Dag13, uren.uren13,  
uren.Dag14, uren.uren14,  
uren.Dag15, uren.uren15,
uren.Dag16, uren.uren16, (uren.uren1+uren.uren11+uren.uren12+uren.uren13+uren.uren14+uren.uren15+uren.uren16) totaal
from
(select uw.w_omschrijving omschrijving
,   to_char(to_date('10-JAN-2004'), 'day', 'nls_date_language = dutch') Dag1
,   to_char(to_date('11-JAN-2004'), 'day', 'nls_date_language = dutch') Dag11
,   to_char(to_date('12-JAN-2004'), 'day', 'nls_date_language = dutch') Dag12
,   to_char(to_date('13-JAN-2004'), 'day', 'nls_date_language = dutch') Dag13
,   to_char(to_date('14-JAN-2004'), 'day', 'nls_date_language = dutch') Dag14
,   to_char(to_date('15-JAN-2004'), 'day', 'nls_date_language = dutch') Dag15
,   to_char(to_date('16-JAN-2004'), 'day', 'nls_date_language = dutch') Dag16
,   sum(decode(uw.w_omschrijving,test1.omschrijving,decode(to_date(test1.datum2),to_date('10-1-2004'),test1.uren1,'0'),'0')) uren1
,   sum(decode(uw.w_omschrijving,test1.omschrijving,decode(to_date(test1.datum2),to_date('11-1-2004'),test1.uren1,'0'),'0')) uren11
,   sum(decode(uw.w_omschrijving,test1.omschrijving,decode(to_date(test1.datum2),to_date('12-1-2004'),test1.uren1,'0'),'0')) uren12
,   sum(decode(uw.w_omschrijving,test1.omschrijving,decode(to_date(test1.datum2),to_date('13-1-2004'),test1.uren1,'0'),'0')) uren13
,   sum(decode(uw.w_omschrijving,test1.omschrijving,decode(to_date(test1.datum2),to_date('14-1-2004'),test1.uren1,'0'),'0')) uren14
,   sum(decode(uw.w_omschrijving,test1.omschrijving,decode(to_date(test1.datum2),to_date('15-1-2004'),test1.uren1,'0'),'0')) uren15
,   sum(decode(uw.w_omschrijving,test1.omschrijving,decode(to_date(test1.datum2),to_date('16-1-2004'),test1.uren1,'0'),'0')) uren16
from 
    uurreg.t_werksoort uw,
    (select 
        uw.w_omschrijving Omschrijving 
    ,   ug.reg_datum Datum2 
    ,   ug.reg_uren uren1
    ,   ap.naam naam
    from 
        uurreg.t_reguur ug 
    ,   uurreg.t_werksoort uw 
    ,   alg.t_personeel ap 
    where 
    ug.reg_datum between '01-JAN-2004' and '31-DEC-2004' 
    and ug.reg_personeelcode = ap.personeel 
    and uw.w_code = ug.reg_werksoortcode 
    and ap.naam = 'Berg' 
    order by ug.reg_datum   ) test1
where  uw.w_omschrijving  = test1.omschrijving (+) 
group by
uw.w_omschrijving) uren
order by uren.omschrijving

hier staat 1 week gedifinieerd. bij 365 dagen zou het dus erg lang zijn

ik heb met google gezocht, met google op deze site gezocht maar ik vond geen echte oplossingen..

[ Voor 11% gewijzigd door Verwijderd op 09-04-2004 11:18 ]

[whoami]

Het lijkt me dat er iets grondigs aan jouw datamodel scheelt.
Hoe ziet jouw datamodel er uit?

[P_de_B]

Kin je dit datamodel nog wijzigen? Het lijkt me dat dat niet helemaal klopt.

_{grr, die snelle mods ook}

[ Voor 22% gewijzigd door P_de_B op 09-04-2004 11:22 ]

[Verwijderd]

t_werksoort:
W_CODE,
W_OMSCHRIJVING

t_reguur:
REG_CODE,
REG_PERSONEELCODE,
REG_DATUM,
REG_VESTIGINGCODE,
REG_DEBICODE,
REG_WERKSOORTCODE,
REG_DOSSIER,
TMP_UREN,
TMP_UREN1,
REG_UREN

t_personeel:
PERSONEEL,
AANHEF,
NAAM,
VOORNAAM,
DATUM_INDIENST,
DATUM_UITDIENST,

[P_de_B]

Ok, je datamodel lijkt toch goed, wat jij nodig hebt is zgn. pivot functionaliteit. Of dat standaard in Oracle zit weet ik niet, wellicht dat het beter is hier Excel of bijv. Crystal Reports voor te gebruiken?

[Verwijderd]

ik moet met query's gaan werken en dit toepassen in het programma Rapport Designer.

pivot kan ik niet vinden in de help van TOAD

[ Voor 26% gewijzigd door Verwijderd op 09-04-2004 11:53 ]

[cameodski]

Hoe moet dat rapport er uit gaan zien. Het lijkt me toch een beetje moeilijk om 365 * 4 (+ 1 keer een schrikkeljaar ) kolommen naast elkaar te tonen.

[P_de_B]

Als ik je goed berijp wil je een tabel als volgt

code:

            dag1, dag2, dag3
werknemer 1    1         4       8
werknemer 2     10      12    10 
werknemer 3

Helaas ken ik Oracle niet goed genoeg, als bovenstaand resultaat wel is wat je ongeveer bedoelt kan ik je alleen aandragen te [google=oracle pivot]

[Verwijderd]

P_de_B schreef op 09 april 2004 @ 11:57:
Als ik je goed berijp wil je een tabel als volgt

code:

            dag1, dag2, dag3
werknemer 1    1         4       8
werknemer 2     10      12    10 
werknemer 3

Helaas ken ik Oracle niet goed genoeg, als bovenstaand resultaat wel is wat je ongeveer bedoelt kan ik je alleen aandragen te [google=oracle pivot]

zo moet het eruit komen te zien.
ipv werknemer is het werksoort. dat zijn 49 werksoorten onder elkaar.

[cameodski]

Verwijderd schreef op 09 april 2004 @ 12:23:
[...]


zo moet het eruit komen te zien.
ipv werknemer is het werksoort. dat zijn 49 werksoorten onder elkaar.

Dus je wilt zo'n 1500 kolommen naast elkaar zien? Lijkt me niet erg overzichtelijk.

[Verwijderd]

cameodski schreef op 09 april 2004 @ 12:45:
[...]

Dus je wilt zo'n 1500 kolommen naast elkaar zien? Lijkt me niet erg overzichtelijk.

uhmm
ik bedoel dat er 4 x365 dagen moeten invoeren... maar de opdrachtgever bepaalt welk jaar hij wilt zien. dus hij krijgt dan 1 jaar te zien

[cameodski]

Verwijderd schreef op 09 april 2004 @ 12:54:
[...]


uhmm
ik bedoel dat er 4 x365 dagen moeten invoeren... maar de opdrachtgever bepaalt welk jaar hij wilt zien. dus hij krijgt dan 1 jaar te zien

Dat betekent dat hij "slechts" 365 of 366 kolommen te zien krijgt? Dan heb je nog steeds A0-papier (of zoiets) nodig.

[ Voor 3% gewijzigd door cameodski op 09-04-2004 12:57 ]

[Verwijderd]

Maak een Stored Procedure met als parameter bv jaar en week. In de procedure bereken je de 7 datums in variabelen, die je weer in de query gebruikt.

[Verwijderd]

cameodski schreef op 09 april 2004 @ 12:57:
[...]

Dat betekent dat hij "slechts" 365 of 366 kolommen te zien krijgt? Dan heb je nog steeds A0-papier (of zoiets) nodig.

weet jij een goede site waar je plaatjes kan uploaden? dan kan ik wel eentje uppen.

code:

Naam werknemer   week 1                             week 2
                    zo ma di wo do vr tot          zo ma di wo do vr tot  
werksoort 1
werksoort 2
werksoort 3
werksoort 4
werksoort n

totaal                                    tot                                       tot

dit is dan pagina 1
dus 2 weken op 1 pagina

in totaal dus ongeveer 28 pagina's per werknemer.
zo wilt hij het hebben

Verwijderd schreef op 09 april 2004 @ 12:59:
Maak een Stored Procedure met als parameter bv jaar en week. In de procedure bereken je de 7 datums in variabelen, die je weer in de query gebruikt.

uhm? is dit nog in sql?
dit ken ik namelijk niet

[ Voor 25% gewijzigd door Verwijderd op 09-04-2004 13:09 ]

[P_de_B]

Je hebt 2 keuze's

Of je schrijft ranzige SQL code die de functie van een Pivot table kopieert (op SQLTeam.com is wel 1 te vinden voor SQL Server), of je gebruikt 1 van de vele pivot tools die beschikbaar zijn. Is het niet mogelijk het niet in SQL code te doen?

[cameodski]

P_de_B schreef op 09 april 2004 @ 13:14:
Je hebt 2 keuze's

Of je schrijft ranzige SQL code die de functie van een Pivot table kopieert (op SQLTeam.com is wel 1 te vinden voor SQL Server), of je gebruikt 1 van de vele pivot tools die beschikbaar zijn. Is het niet mogelijk het niet in SQL code te doen?

Waarom vind je dat dan ranzig. Als het aantal kolommen beperkt blijft vind ik het nog wel meevallen met de ranzigheid en het is vaak wel goed voor de performance, omdat het RDBMS het meestal toch wel wat efficienter kan uitvoeren.

[P_de_B]

cameodski schreef op 09 april 2004 @ 13:37:
[...]

Waarom vind je dat dan ranzig. Als het aantal kolommen beperkt blijft vind ik het nog wel meevallen met de ranzigheid en het is vaak wel goed voor de performance, omdat het RDBMS het meestal toch wel wat efficienter kan uitvoeren.

Ik doelde op een proc die zelf 'pivotten' kan. Ranzig is misschien niet het juiste woord. Mijn punt is dat er veel tools zijn die op basis van een eenvoudige query goed kunnen pivotten. Waarom zou je zelf in SQL die functionaliteit willen evenaren?

[cameodski]

P_de_B schreef op 09 april 2004 @ 13:39:
Ik doelde op een proc die zelf 'pivotten' kan. Ranzig is misschien niet het juiste woord. Mijn punt is dat er veel tools zijn die op basis van een eenvoudige query goed kunnen pivotten. Waarom zou je zelf in SQL die functionaliteit willen evenaren?

Waarom ik dat zou willen?
Omdat ik in het verleden tegen fikse beperkingen van Crystal Reports aan ben gelopen en toen de ontdekking kwam dat je dit op vrij eenvoudige wijze in sql kon doen.
Ik had toen bijvoorbeeld een rapport waarbij ik een lijstje met klanten had (rijen) en per klant moest de omzet op maand (kolommen) uitgesplitst worden.
Wij hebben dat toen eerst mbv Crystal Reports geprobeerd, maar dan werden klanten die geen omzet hadden gewoon weggelaten.
Mbv sql (stored procedure in MSSQL) waren deze problemen binnen de kortste keren opgelost.

Het zou natuurlijk best kunnen dat er tools zijn die het beter kunnen, maar mbv sql werkte het naar tevredenheid en even alle rapportjes ombouwen naar eentje die dat wel goed kan, maar iets anders misschien weer niet, was niet echt een optie.

P.S. In Crystal Reports noemen ze het volgens mij een cross table.

[ Voor 12% gewijzigd door cameodski op 09-04-2004 13:52 ]

[justmental]

Als je Oracle reports gebruikt kijk dan naar een matrix report.

[P_de_B]

cameodski schreef op 09 april 2004 @ 13:51:
[...]

Waarom ik dat zou willen?
Omdat ik in het verleden tegen fikse beperkingen van Crystal Reports aan ben gelopen en toen de ontdekking kwam dat je dit op vrij eenvoudige wijze in sql kon doen.
Ik had toen bijvoorbeeld een rapport waarbij ik een lijstje met klanten had (rijen) en per klant moest de omzet op maand (kolommen) uitgesplitst worden.
Wij hebben dat toen eerst mbv Crystal Reports geprobeerd, maar dan werden klanten die geen omzet hadden gewoon weggelaten. supress empty rows?
Mbv sql (stored procedure in MSSQL) waren deze problemen binnen de kortste keren opgelost.

Het zou natuurlijk best kunnen dat er tools zijn die het beter kunnen, maar mbv sql werkte het naar tevredenheid en even alle rapportjes ombouwen naar eentje die dat wel goed kan, maar iets anders misschien weer niet, was niet echt een optie.

P.S. In Crystal Reports noemen ze het volgens mij een cross table.

Misschien zijn er wel beperkingen aan de cross tab (zo heet 'ie) van Crystal Reports, ik ben er wel tevreden over.

[Verwijderd]

Een truc die ik bij dit soort problemen vaker heb toegepast, is het definieren van een tabel met vaste waarden. Hier bijvoorbeeld een tabel JaarDagen met in elke rij een datum van het betreffende jaar. Met deze tabel kan je dan joinen om de waarden per dag te sommeren. Als je geen tabel wil definieren kan je deze in de query samenstellen met bv SELECT ...1-JAN-2004.. UNION SELECT ..2-JAN-2004... UNION ..enzovoort t/m 31-DEC-2004.