[MSSQL] Selectie beperken

[Verwijderd]

Hoi!

Ik heb een probleem waar ik al een hele tijd mee bezig ben, maar ik kom er niet uit. Ik heb een tabel met een naam en een begin- en einddatum. De begin- en einddatum geven een geldigheid aan en deze overlappen niet per naam.

Wat ik vervolgens wil is een select statement maken die voor een bepaalde periode, gedefinieerd door een begindatum en een eind datum, selecteert welke records op in die periode geldig zijn. Maar als er meerdere geldig zijn, dan wil ik alleen de eerste hebben. En daar kom ik niet uit.

Ik heb de tabel vereenvoudigd naar dit:

SQL:

CREATE TABLE AA_TEST (
    NAAM VARCHAR(10),
    BEGINDATUM DATETIME,
    EINDDATUM DATETIME
)

INSERT INTO AA_TEST VALUES ('MARK', '2009-01-01', '2009-02-28')  --record 1
INSERT INTO AA_TEST VALUES ('MARK', '2009-03-01', '2009-04-30')  --record 2
INSERT INTO AA_TEST VALUES ('MARK', '2009-05-01', '2009-06-30')  --record 3
INSERT INTO AA_TEST VALUES ('MARK', '2009-07-01', '2009-08-31')  --record 4
INSERT INTO AA_TEST VALUES ('MARK', '2009-09-01', '2009-12-31')  --record 5
INSERT INTO AA_TEST VALUES ('HENK', '2007-01-01', NULL)  --record 6
INSERT INTO AA_TEST VALUES ('KEES', '2008-01-01', '2015-12-31')  --record 7

Als ik nu als periode '2009-01-15' tot '2009-08-28' aangeef, dan wil ik dat record 2, 6 en 7 zichtbaar worden. Een NULL betekent dat er geen einde is van de geldigheid.

De query die ik momenteel heb is zo:

SQL:

select naam, begindatum, einddatum from aa_test
where DATEINPERPERIOD('2009-01-15', '2009-08-28', begindatum, einddatum) = 1
order by begindatum

De function DATEINPERPERIOD ziet er zo uit:

SQL:

CREATE FUNCTION [DATEINPERPERIOD] (
    @BEGINDATUM_PERIODE                DATETIME,
    @EINDDATUM_PERIODE                  DATETIME,
    @BEGINDATUM_DIENSTREGELING  DATETIME,
    @EINDDATUM_DIENSTREGELING    DATETIME
)
RETURNS BIT
AS
BEGIN
    DECLARE @RESULT BIT
    IF
      (((@BEGINDATUM_DIENSTREGELING >= @BEGINDATUM_PERIODE)
        AND 
        (@BEGINDATUM_DIENSTREGELING <  @EINDDATUM_PERIODE))
        
               OR
        ((@BEGINDATUM_DIENSTREGELING < @BEGINDATUM_PERIODE)
        AND
       ((@EINDDATUM_DIENSTREGELING >= @EINDDATUM_PERIODE) 
                   OR (@EINDDATUM_DIENSTREGELING IS NULL ))))
    BEGIN
        SET @RESULT = 1
    END
    ELSE 
        SET @RESULT = 0

    RETURN @RESULT
END

De functie geeft dus een true of false terug als het record in de periode valt.

Het resultaat:

SQL:

naam    begindatum                 einddatum
HENK    2007-01-01 00:00:00.000 NULL
KEES    2008-01-01 00:00:00.000 2015-12-31 00:00:00.000
MARK    2009-03-01 00:00:00.000 2009-04-30 00:00:00.000
MARK    2009-05-01 00:00:00.000 2009-06-30 00:00:00.000
MARK    2009-07-01 00:00:00.000 2009-08-31 00:00:00.000

Hierbij wil ik dus ALLEEN het eerste "MARK" record zien, omdat dit het eerste geldige record is van die naam.

Dit is dus het resultaat dat ik zou willen hebben:

SQL:

naam    begindatum                 einddatum
HENK    2007-01-01 00:00:00.000 NULL
KEES    2008-01-01 00:00:00.000 2015-12-31 00:00:00.000
MARK    2009-03-01 00:00:00.000 2009-04-30 00:00:00.000

HOE doe ik dit? Ik heb van alles geprobeerd met MIN(begindatum) en GROUP BY's, maar niets dat enig resultaat oplevert.

Het liefst zou ik het willen oplossen zonder de originele query te herhalen. Ik moet dit namelijk in 20 vrij lange queries herhalen en deze zou ik liever niet allemaal verdubbelen door een subselect te doen.

Alvast hartelijk bedankt!

[Verwijderd]

lijkt toch gewoon ok met min en groupby:

SQL:

select naam, min(begindatum), min(einddatum) from aa_test 
where dbo.DATEINPERPERIOD('2009-01-15', '2009-08-28', begindatum, einddatum) = 1 
group by naam

[edit] niet helemaal, datums van "Mark" kunnen uit 2 verschillende records komen

[ Voor 16% gewijzigd door Verwijderd op 29-10-2009 16:34 ]

[pedorus]

Een oplossing met MIN gaat mis bij dingen als NULL's en overlappende periodes, ik weet niet of dat kan voorkomen.
Je hebt hier een soort groupwise maximum probleem. Kijk eens naar deze oplossing als je SQL Server 2005 of later hebt.

[NMe]

Je functie houdt trouwens geen rekening met records die beginnen vóór de periode waar je op zoekt en eindigen ín de periode waar je op zoekt. Is dat wel wat je wil?

[.oisyn]

Verwijderd schreef op donderdag 29 oktober 2009 @ 16:05:
Als ik nu als periode '2009-01-15' tot '2009-08-28' aangeef, dan wil ik dat record 2, 6 en 7 zichtbaar worden. Een NULL betekent dat er geen einde is van de geldigheid.

Waarom record 3 niet dan?

Het resultaat:

SQL:

naam    begindatum                 einddatum
HENK    2007-01-01 00:00:00.000 NULL
KEES    2008-01-01 00:00:00.000 2015-12-31 00:00:00.000
MARK    2009-03-01 00:00:00.000 2009-04-30 00:00:00.000
MARK    2009-05-01 00:00:00.000 2009-06-30 00:00:00.000
MARK    2009-07-01 00:00:00.000 2009-08-31 00:00:00.000

Hierbij wil ik dus ALLEEN het eerste "MARK" record zien, omdat dit het eerste geldige record is van die naam.

Nogmaals, 2009-05-01 tot 2009-06-30 ligt toch ook geheel tussen 2009-01-15 en 2009-08-28?

Als bovenstaande idd een vergissing is en die records er dus idd ook tussen moeten, dan is de logica die je moet doen sowieso: BEGINDATUM >= start AND COALESCE(EINDDATUM <= end, TRUE). Jij doet wat meer tests, maar feitelijk zijn die onzinnig, aangezien het vast staat dat BEGINDATUM <= EINDDATUM. Tenminste, dat mag ik hopen

HOE doe ik dit? Ik heb van alles geprobeerd met MIN(begindatum) en GROUP BY's, maar niets dat enig resultaat oplevert.

Ik zie eerlijk gezegd niet in waarom het niet zou moeten werken met een GROUP BY op naam en een MIN op BEGINDATUM? Het nadeel is dan idd alleen dat je de einddatum niet weet, maar dat is met een fatsoenlijke index en een join niet zo duur.

[.oisyn]

NMe schreef op donderdag 29 oktober 2009 @ 16:56:
Je functie houdt trouwens geen rekening met records die beginnen vóór de periode waar je op zoekt en eindigen ín de periode waar je op zoekt. Is dat wel wat je wil?

Lijkt me wel, anders had ie wel gezegd dat record 1 ook bij z'n resultaten had moeten zitten . Maar then again, over record 3 is ie ook wazig

[pedorus]

.oisyn schreef op donderdag 29 oktober 2009 @ 16:59:
[...]

Waarom record 3 niet dan?

Om dezelfde reden dat record 4 in het tussenresultaat valt, maar niet in het eindresultaat. Overigens denk ik ook dat de logica makkelijker kan.

[.oisyn]

Oh ja duh . Record 4 valt er trouwens niet in z'n geheel tussen. 6 en 7 trouwens ook niet. Oh maar wacht, een record met NULL natuurlijk ook weer niet, dus wellicht wil ie dat weer wel. En dan geldt wat NMe zegt ook, want 6 en 7 horen er immers ook bij.

Misschien moet ie eerst maar eens aan z'n probleemomschrijving gaan werken . Als ie alle periodes wilt hebben waarvan een gedeelte binnen de opgevraagde periode ligt dan kan dat zo:

SQL:

begindatum <= eindperiode AND COALESCE(einddatum >= beginperiode, TRUE)

[ Voor 89% gewijzigd door .oisyn op 29-10-2009 17:16 ]

[Verwijderd]

@ Oisin,

"Waarom record 3 niet dan?"

Hij wil een soort distinct op "naam".
<edit> spuit 11

[ Voor 8% gewijzigd door Verwijderd op 29-10-2009 17:14 ]

[.oisyn]

Ik kom hierop

SQL:

SELECT a.naam, min(a.begindatum), b.einddatum
FROM aa_test a
INNER JOIN aa_test b USING (naam, begindatum)
WHERE a.begindatum <= '2009-08-28' and COALESCE(a.einddatum >= '2009-01-15', true)
GROUP BY naam

En natuurlijk een primary key (of iig een index) op (naam, begindatum)
(of is USING geen ANSI SQL?)

Resultaat:

HENK	2007-01-01 00:00:00	NULL
KEES	2008-01-01 00:00:00	2015-12-31 00:00:00
MARK	2009-01-01 00:00:00	2009-02-28 00:00:00

[ Voor 35% gewijzigd door .oisyn op 29-10-2009 17:24 ]

[pedorus]

Mm, MySQL for the win. USING is MySQL en je hebt hier de 'ongedefinieerde data'-functie gebruikt op b.einddatum, die ongekoppeld is aan min(a.begindatum). Op die manier kun je net zo goed a.einddatum gebruiken. Ik neem aan dat MSSQL geen tikfout was, en daar heb je geen USING (maar wel het koele "Rank() over (Partition ..." wat ik al aanhaalde). Dus dan krijg je iets als:

SQL:

select naam, begindatum, einddatum from
    (SELECT *, RANK() over (partition by naam order by begindatum) as r
         FROM aa_test a
         WHERE a.begindatum <= '2009-08-28' and 
             (a.einddatum is null or a.einddatum >= '2009-01-15')
     ) as tmp where r=1;

Maar dit is misschien een ander selectiecriterium dan dat TS wil.

[.oisyn]

pedorus schreef op donderdag 29 oktober 2009 @ 20:15:
Mm, MySQL for the win. USING is MySQL en je hebt hier de 'ongedefinieerde data'-functie gebruikt op b.einddatum, die ongekoppeld is aan min(a.begindatum).

Hmm crap, dat probeerde ik juist te voorkomen, maar dat GROUP BY moet dan natuurlijk wel voor de JOIN komen, stom

[cariolive23]

pedorus schreef op donderdag 29 oktober 2009 @ 20:15:
USING is MySQL

offtopic:
USING is onderdeel van de SQL standaard en werkt in diverse databases, o.a. MySQL en PostgreSQL. Daarnaast is er ook nog de NATURAL JOIN, scheelt je nog meer tikwerk bij het schrijven van de queries. Moet je wel het datamodel goed kennen en nooit meer veranderen, de relaties worden niet keihard in de query zelf uitgeschreven. En dat kan voor vage problemen zorgen.

http://en.wikipedia.org/wiki/Join_(SQL)

[Verwijderd]

Allereerst allen heel hartelijk bedankt voor het meedenken!

pedorus schreef op donderdag 29 oktober 2009 @ 16:34:
...als je SQL Server 2005 of later hebt.

Ja, ik gebruik SQL Server 2005.

NMe schreef op donderdag 29 oktober 2009 @ 16:56:
Je functie houdt trouwens geen rekening met records die beginnen vóór de periode waar je op zoekt en eindigen ín de periode waar je op zoekt. Is dat wel wat je wil?

Ja. Als er meerdere geldige records zijn, wil ik alleen degene die het eerste begint. En een geldigheid mag ook niet beginnen voor de aangegeven datum. Het kan best zijn dat de function wat over the top is, ik heb 'm zelf niet gebruikt en ga 'm alleen veranderen als dat echt nodig is.

pedorus schreef op donderdag 29 oktober 2009 @ 20:15:
Mm, MySQL for the win. USING is MySQL en je hebt hier de 'ongedefinieerde data'-functie gebruikt op b.einddatum, die ongekoppeld is aan min(a.begindatum). Op die manier kun je net zo goed a.einddatum gebruiken. Ik neem aan dat MSSQL geen tikfout was, en daar heb je geen USING (maar wel het koele "Rank() over (Partition ..." wat ik al aanhaalde). Dus dan krijg je iets als:

SQL:

select naam, begindatum, einddatum from
    (SELECT *, RANK() over (partition by naam order by begindatum) as r
         FROM aa_test a
         WHERE a.begindatum <= '2009-08-28' and 
             (a.einddatum is null or a.einddatum >= '2009-01-15')
     ) as tmp where r=1;

Maar dit is misschien een ander selectiecriterium dan dat TS wil.

Ja, dat is wat ik wil hebben! Ik kende het "Rank() over (partion..." helemaal niet! Hier ga ik mee aan de slag, super!

En als iemand nog een goede site weet waar dat Rank() over... uitgelegd wordt (ik kan ook Googlen, maar misschien weet iemand een hele goede site?), dan hoor ik dat graag.

Nogmaals allen, bedankt!

[ Voor 4% gewijzigd door Verwijderd op 30-10-2009 09:31 ]

[cariolive23]

Verwijderd schreef op vrijdag 30 oktober 2009 @ 09:30:
En als iemand nog een goede site weet waar dat Rank() over... uitgelegd wordt (ik kan ook Googlen, maar misschien weet iemand een hele goede site?), dan hoor ik dat graag.

Het is weliswaar de handleiding van PostgreSQL, maar het is een vrij duidelijk verhaal en ook (voor een groot deel) bruikbaar voor SQL Server:
http://www.postgresql.org...tive/tutorial-window.html

[.oisyn]

Verwijderd schreef op vrijdag 30 oktober 2009 @ 09:30:
En als iemand nog een goede site weet waar dat Rank() over... uitgelegd wordt (ik kan ook Googlen, maar misschien weet iemand een hele goede site?), dan hoor ik dat graag.

pedorus in "[MSSQL] Selectie beperken"

[Verwijderd]

Thanks! Ik had het linkje in eerste instantie over het hoofd gezien Alles werkt helemaal naar behoren en ik ben weer een stukje wijzer

Allen superbedankt!

[Verwijderd]

RANK() en USING zijn allebei hartstikke handig, maar dit probleem is volgens mij ook vrij simpel in standaard SQL op te lossen:

SQL:

select A.NAAM, A.BEGINDATUM, A.EINDDATUM
from AA_TEST A
inner join (select NAAM, min(BEGINDATUM) EERSTEDATUM
            from AA_TEST 
            where BEGINDATUM <= '2009-08-28' 
            and (EINDDATUM is NULL or EINDDATUM >= '2009-01-15')
            group by NAAM) B
on B.NAAM = A.NAAM and A.BEGINDATUM = B.EERSTEDATUM
order by A.BEGINDATUM

[ Voor 119% gewijzigd door Verwijderd op 05-11-2009 22:22 ]