SQL opzoeken in periode indeling tabel

[W4rren]

Mijn vraag
Voor mijn werk probeer ik met een query om een periode nummer toe te voegen aan een tabel met orderregels. De moeilijkheid hierin is dat wij met 13 periodes van 4 weken werken. Hoe voeg ik aan onderstaande tabel orderregels het periode nummer toe op basis van de orderdatum die binnen de datum in en datum uit ligt?

De tabellen bevatten o.a. de volgende data:

Tabel: Orderregels

Product	Orderregel	Orderdatum

Tabel: Periode indeling

Datum_in	Datum_uit	Periode

Relevante software en hardware die ik gebruik
Excel SQL query en ingebouwde SQL query bouwer in Multivers Extended.

Wat ik al gevonden of geprobeerd heb
Mijn kennis van SQL zorgt ervoor dat ik basis queries kan maken maar dit probleem krijg ik niet opgelost aangezien mijn kennis te beperkt is en ik niet weet waar ik het moet zoeken. Een link naar een website of iets waar ik zelf kan gaan grutten is overigens ook van harte welkom.

[DamadmOO]

Hetgeen wat je hier nodig hebt is een "Between-join". Deze operatie is vrij traag zijn als je veel data hebt, Als dat voor jou een probleem hebt dan zou ik een extra tabel aanmaken waar je alle datums aan de juiste periode koppelt zodat je een gewone join kan gebruiken.

Een voorbeeld van een "Between-join" kan je vinden op: http://dba.stackexchange....value-between-min-and-max

[MAX3400]

Niet? Ik zie geen enkele "link" tussen de twee tabellen; je had allicht even de code kunnen posten op basis waarvan je nu bezig bent om de orderregel proberen te vullen.

DamadmOO schreef op zondag 20 maart 2016 @ 09:04:
Hetgeen wat je hier nodig hebt is een "Between-join". Deze operatie is vrij traag zijn als je veel data hebt

13 periodes per jaar; als een SQL-bak geen 13 regels kan querien, dan heeft topicstarter sowieso wel een major uitdaging

[DamadmOO]

MAX3400 schreef op zondag 20 maart 2016 @ 09:05:

[...]

13 periodes per jaar; als een SQL-bak geen 13 regels kan querien, dan heeft topicstarter sowieso wel een major uitdaging

Dat klopt, maar ik heb between joins gezien naar tabellen met meer dan 1 miljoen regels. En dan kan je makkelijk koffie drinken terwijl de query bezig is Het was dus meer bedoelt voor anderen die een keer dit topic vinden.

[RobIII]

DamadmOO schreef op zondag 20 maart 2016 @ 09:13:
[...]


Dat klopt, maar ik heb between joins gezien naar tabellen met meer dan 1 miljoen regels. En dan kan je makkelijk koffie drinken terwijl de query bezig is

Ook onzin. Er zal wel een index ontbreken dan

[Juup]

SQL:

... WHERE Orderdatum > Datum_in AND Orderdatum < Datum_uit

?

[ajakkes]

Round.up(weeknum(date)/4)?

Of heb je meer flexibiliteit nodig?

Let wel op dat weeknummers volgens de ISO standaard berekend moeten worden.

[Hydra]

Waarom doe je dit in je database? Je hebt de orderdatum. Daarvan het weeknummer laten zien is een kwestie van datum/tijd formatting. Dat doe je normaal in je applicatie.

Don't mind me...

[ Voor 7% gewijzigd door Hydra op 21-03-2016 14:21 ]

[ajakkes]

Nee, hij wil van periode 5, namelijk week 17 tm week 20 alle orders tonen en totalen van weergeven. En mogelijk is periode 13 afhankelijk van het jaar 5 weken of gevolgd door een periode 14 van 1 week.

[Skinny]

Er vanuitgaande dat die periode tabel geen overlappende periodes bevat kun je jezelf afvragen : Hoe zoek ik zelf een Periode op bij een gegeven OrderRegel ?

Door de OrderDatum te nemen en te kijken bij welke periodes deze datum tussen DatumIn en DatumUit ligt (zie ook Jaaap hierboven)

SQL:

SELECT ol.*, p.periode
FROM orderregels ol
  INNER JOIN periodes p ON  (....)

Probeer vervolgens deze logica uit te schrijven in een join zodat je voor elke OrderRegel netjes de periode terugkrijgt.

Succes!

spoiler:

http://sqlfiddle.com/#!9/75e3f/1

[Hydra]

ajakkes schreef op maandag 21 maart 2016 @ 13:53:
Nee, hij wil van periode 5, namelijk week 17 tm week 20 alle orders tonen en totalen van weergeven. En mogelijk is periode 13 afhankelijk van het jaar 5 weken of gevolgd door een periode 14 van 1 week.

Thanks! Te vlug gelezen.

[MSalters]

Het makkelijkste is je periodes 201601 tot en met 201613 te nummeren, en volgend jaar 201701 tot en met 201713. Data zijn lineair, niet cyclisch. Mocht je ergens toch een periode zonder jaar nodig hebben dan is dat simpelweg "modulo 100".

[Philip J. Dijkstra]

Als de periode gemaakt is op basis van (iso) weken staan er verschillende functies en formules klaar om je te helpen van een orderdatum een periode te maken zonder join.

(datepart(year, orderdatum)*100) + ROUND(((datepart(iso_week, orderdatum)/4)+0.5),0)

Aantal weken delen door 4 plus een half om altijd omhoog af te ronden omdat we dan ik de volgende periode zitten.

Dus eigenlijk wat Ajakkes zegt... zie ik nu

[Croqy]

De periodetabel is al ingericht met start en einddatum, dus een simpele join naar de periodetabel op basis van de orderdatum groter dan startdatum en kleiner dan einddatum volstaat, zoals Jaaap hierboven aangaf.
Maar als je de week op basis van de iso laat bepalen heb je uberhaupt geen join naar de periodetabel nodig. De periode bereken je dan zelf.

[W4rren]

Bedankt voor alle tips. Op dit moment los ik dit op in excel door een hulptabel met een vlookup maar ik zou graag het aantal vlookups zo min mogelijk houden.

Ik ben bang dat ik moet uitwijken naar een betere SQL query editor. In Microsoft query in excel kan ik geen joins toevoegen als ik al gegevens uit meerdere tabellen select. De Query editor in ons ERP systeem gaat dit misschien wel doen dus daar ga ik dit proberen.

Wat betreft de berekening. Eens zien of dit ook een oplossing kan zijn.

Nogmaals bedankt voor de tips.

[Jean Paul]

Er is niks mis met hulptabellen in Excel, zolang je niet te maken hebt met enorme hoeveelheden data en lange refresh-tijden is het prima

Volgens mij geeft Jaaap ook het juiste antwoord. Uitgewerkt is dat iets van:

code:

SELECT t2.Periode
     , t1.Product
     , t1.Orderregel
     , T1.Orderdatum
FROM   Orderregels t1
     , [Periode indeling] t2
WHERE  t1.Orderdatum >= t2.Datum_in
AND    t1.Orderdatum <= t2.Datum_uit

[ Voor 52% gewijzigd door Jean Paul op 23-03-2016 15:31 ]

[W4rren]

Juup schreef op zondag 20 maart 2016 @ 12:09:

SQL:

... WHERE Orderdatum > Datum_in AND Orderdatum < Datum_uit

?

Dankjewel Jaap. Het is mij nu duidelijk hoe dit werkt. Ik heb thuis met SQL workbench geoefend om dit werkend te krijgen en mijn kennis is nu weer een beetje opgekrikt. Hopelijk krijg ik dit op het werk nu ook voor elkaar.