How to sum() prices between date ranges?-CodePudding

I'm trying to figure out how to make an operation in my table to get a sum of prices by periods; basically i have 4 tables: 'Rooms','Medias','Details' and 'Prices'; Room is my main table with IDs, and i need to join child tables to get details, media and prices for each unit. That works fine as now:

@From as date
@To as date

SELECT  
Unit.Id,
Unit.Description,
Details.Description,
Medias.path,

FROM Unit
LEFT JOIN Details
    ON Unit.Id = Details.UnitId
LEFT JOIN Medias
    ON Unit.Id = Medias.UnitId

I now need to add a third join to 'Price' table who has a structure like this:

   Id  UnitId      From         To        Price    
  -----------------------------------------------
    1   1855   2022-06-18  2022-07-08  203.8600        
    2   1855   2022-07-09  2022-08-12  128.5700    
    3   1855   2022-08-13  2022-08-19  102.8600  
    4   2600   2022-06-18  2022-07-08  231.8600        
    5   2600   2022-07-09  2022-08-12  322.5700    
    6   2600   2022-08-13  2022-08-19  122.8600

What i'm trying to do is to add to my select a sum() of Prices over the period that comes to my stored procedure from the variables @From and @To, to get out a total price for a period passed.

For example passing @From = '2022-07-07' and @To = '2022-07-11' it is supposed to give me: 664.86 (203.8600 203.8600 128.5700 128.5700) for the unit 1855 and 1108.86 (231.8600 231.8600 322.5700 322.5700) for the unit 2600

Hope i made this clear, thank you for helping!

CodePudding user response：

Since you only require the Price you could use a correlated subquery, something like:

select u.Id, u.Description, d.Description, m.Path, ( 
   select Sum(Price)
     from Prices p
     where p.UnitId = u.Id and p.from >= @From and p.to <= @To
 ) as Price
from Unit u
left join Details d on d.UnitId = u.Id 
left join Medias m on m.UnitId = u.Id;