I have a SQL Server database with this table:

and I am trying to get RatePrice between specific dates for a certain TypeID e.g.

DECLARE @StartDate DATE = '2022-02-26';
DECLARE @EndDate DATE = '2022-03-02';
DECLARE @TypeID int = 10;
DECLARE @RatePrice money;
DECLARE @RateDate DATE;

WHILE (@StartDate <= @EndDate)
BEGIN
    SELECT
        @RatePrice = RatePrice,
        @RateDate = @StartDate
    FROM mgaRate
    WHERE mgaRate.TypeID = @TypeID
      AND ((RateStart BETWEEN @StartDate AND @EndDate) OR
           (RateEnd BETWEEN @StartDate AND @EndDate) OR
           (RateStart <= @StartDate AND RateEnd >= @EndDate)
          )

    PRINT @RateDate;
    PRINT @RatePrice;

    SET @StartDate = DATEADD(day, 1, @StartDate);
END;

But the result is not correct:

2022-02-26
400.00

2022-02-27
400.00

2022-02-28
400.00

2022-03-01
400.00

2022-03-02
400.00

In February the price should show 300.00.

CodePudding user response：

You can use a calendar table to get all the dates in the range you need, and for each date you can get the rate using a subquery from the original table:

DECLARE @StartDate DATE = '2022-02-26';
DECLARE @EndDate DATE = '2022-03-02';
DECLARE @TypeID int = 10;

with calendar as (
    select @StartDate as d
    union all 
    select dateadd(day, 1, d)
    from calendar
    where d < @EndDate
)
select
  d,
  (select RatePrice
  from mgaRate
  where d between RateStart and RateEnd
  and TypeID = @TypeID)
from calendar

You can replace the recursive CTE with an actual calendar table.

Fiddle