Home SQL Input Date Range to work out Rate per day
Reply: 2

SQL Input Date Range to work out Rate per day

DanF
1#
DanF Published in 2018-02-13 18:45:01Z

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.

Gordon Linoff
2#
Gordon Linoff Reply to 2018-02-13 18:50:01Z

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.

DanF
3#
DanF Reply to 2018-02-13 19:39:28Z

This is the T-SQL I ended up using to get the required result. Thanks to Gordon for getting me on the right track.

with cte as (
  select fromdate as dte, todate
  from Table1
  union all
  select dateadd(day, 1, dte), todate
  from cte where dte < todate
 )
select dte, t2.rate
from cte cross apply
 (select top 1 t2.rate
  from Table2 t2
  where t2.fromdate <= cte.dte
  order by t2.fromdate desc
 ) t2;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.428698 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO