I have a Table with SalesPerson and Sales for different dates. I have another table where I have SalesPerson and multiple date ranges where different Adjustment factor needs to be applied.
I can do this with a case statement when there is one or no row for the person in the adjustment factor table. But I am not sure how to loop through different date ranges for same salesperson
x
select a.date, a.salesperson, a.sales, b.adjustmentfactor,
case when a.date between b.startdate and b.enddate then b.adjustmentfactor
else 1 end realadjfactor,
a.sales * case when a.date between b.startdate and b.enddate then
b.adjustmentfactor else 1 end realsales
from sales a left join adjfactor b on a.salesperson = b.salesperson
Advertisement
Answer
Do you just want a join
?
select s.date, s.salesperson, s.sales,
coalesce(af.realadjfactor, 1) * s.sales as adjustedsales,
coalesce(af.realadjfactor, 1) as adjfactor
from sales s left join
adjfactor af
on s.salesperson = af.salesperson and
s.date between af.startdate and af.enddate;