Post reply

Help with Select Statement (involving dates)

  • January 20, 2005 at 8:41 am

    #61645

    Hi All. (SQL Server 2000)

    I have the following problem.

    I have two tables (parent tbl1Address) and (child –tbl1AddressPersonLink)

    tbl1Address

    intAddressID (key)

    strAddressLine1

     

    tbl1AddressPersonLink

    intAddressLinkID (key)

    intAddressID (FK)

    intPersonID,

    intFromMonth

    intFromYear

    intToMonth

    intToYear

    What I want to do is return the address for the passed person ID. A person can have more than 1 linked address hence the From month / year To month / year.

    The From month, year and To month, year may not be filled in in which case the address has no time limits.

    If From month and To month are filled in, but no years, the person is at that address within those months every year.

    If there is no From month, but there is a To month, the person is at that address up until the To month likewise with From and To year.

    What I need to do is compare the current date with these values and extract the correct address line 1 from tbl1Address. If more than 1 address is found I need to return an ‘Address not known’ as it could be any of them.

    If an address link record has no months/ or years specified it means that there is no date range (ie it is always relevant). If no match can be found on todays date but there is only 1 link record with no dates specified, I want to return that address.

    Phew…

    Any suggestions for this.

    Any help much appreciated.

    Thanks.

  • January 21, 2005 at 3:25 am

    #538466

    See if this works

    DECLARE @yy int, @mm int, @yymm int, @rowct int, @intAddressID int

    SET @yy = year(GETDATE())

    SET @mm = month(GETDATE())

    SET @yymm = (@yy * 100) +  @mm

    SET NOCOUNT ON

    SELECT @intAddressID = intAddressID

    FROM tbl1AddressPersonLink

    WHERE intPersonID = @intPersonID

    AND @yymm >= (ISNULL(intFromYear,@yy) * 100) + ISNULL(intFromMonth,@mm)

    AND @yymm <= (ISNULL(intToYear,@yy) * 100) + ISNULL(intToMonth,@yy)

    SET @rowct = @@ROWCOUNT

    SET NOCOUNT OFF

    IF @rowct = 1

    SELECT strAddressLine1

    FROM tbl1Address

    WHERE intAddressID = @intAddressID

    ELSE

    SELECT 'Address not known'

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 21, 2005 at 3:38 am

    #538468

    That's devastatingly more compact than what I was about to post One little thing, I think it makes sense for the main SELECT to have a DISTINCT - the problem states that finding more than one address is an error, but presumably overlapping Link records that specify the same address are allowed...

     

  • January 21, 2005 at 3:47 am

    #538470

    Thanks guys.

    I will give it a go.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply