Calculate SUM between two dates SQL SERVER

I’m having two tables

Calendar

FirstDate   |  LastDate
------------+-----------    
2020-05-01     2020-05-03
2020-05-02     2020-05-04

ProfileRate

ID |    Date    | Rate | Product
---+------------+------+----------
1    2020-05-01   100    Prod1
2    2020-05-02   200    Prod1
3    2020-05-03   300    Prod1
4    2020-05-04   400    Prod1
5    2020-05-01   150    Prod2
6    2020-05-02   250    Prod2
7    2020-05-03   350    Prod2
8    2020-05-04   450    Prod2

I want to calculate SUM. Something like this

FirstDate   | LastDate    | Prod1  | Prod2 
------------+-------------+--------+-------
2020-05-01    2020-05-03    600      750
2020-05-02    2020-05-04    900      1050

What I’ve tried:

SELECT [Date], SUM([Rate])
FROM [ProfileRate]
WHERE [Date] BETWEEN '2020-05-01' AND '2020-05-04'
GROUP BY [Date]

Please help me. Thanks

Answer

you have to do several steps to solve the problem:

1. combine calendar records with profiles
2. establish which profiles meet the calendar range
3. total the value for each profile and date range
4. finally establish the detailed information of the calendar

this query meets the conditions

with DataForCalendar as
(
  SELECT IdCalendar,FirstDate,LastDate,Id,DateRate,Rate,Product
  FROM
  Calendar CROSS JOIN ProfileRate 
  where DateRate  between '2020-05-01' and '2020-05-05'
),
DetailForCalendar as 
(
  select IdCalendar,Rate,Product 
  from DataForCalendar
  where   DateRate between FirstDate and LastDate
),
TotalForCalendar as 
(
  select IdCalendar,Product,sum(Rate)As Total from DetailForCalendar
  GROUP BY IdCalendar,Product
)
select   Calendar.IdCalendar,Calendar.FirstDate,Calendar.LastDate,
TotalForCalendar.Product,TotalForCalendar.Total 
from TotalForCalendar inner join 
Calendar on TotalForCalendar.IdCalendar=Calendar.IdCalendar;

in this example you can find step by step how it work

in this example there is one more product and one more calendar.