CODEWITHSUNDEEP
asp.netsqlvb.netdate
Jason
Jason

Reputation: 15

Calculating across date ranges

Hi all I have a task where I have to calculate a price across multiple date ranges and I am stuck on how I would do it.

I have query that returns a set of date ranges and a price for each rage the ranges are sequential and are UK dates & currency

FromDate     ToDate         PricePerDay
01/05/2013   06/05/2013     £7
07/05/2013   20/05/2013     £12

The query has 2 parameters: @date - which is a day the client wants training. @NumberOfDays - which is the number of days.

Now if the client submits the @date = 02/05/2013 and number of NumberOfDays = 2 the query will only return the first line and its fairly easy to read the data back and say ok the total price will be 2x£7.

But if the client submits @date = 04/05/2013 and number of NumberOfDays = 7 then it will return both lines above and I will have to calculate as follows

3 Days @ £7 and 4 days @ £12 because the date the client selected crosses both ranges.

I have to do this in a VB.net class (business Logic Class) company policy that DB is storage only and should not define business rules.

Any help or tips would be appreciated on this.

Jason

Upvotes: 0

Views: 139

Answers (1)

Laurence
Laurence

Reputation: 10976

Here's an answer in SQL (SQL Server)

I've written it as a stored procedure for my convenience in using sqlfiddle. You could embed the SQL in a VB.Net clas instead.

It works by picking out rows that are in the specified date range (I think you already have this bit figured out). It then truncates each range if necessary to fit in the specified range. Finally it works out how many days are in each of the truncated ranges, multiplies by the cost for that range and then adds them all up.

Create Table Rates (
  FromDate datetime,
  ToDate datetime,
  PricePerDay money
);

Insert Into Rates (FromDate, ToDate, PricePerDay) Values
  ('2013-05-01', '2013-05-06', 7),
  ('2013-05-07', '2013-05-20', 12);

GO

Create Procedure Cost(@FromDate datetime, @Days int) As
  Declare @ToDate date
  Set @ToDate = DateAdd(Day, @Days, @FromDate)
  Select 
    Sum(DateDiff(Day, FromDate, ToDate) * PricePerDay) As TotalCost
  From (
    Select
      Case When @FromDate > FromDate Then @FromDate Else FromDate End As FromDate,
      Case When @ToDate < DateAdd(Day, 1, ToDate) Then @ToDate Else DateAdd(Day, 1, ToDate) End As ToDate,
      PricePerDay
    From
      Rates
    Where
      FromDate < @ToDate And
      ToDate > @FromDate
  ) X
GO

exec Cost '2013-05-02', 2
GO
exec Cost '2013-05-04', 7

Upvotes: 2

Related Questions