[SQL] Tabellen joinen met elk hun from_dates

[Verwijderd]

Beste,
Ik heb een probleem en ik slaag er maar niet in om dit op te lossen...
Ik ben er zeker van dat dit voor sommigen onder jullie een fluitje van een cent zal zijn.
Ik maak gebruik van SQL server 2005 (Dus aanleiding naar een eventuele oplossing mag ook in de vorm van SSIS zijn)


Beschouwen we 2 tabellen:
- DimBetaler_adres
Met respectievelijk | unieknummer | naam | straat | bus | from_date | als kolommen

- DimBetaler_group
Met | unieknummer | group_id | from_date als kolommen

Nu wil ik van deze 2 tabellen 1 tabel maken; zijnde DimBetaler:
Met | unieknummer | naam | straat | bus | group_id | als kolommen
De koppeling tussen de 2 voorgaande tabellen moet gebeuren via het unieknummer ( en de from_date; en het is daar waar het probleem zich stelt; from_date is datetime en unieknummer is int)


Laat mij deze nu vullen met data om mijn probleem te illustreren:
- DimBetaler_adres

| unieknummer | naam | straat | bus | from_date |
1500 Deterfim vredestraat 1 1992-01-01
1500 Deterfim vrijheidsstraat 12 1995-01-01
1500 AquaJet vrijheidsstraat 25 2000-01-01


- DimBetaler_group
| unieknummer | group_id | from_date
1500 5 1992-01-01
1500 6 1994-01-01
1500 7 1995-01-01
1500 15 1998-01-01
1500 23 2005-01-01

Nu, dit is de tabel die ik moet bekomen:

DimBetaler_adres
| unieknummer | naam | straat | bus | group_id | from_date |
1500 Deterfim vredestraat 1 5 1992-01-01
1500 Deterfim vrijheidstraat 12 6 1994-01-01
1500 Deterfim vrijheidstraat 12 7 1995-01-01
1500 Deterfim vrijheidstraat 12 15 1998-01-01
1500 Deterfim vredestraat 25 15 2000-01-01
1500 Deterfim vredestraat 25 23 2000-01-01

Dus zoals jullie zien is de koppeling gebeurd op unieknummer...
Echter moet ik ook rekening houden met de from_date's dus als de ene waarde veranderd (Bv. straat) moet rekening gehouden worden met wat de andere waarde was op het tijdstip van verandering. (Dus bv. group_id) en omgekeerd.

Wat ik al allemaal geprobeerd heb:
- Via SSIS een date loop gemaakt welke mij de datums afloopt en deze vult.
- En nog zoveel andere methoden via SSIS...
- eerst left join op eerste tabel; dan samenvoegen met left join op 2de... Blok...
- ...

Op het net vind ik hier niets van terug... daar het nogal vrij specifiek is...
Zouden jullie mij willen helpen?
Alvast bedankt!
Vriendelijke groeten,
J.

[P.O. Box]

alvast een eerste reactie...
- je uniekenummer kan ik nou niet direct "uniek" noemen...

je laatste tabel moet neem ik aan zijn:
| unieknummer | naam | straat | bus | group_id | from_date |
1500 Deterfim vredestraat 1 5 1992-01-01
1500 Deterfim vrijheidstraat 12 6 1994-01-01
1500 Deterfim vrijheidstraat 12 7 1995-01-01
1500 Deterfim vrijheidstraat 12 15 1998-01-01
1500 AquaJet vredestraat 25 15 2000-01-01
1500 AquaJet vredestraat 25 23 2005-01-01

en klopt het dat er in tabel DimBetaler_group nog een entry mist voor Aquajet vanaf 2001-01-01? of is dat bewust zo? (dat laatste maakt het lastiger)

[ Voor 13% gewijzigd door P.O. Box op 08-03-2007 12:56 ]

[P.O. Box]

- DimBetaler_adres
| unieknummer | naam | straat | bus | from_date |
1500 Deterfim vredestraat 1 1992-01-01
1600 Deterfim vrijheidsstraat 12 1995-01-01
1700 AquaJet vrijheidsstraat 25 2000-01-01


- DimBetaler_group
| unieknummer | group_id | from_date
1500 5 1992-01-01
1500 6 1994-01-01
1600 7 1995-01-01
1600 15 1998-01-01
1700 19 2000-01-01
1700 23 2005-01-01

DimBetaler_combi
| unieknummer | naam | straat | bus | group_id | from_date |
1500 Deterfim vredestraat 1 5 1992-01-01
1600 Deterfim vrijheidstraat 12 6 1994-01-01
1600 Deterfim vrijheidstraat 12 7 1995-01-01
1600 Deterfim vrijheidstraat 12 15 1998-01-01
1700 AquaJet vredestraat 25 15 2000-01-01
1700 AquaJet vredestraat 25 23 2005-01-01


Van bovenstaande uitgaande, zou je het zo kunnen doen, maar weet niet of dat ook je bedoeling is en of je de tabellen wel zoals hierboven bedoelt?

SQL:

SELECT
    adres.unieknummer, adres.naam, adres.straat, adres.bus, group.groepid, GREATEST(adres.fromdate, group.fromdate)
FROM
    adres
LEFT JOIN
    group
ON
    adres.unieknummer = group.unieknummer

ik weet niet of de GREATEST functie werkt...anders kun je dat vervangen door: "IF(adres.fromdate > group.fromdate, adres.fromdate, group.fromdate)

[Robtimus]

Je kan joinen op meerdere velden, dus je krijgt dan dit:

SQL:

SELECT betaler.unieknummer, adres.naam, adres.straat, adres.bus, betaler.group_id, betaler.from_date
FROM DimBetaler_group betaler INNER JOIN DimBetaler_adres adres ON betaler.unieknummer = adres.unieknummer AND betaler.from_date = adres.from_date

[P.O. Box]

nog even nalezend is dit volgens mij niet wat je zoekt... maar ik begrijp ook niet helemaal wat je nu wel zoekt dan....

ik denk nog even...

[Verwijderd]

Beste,
alvast bedankt voor jullie reacties!
Hier mijn antwoorden op enkele vragen:

Edwardvb schreef:

en klopt het dat er in tabel DimBetaler_group nog een entry mist voor Aquajet vanaf 2001-01-01? of is dat bewust zo? (dat laatste maakt het lastiger)

Antwoord:
Neen, de DimBetaler_group mist geen entry voor Aquajet vanaf 2001-1-01. Waarom vraag je dit juist?

In mijn geval is dus de firma met unieknummer 1500 van naam veranderd. Maar gaat het nog over diezelfde firma. De uniekenummers zijn dus correct.

Nu, waar ik dus problemen mee heb is het volgende:
Ik wil een combinatie maken van de 2 tabellen... (dus de 2 naar één tabel migreren).
Als je de combi tabel bekijkt zul je zien dat de moeilijkheid hem in de from_dates ligt.


IceManX: Inderdaad, ik kan joinen op verschillende velden, maar de from_dates moeten niet noodzakelijk gelijk zijn; ze moeten gewoon binnen dezelfde tijdsframe vallen. (of hoe zeg je dit?)
Dus indien een bepaalde waarde geldig is (Bv. adres); en die andere waarde veranderd (Bv. group),
dan moet een rij weggeschreven worden met wat het adres zou zijn tijdens die group verandering.
en omgekeerd. (met als nieuwe from_date, de from_date van de gewijzigde entiteit)
Dus Left join is volgens mij niet goed; daar ik dan enkel de adres tabel als referentie gebruik; en ik beide tabellen als referentie wil.

[P.O. Box]

deze twee tabellen union-en (in 2000 versie lukt dat niet, misschien dat dat in 2005 wel lukt... anders een tijdelijke tabel vullen).... Daarna een distinct select op de tabel...

SQL:

/* first select addresses with group they belong to when adress changes */
select
    adres.unieknummer,
    adres.naam,
    adres.straat,
    adres.bus,
    adres.from_date,
    (
        select top 1
            groep.groepid
        from
            groep
        where
            groep.unieknummer = adres.unieknummer
            and
            groep.from_date <= adres.from_date
        order by
            groep.from_date desc
    ) as groepid
from
    adres

SQL:

/* then join with groups with address they belong to */
select 
    adres.unieknummer,
    adres.naam, 
        adres.straat,
        adres.bus,
    groep.from_date, 
    groep.groepid 
from 
    groep
left join
    adres
on
    adres.unieknummer = groep.unieknummer
    and
    adres.from_date = 
    ( select
        max(adressub.from_date)
      from
        adres adressub
      where
        adressub.from_date<= groep.from_date
    )

Dit geeft bij mij dit resultaat:

code:

1500      Deterfim      Vredestraat                 1           1992-01-01 00:00:00.000 5
1500      Deterfim      Vrijheidsstraat             12          1995-01-01 00:00:00.000 7
1500      AquaJet       Vrijheidsstraat             25          2000-01-01 00:00:00.000 15  

en


1500      Deterfim      Vredestraat               1             1992-01-01 00:00:00.000 5
1500      Deterfim      Vredestraat               1             1994-01-01 00:00:00.000 6
1500      Deterfim      Vrijheidsstraat           12            1995-01-01 00:00:00.000 7
1500      Deterfim      Vrijheidsstraat           12            1998-01-01 00:00:00.000 15
1500      AquaJet       Vrijheidsstraat           25            2005-01-01 00:00:00.000 23

dubbele eruit en je hebt wat je wilt hebben volgens mij....

Zoiets?

[ Voor 43% gewijzigd door P.O. Box op 08-03-2007 15:15 ]

[P.O. Box]

ik ben lekker bezig

zo kan het ook in 1 x:

SQL:

/* first select addresses with group they belong to when adress changes */ 
select 
    adres.unieknummer, 
    adres.naam, 
    adres.straat, 
    adres.bus, 
    adres.from_date, 
    ( 
        select
            groep.groepid 
        from 
            groep 
        where 
            groep.unieknummer = adres.unieknummer 
            and 
            groep.from_date = (
        select max(subgroep.from_date)
        from
            groep subgroep
        where
            subgroep.unieknummer = adres.unieknummer
            and
            subgroep.from_date <= adres.from_date 
        )
    ) as groepid 
from 
    adres
union
/* then join with groups with address they belong to */
select 
    adres.unieknummer,
    adres.naam, 
        adres.straat,
        adres.bus,
    groep.from_date, 
    groep.groepid 
from 
    groep
left join
    adres
on
    adres.unieknummer = groep.unieknummer
    and
    adres.from_date = 
    ( select
        max(adressub.from_date)
      from
        adres adressub
      where
        adressub.unieknummer= adres.unieknummer
        and
        adressub.from_date<= groep.from_date
    )
    
order by
    from_date

[Verwijderd]

Hey,
Bedankt voor je reactie!
Ik ga dit direct testen. Zal je vertellen hoe het afgelopen is ;-)
Nogmaals dank!
Groeten,
J.

[P.O. Box]

oei.... 15:19 om 15:20... ik hoop dat je mijn "in 1 x"-versie hebt meegekregen

[Redshark]

Zou het trouwens niet handig zijn om naast een from_date ook een to_date mee te nemen als je toch bezig bent?

[Verwijderd]

Hey, zie het nu pas.
Ik ga dit morgen uittesten !
Alvast bedankt voor jullie hulp !
Vriendelijke groeten,
J.

@Redshark, ik heb hier reeds een scriptje voor... ik doe dit achteraf na de implementatie in het Data warehouse.

[P.O. Box]

en? lukt het?

[Verwijderd]

Hey,
alvast bedankt !
Het lukt, maar niet voor alle toepassingen...

Ik krijg soms de foutmelding dat de subquery meer dan één resultaat weergeeft...
(Is dit niet het geval indien er meerdere group_id's zijn binnen éénzelfde adres periode?
...
Ga nog even verder zoeken !
Alvast bedankt !

[P.O. Box]

hmm... dat is inderdaad zo... maar dat betekent dat er meerdere entries zijn in de groeptabel op dezelfde datum voor een adres... ik weet niet wat zo'n groep betekent, maar geeft "select top 1" in de subquery niet het gewenste resultaat? (ik weet niet wat meerdere entries met dezelfde datum, hetzelfde unieknummer, maar meerdere (evt. dezelfde?) groepnummers betekenen?)

[Verwijderd]

hey,
Met Select Top 1 heb ik deze foutmelding niet meer !!!
maw ik kan de tabel nu vullen !
Nu kijken of er geen waarden ontbreken...
Nogmaals hartelijk dank voor je hulp !

[Verwijderd]

Hmm,
ben hier terug...
Het is zo dat er tussen de adressen de groepen een één op veel relatie bestaat.
Dus meerdere verschillende groepen mogen geldig zijn gedurende één adres periode...

[P.O. Box]

Verwijderd schreef op dinsdag 13 maart 2007 @ 14:52:
Hmm,
ben hier terug...
Het is zo dat er tussen de adressen de groepen een één op veel relatie bestaat.
Dus meerdere verschillende groepen mogen geldig zijn gedurende één adres periode...

daar was ik al bang voor....

ik weet niet of dit werkt hoor, maar probeer eens:

SQL:

select  
    adres.unieknummer,  
    adres.naam,  
    adres.straat,  
    adres.bus,  
    adres.from_date, 
    groep.id
from
    adres
left join
    group
on
    (
            groep.unieknummer = adres.unieknummer  
            and  
            groep.from_date = ( 
        select max(subgroep.from_date) 
        from 
            groep subgroep 
        where 
            subgroep.unieknummer = adres.unieknummer 
            and 
            subgroep.from_date <= adres.from_date  
        ) 
    )

[Verwijderd]

Ik ga direct enkel Printscreens posten... dan weet je de exacte situatie...
Als je tenminste nog wil helpen ;-)
Grtz.
J.

[Verwijderd]

Hey,
daarnet je post gelezen...
Ik moet zeggen, nu ziet het er voortreffelijk uit !!!
ik ga de data nog verder controleren !
Bij deze,
Nogmaals... bedankt!
(Van waar ben jij ergens; als ik mag vragen)

[P.O. Box]

Verwijderd schreef op dinsdag 13 maart 2007 @ 15:12:
Nogmaals... bedankt!
(Van waar ben jij ergens; als ik mag vragen)

Geen dank...

ennuh, wat bedoel je met je vraag daarna?
(het enige antwoord dat ik nu kan bedenken is namelijk: Arnhem)

[Verwijderd]

Welja,
kwestie dat ik weet van waar je ergens bent ;-)
Zomaar hoor...

Nu... laatste vraagje, ik ben je onderweg verloren... (maw, ik heb het lastig om deze query nog te begrijpen...) heb ik nu nog de union statement nodig?

[P.O. Box]

ja die heb je nog nodig...
wat je doet is:

je selecteert alle adreswijzigingen en de daarbij behorende groepen... en je selecteert alle groepswijzigignen en de daarbij behorende adressen... als je deze twee combineert, dan heb je alle wijzigingen bij een unieknummer....