Hi can anyone help me with the T-SQL query required to produce a rate per day based on two tables as below:
I have one table containing ranges:
FromDate | ToDate
2017-06-20 2017-07-01
and one table containing rates (This table is not limited to 2 records):
FromDate | Rate
2017-06-11 400
2017-06-24 1200
Based on the range from the first table I need to produce a result set as follows which returns the rate for each date in the input range:
Date | Rate
2017-06-20 400
2017-06-21 400
2017-06-22 400
2017-06-23 400
2017-06-24 1200
2017-06-25 1200
2017-06-26 1200
2017-06-27 1200
2017-06-28 1200
2017-06-29 1200
2017-06-30 1200
2017-07-01 1200
I will then need to sum the rate in a later step to produce a total.
I would really appreciate any help you can provide.
A pretty simple method is a recursive CTE and apply:
with cte as (
select fromdate as dte, todate
from table1
union all
select dateadd(day, 1, fromdate), todate
from cte
)
select dte, t2.rate
from cte cross apply
(select top 1 t2.rate
from table2 t2
where t2.fromdate <= cte.dte
order by t2.dte desc
) t2;
There are definitely other approaches. This will work on ranges up to 100 days long by default (you can use the maximum recursion option to get more).
If you have more data, a numbers table of some sort is helpful.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加