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
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
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.
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.