Results 1 to 3 of 3

Thread: Filtering Dates

  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: Filtering Dates

    I have two tables the Table A shows transaction dates by week with a startdate and enddate (sometimes the period is less than a week i.e. mon-fri). Table B has a series of rates and effective dates. All the other fields match up fine, family = family, ratetype = ratetype, etc. What I need is a way to find out which record in Table B matches up to Table A based on the EffectiveDate.

    Table B example
    Family ratetype timetype effectivedate
    100 2 2 01/01/03
    100 2 1 01/01/03
    100 2 1 03/01/03
    100 2 2 03/01/03

    Table A example
    Payment Family StartDate EndDate RateType TimeType
    1 100 01/20/03 01/26/03 2 2
    2 100 01/27/03 02/02/03 2 2
    3 100 02/03/03 02/03/03 2 1
    4 100 02/04/03 02/04/03 2 1
    5 100 02/24/03 03/02/03 2 2
    6 100 03/17/03 03/23/03 2 2

    payments 1-4 should match up with effectivedate 01/01/03
    also payment 5 since the startdate is prior to effectivedate 03/01/03

    payment 6 goes with 03/01/03

    thanks for any help

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi there!

    I hope the following query will do it:

    select a.payment
    ,a.family
    ,a.timetype
    ,a.startdate
    ,a.enddate
    ,max(b.effectivedate)
    from dbo.table_a a
    inner join dbo.table_b b
    on a.family = b.family
    and a.ratetype = b.ratetype
    and a.timetype = b.timetype
    and a.startdate > b.effectivedate
    group by a.payment ,a.family ,a.timetype ,a.startdate ,a.enddate
    order by 1

    Otherwise, post a reply!

    Greetings,
    Carsten

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    CarstenK, thanks for the reply unfortunately that one won't work,,, mostly due to the fact I spent way to much time looking at this yesterday and forgot two key components.

    Table B has an additional field which goofs up simple filtering by effectivedate.
    Table B example
    Family, ratetype, timetype, effectivedate, fee
    100, 2, 2, 01/01/03, $10.00
    100, 2, 1, 01/01/03, $5.00
    100, 2, 1, 03/01/03, $4.00
    100, 2, 2, 03/01/03, $8.00

    Additionally I forgot that Table A can have values of 1, 2, or 3 in the RateType Field. For my calculation RateType 1 or 2 in Table A means grab the Fee specified by the Effective date. If RateType in Table A is 3 then I need to use the Effective Rate 2 from Table B and multiply by 5 (could be either TimeType 1 or 2). There is also a flag I have not included that means no fee applies to this payment.

    Table A example
    Payment, Family, StartDate, EndDate, RateType, TimeType
    1, 100, 01/20/03, 01/26/03, 2, 2
    2, 100, 01/27/03, 02/02/03, 2, 2
    3, 100, 02/03/03, 02/03/03, 2, 1
    4, 100, 02/04/03, 02/04/03, 2, 1
    5, 100, 02/24/03, 03/02/03, 2, 2
    6, 100, 03/17/03, 03/23/03, 2, 2


    Other than the effectivedate filtering I am using this case statement to compile the fee portion:

    Case Applyfee when 0 then 0 when 1 then
    (case a.ratetype when 3 then (b.fee *5)
    else b.fee end) end as Fees
    from a join b on
    a.timetype = b.timetype and
    a.family = b.family

    I just had to add ratetype1 to this mix late yesterday, hopefully I'll be able to drop it after talking with someone today and filter it out of table B, otherwise some way to link ratetype >=2 in table A = 2 in table B will have to be worked out

    Now I'm more confused if I have described this right but will post anyhow.

    Brent

Posting Permissions

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