Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Posts
    20

    Unanswered: Recursive SQL Query

    Hello,

    I have an issue with a query I need to create. The following in my Basic table with the name ClientAddress:


    Clientnr----------AddressLine---------Country---------Stopdate
    47-----------------Mainstrett3---------GERMANY---------30.Nov.2010
    47-----------------Obamastreet3--------USA-------------01.Feb.2011
    47-----------------Boulevard3----------USA-------------01.Oct.2011
    47-----------------Rue7----------------FRANCE----------31.Dec.9999
    588----------------Neustrasse3---------GERMANY---------31.Dec.9999
    899----------------Uferstrasse4--------GERMANY---------25.Mar.2012
    899----------------Boulevard78---------UK--------------31.Dec.9999
    .....

    So it stores historical addresses (the ones that have a Stopdate<>31.Dec.9999) and current valid addresses (Stopdate=31.Dec.9999) for quite a number of clients. In total there are around 20,000 lines in the table.


    I need to write now an SQL statement (No stored procedure etc as my user does not have the rights for this) that reports me any country change in the following way (Based on the above example) (in case there is a new address record, but within the same country, this can be ignored):

    Clientnr---------------Old_Country-------------NEW_Country---------Changedate
    47---------------------GERMANY-----------------USA-----------------30.Nov.2010
    47---------------------USA---------------------FRANCE--------------01.Oct.2011
    899--------------------GERMANY-----------------UK------------------25.Mar.2012
    ...

    Does anyone know who to do this? I tried to solve it via recursive SQL(seems for me to be the only way), read now 2 hours about it, but to be honest I do not know how to use this powerful functionality as I never worked with it till now.


    Can anyone help me with this issue here, please? Thanks in advance

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    You can try this (not test):
    Code:
    select * from (
       select Clientnr, AddressLine 
              ,country as old_country
              ,lead( country ,1) over ( partition by clientnr order by stopdate ) as new_country
              ,stopdate ) T
       where old_country <> new_country
    if you can not use lead function ,you can use window function instead of it
    Code:
    select * from (
       select Clientnr, AddressLine 
              ,country as old_country
              ,max( country ) 
               over ( partition by clientnr order by stopdate 
                      range between 1 following and 1 following ) as new_country
              ,stopdate ) T
       where old_country <> new_country

  3. #3
    Join Date
    Oct 2012
    Posts
    11
    Try the query below. I haven't tested it and I'm not sure of the syntax for getting the top 1 record.

    select * from clientaddress c1
    where c1.stopdate = '9999-12-31'
    and c1.country <> (
    select c2.country from clientaddress c2
    where c2.stopdate < '9999-12-31'
    and c2.clientnbr = c1.clientnbr
    order by c2.stopdate desc
    fetch first 1 row only)

  4. #4
    Join Date
    Sep 2012
    Posts
    20
    Thanks very much!!!! Already the first query solved my Issue!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •