ник: Lukas
Со служебной табличкой "календарь":
SELECT
t2.OrderID,
Sum(t2.RateOnDay) AS Total
FROM (
SELECT
tblOrders.OrderID,
(SELECT TOP 1 t1.Rate FROM tblOrderDetails t1 WHERE t1.OrderID=tblOrders.OrderID AND t1.DateFrom<=tblCalendar.OnDate ORDER BY t1.DateFrom Desc) AS RateOnDay
FROM
tblCalendar
INNER JOIN tblOrders ON (tblCalendar.OnDate Between tblOrders.DateFrom AND tblOrders.DateTo)
) AS t2
GROUP BY
t2.OrderID;
|
Блин, забыл ставку поделить на количество дней в месяце.
