[SQL] help met een statement...

[codemann]

Ik ga het proberen zo eenvoudig mogelijk te beschrijven :
Ik ben een tool aan het maken voor een hosting bedrijf en momenteel vraag ik alle domeinen van een klant op waar een forwarding aan hangen.
Nu, de tabellen voorzien geen huidige status van een domein, maar de historiek van een domein is een opeenvolging van bestellingen. Elke bestelling die een klant doet heeft een verwijzing naar de tabel met alle registratie-informatie en naar de tabel met alle hosting/fw-informatie. Een vernieuwing van een domein is een kwestie van de laatste bestelling aan een nieuwe factuur te hangen met een paar extra parameters. De facturen worden op die manier al een tijdje gemaakt en dit werkt probleemloos. Ik kan zelfs foutjes die vroeger in de boekhouding gemaakt zijn of speciale gevallen probleemloos erin krijgen.
In ieder geval, ik wil dus de huidige status van het domein opvragen. Ik begon met te kijken naar alle domeinen van die klant die de juiste status hebben en waarvan de URLFW_ID en de MAILFW_ID niet NULL is. Dit geeft me inderdaad alle forwardings terug, maar ook de forwardings die niet meer actief zijn.
Nu, geen probleem, elk record in de tabel met hosting/fw-info heeft een activatiedatum en bij de koppeling van de bestelling aan de factuur voeg ik een factor toe om aan te tonen de hoeveelste vernieuwing dat is hiervan. Dus ik voegde aan m'n lijst van voorwaarden toe "DATEADD(year, iox.OPTION_FACTOR+1, do.ACTIVATION_DATE) > GETDATE()". Op die manier bekijk ik alleen de huidige hostings/fw dacht ik.
Tot ik gisterenavond de slaap niet kon vatten en er wat over lag na te denken... Als een klant een forwarding heeft besteld verleden week (activatiedatum=29 juli bijv.) en hij upgrade vandaag naar een hosting (activatiedatum=4 aug dus), dan gaat die forwarding nog steeds getoond worden.
Mijn vorige workaround met de DATEADD() was dus geen goede en ik zal terug naar mijn originele idee moeten : van elk domein moet ik de laatste bestelling hebben en daarvan moet ik dan de hosting/fw bekijken.

Een voorbeeld van mijn huidig statement ziet er als volgt uit :
(ik heb het wat ingekort om het leesbaar te houden)

code:

SELECT d.ID, do.ID OPTION_ID 
FROM INVOICES i 
INNER JOIN INVOICES_ORDERS_X iox ON i.ID = iox.INVOICE_ID 
INNER JOIN ORDERS o ON iox.ORDER_ID = o.ID 
INNER JOIN DOMAIN_OPTIONS do ON o.DOMAIN_OPTION_ID = do.ID 
INNER JOIN DOMAINS d ON o.DOMAIN_ID = d.ID 
WHERE i.BILLING_ID = 302 
AND (do.URLFW_ID IS NOT NULL OR do.MAILFW_ID IS NOT NULL) 
AND d.STATUS_ID = 3

Ik hoop dat mijn uitleg een beetje te begrijpen en volledig is. Het ziet er misschien wat chaos-achtig uit, maar als je de complexiteit bekijkt is dat ook wel nodig. Over de database structuur heb ik goed nagedacht en ik ben er vrij tot heel tevreden over. Ik kan er alles mee wat ik wil. Dit moet ook lukken, ben ik vrij zeker van, het lukt me alleen momenteel niet in 1 statement...

Alvast bedankt!

[Altaphista]

Is dit niet in een SP op te lossen? Lijkt mij wat makkelijker, het hoeft toch niet per se in 1 statement...

[codemann]

Hmm een stored procedure is inderdaad een mogelijkheid waaraan ik nog niet gedacht had, good idea!
Neen, het moet inderdaad niet in 1 statement, maar ik voelde dat het mogelijk was en wou het toch graag proberen

[Altaphista]

codemann schreef op 04 augustus 2004 @ 11:46:
Hmm een stored procedure is inderdaad een mogelijkheid waaraan ik nog niet gedacht had, good idea!
Neen, het moet inderdaad niet in 1 statement, maar ik voelde dat het mogelijk was en wou het toch graag proberen

idd heb je ook nog voordeel van cursors en kan je wat uitgebreidere checks en statements gebruiken

[Creepy]

En je kan niet de MAX(activatiedatum) per domain opvragen?

[codemann]

Creepy schreef op 04 augustus 2004 @ 11:49:
En je kan niet de MAX(activatiedatum) per domain opvragen?

Bedoel je opvragen en het later in ASP verder filteren? Ik zag geen manier om alleen de MAX(datum) te tonen per domein...

[codemann]

Ok ik ga mij nog eens aan de SP wagen, dat is alweer enkele jaartjes geleden. Mocht iemand me toch nog weten te zeggen hoe het zou moeten in 1 statement, heel graag! Een mens leert graag bij eh

[codemann]

Ik kan het niet loslaten, met SP is het ook maar een hoop gerommel en ik blijf met het gevoel zitten dat dit gemakkelijker kan.

Even een concreter voorbeeld :

code:

SELECT d.ID DOMAIN_ID, do.ID, do.ACTIVATION_DATE
FROM INVOICES i 
INNER JOIN INVOICES_ORDERS_X iox ON i.ID = iox.INVOICE_ID 
INNER JOIN ORDERS o ON iox.ORDER_ID = o.ID 
INNER JOIN DOMAIN_OPTIONS do ON o.DOMAIN_OPTION_ID = do.ID 
INNER JOIN DOMAINS d ON o.DOMAIN_ID = d.ID 
INNER JOIN DOMAIN_EXTENTIONS de ON d.EXT_ID = de.ID 
WHERE i.BILLING_ID = 302 
AND (do.URLFW_ID IS NOT NULL OR do.MAILFW_ID IS NOT NULL) 
AND d.STATUS_ID = 3 
ORDER BY d.ID

Dit geeft :

code:

DOMAIN_ID      OPTION_ID      ACTIVATION_DATE
84             84             2002-09-13 00:00:00.000
84             1176           2003-09-13 00:00:00.000
86             86             2002-07-02 00:00:00.000
252            252            2001-11-11 00:00:00.000
357            357            2002-02-24 00:00:00.000
357            357            2002-02-24 00:00:00.000
357            357            2002-02-24 00:00:00.000
385            385            2002-01-14 00:00:00.000
385            385            2002-01-14 00:00:00.000
385            385            2002-01-14 00:00:00.000
401            401            2002-12-17 00:00:00.000
401            401            2002-12-17 00:00:00.000
...

Zoals je ziet heeft domein 84 2 opties, ik zou alleen 1176 moeten zijn.
357 heeft 3x dezelfde optie, dit betekent dat er 3x verlengd is geweest, ik zou er ook maar 1 van moeten zien.

Dit moet volgens mij te doen zijn met een GROUP BY ofzo, maar met de beste wil van de wereld geraak ik er niet aan uit

[ Voor 18% gewijzigd door codemann op 04-08-2004 13:06 ]

[curry684]

Ik snap echt geen bal van je vraag, heb je geen entity relationship diagram oid erbij?

En als je iets aan een post hebt toe te voegen: gebruik aub de edit-knop. 3 posts op een rij lezen niet handig

[codemann]

curry684 schreef op 04 augustus 2004 @ 13:30:
Ik snap echt geen bal van je vraag, heb je geen entity relationship diagram oid erbij?

En als je iets aan een post hebt toe te voegen: gebruik aub de edit-knop. 3 posts op een rij lezen niet handig

Neen niet direct, sorry

Om het even kort te zeggen, als je de bovenstaande result set ziet moet dat wel te begrijpen zijn denk ik : ik wil de huidige status van alle domeinen van een gebruiker zien... een domein kan zoals je ziet meerdere option_ids hebben; concreet het kan eerst een forwarding geweest, later een hosting, enzovoort; als een domein dezelfde status meerdere malen aanhoudt dan wil dit eigenlijk gewoon zeggen dat het gewoon verlengd is...
Veel blabla, maar wat ik dus gewoon wil is per domein zoals je ze hierboven in de result set ziet maar 1 option_id, namelijk de laatste en dus de huidige status.

PS: mijn excuses voor de spam, kzal meer m'n edit knop gebruiken

[ Voor 5% gewijzigd door codemann op 04-08-2004 13:46 ]

[Creepy]

Dit moet volgens mij te doen zijn met een GROUP BY ofzo, maar met de beste wil van de wereld geraak ik er niet aan uit

code:

357            357            2002-02-24 00:00:00.000
357            357            2002-02-24 00:00:00.000
357            357            2002-02-24 00:00:00.000

Eeeeh.. dit is drie keer precies hetzelfde? Dit lijkt me foute invoer in je database? Want 3 keer dezelfde optie op hetzelfde domain op hetzelfde tijdstip activeren lijkt me niet te kunnen

[codemann]

Creepy schreef op 04 augustus 2004 @ 13:50:
Dit moet volgens mij te doen zijn met een GROUP BY ofzo, maar met de beste wil van de wereld geraak ik er niet aan uit

code:

357            357            2002-02-24 00:00:00.000
357            357            2002-02-24 00:00:00.000
357            357            2002-02-24 00:00:00.000

Eeeeh.. dit is drie keer precies hetzelfde? Dit lijkt me foute invoer in je database? Want 3 keer dezelfde optie op hetzelfde domain op hetzelfde tijdstip activeren lijkt me niet te kunnen :)[/quote]

Als ik meer velden zou tonen dan zou je zien dat in de tabel waar de bestelling aan de factuur wordt gekoppeld dat er daar nog een "option_factor" is... deze gebruik ik om als een hosting/forwarding blijft bestaan om hem dan niet de hele tijd te herhalen met gewoon een nieuw jaartal. Het eerste record dat je ziet zal dus waarschijnlijk factor 0 hebben, en de andere 2 zullen 1 en 2 hebben... de huidige hosting/fw loopt dus van 24-2-2004 tot 24-2-2005...
Met andere woorden, zoals ik net zei : in dit geval is de huidige hosting ook de option_id met de hoogste activation_date, ook al is deze 3x hetzelfde.

[pistole]

had je nog niet aan de combinatie van distinct en max gedacht (eventueel met een having erbij)?

[ Voor 22% gewijzigd door pistole op 04-08-2004 13:59 ]

[codemann]

pistole schreef op 04 augustus 2004 @ 13:58:
had je nog niet aan de combinatie van distinct en max gedacht (eventueel met een having erbij)?

Jawel, in die richting ben ik de hele tijd aan het denken en ik heb de MSSQL documentatie ook al zitten nalezen... Maar die dingen zijn niet m'n sterkste kant en ik krijg ze niet bij elkaar zoals het zou moeten. Maar je hebt gelijk, volgens mij moet het zeker in die richting gezocht worden...