Sum Actual in different period I have the following table test : test is a table that contains the number of week , start and end day of each week for a month

WeekOfMonth  StartDate                  EndDate
1            2014-05-01 00:00:00.000    2014-05-03 00:00:00.000
2            2014-05-04 00:00:00.000    2014-05-10 00:00:00.000
3            2014-05-11 00:00:00.000    2014-05-17 00:00:00.000
4            2014-05-18 00:00:00.000    2014-05-24 00:00:00.000
5            2014-05-25 00:00:00.000    2014-05-31 00:00:00.000
6            2014-12-25 00:00:00.000    2014-12-31 00:00:00.000


and I have anothor table Actual

 Actual     StartDate                  EndDate
100          2014-05-01 00:00:00.000    2014-05-01 00:00:00.000
22           2014-05-01 00:00:00.000    2014-05-02 00:00:00.000
34           2014-05-11 00:00:00.000    2014-05-16 00:00:00.000
45           2014-05-11 00:00:00.000    2014-05-16 00:00:00.000
58           2014-05-25 00:00:00.000    2014-05-31 00:00:00.000


I need the following result

Actual          StartDate                   EndDate
122          2014-05-01 00:00:00.000    2014-05-03 00:00:00.000
79           2014-05-11 00:00:00.000    2014-05-17 00:00:00.000
58           2014-05-25 00:00:00.000    2014-05-31 00:00:00.000


I try the following query , but it returns incorrect results

Select sum(Actual) as Actual , t.StartDate, t.EndDate
from Actual 
inner join test t  ON StartDate between @StartDate and @EndDate AND EndDate BETWEEN @StartDate     And @EndDate 
group by  t.StartDate,  t.EndDate


In my case it will return

Actual          StartDate                   EndDate
122          2014-05-01 00:00:00.000    2014-05-03 00:00:00.000
122          2014-05-11 00:00:00.000    2014-05-17 00:00:00.000
122          2014-05-25 00:00:00.000    2014-05-31 00:00:00.000


How can I change it ? Any help would be the most appreciated .
Your join has to describe a condition where rows in actual are matched to rows in test. In your example, you're just filtering rows using variables; the join is effectively a CROSS JOIN (a cartesian product), which is why you get SUM(actual)=122 on every row.

The following query should solve your problem, with the assumption that rows in actual cannot span multiple weeks in test.

SELECT SUM(a.Actual), w.StartDate, w.EndDate
FROM actual AS a
INNER JOIN test AS w ON
    a.StartDate&lt;=w.EndDate AND
    a.EndDate&gt;=w.StartDate
GROUP BY w.StartDate, w.EndDate;


If rows in actual can span multiple week, you may need to calculate some type of distribution of those. Here's a simple idea:

WITH cte AS (
    SELECT a.Actual,
           1+DATEDIFF(dd, a.StartDate, a.EndDate) AS actualDays,
           (CASE WHEN a.StartDate&lt;w.StartDate THEN w.StartDate ELSE a.StartDate END) AS weekStartDate,
           (CASE WHEN a.EndDate&gt;w.EndDate THEN w.EndDate ELSE a.EndDate END) AS weekEndDate,
           w.StartDate, w.EndDate
    FROM actual AS a
    INNER JOIN test AS w ON
        a.StartDate&lt;=w.EndDate AND
        a.EndDate&gt;=w.StartDate)

SELECT SUM(a.Actual*(1+DATEDIFF(dd, weekStartDate, weekEndDate))/actualDays) AS Actual,
       StartDate, EndDate
FROM cte
GROUP BY StartDate, EndDate;


Explanation, using an example: assume Actual=1000, StartDate='2014-05-02', EndDate='2014-05-13':

The common table expression (cte) will match this with three rows in test.

a.Actual  w.StartDate  w.EndDate
1000      2014-05-01   2014-05-03
1000      2014-05-04   2014-05-10
1000      2014-05-11   2015-05-17


Note the CASE expressions: If a.StartDate is within the week, this is the weekStartDate, otherwise, use w.StartDate. Same thing for the end dates, but the other way around.

a.Actual  w.StartDate  w.EndDate   weekStartDate  weekEndDate
1000      2014-05-01   2014-05-03  2014-05-02     2014-05-03   -- start date the 2nd
1000      2014-05-04   2014-05-10  2014-05-04     2014-05-10
1000      2014-05-11   2015-05-17  2014-05-11     2014-05-13   -- end date the 13th


Now all we have to do is calculate the number of days between weekStartDate and weekEndDate for each row. Divide this number of days by the total number of days for the Actual (actualDays=12), and you get a distribution key:

a.Actual  w.StartDate  w.EndDate   (distribution key)
1000      2014-05-01   2014-05-03  2/12 =0.167
1000      2014-05-04   2014-05-10  7/12 =0.583
1000      2014-05-11   2015-05-17  3/12 =0.250


... and multiply that distribution key by the original Actual:

w.StartDate  w.EndDate   (Actual)
2014-05-01   2014-05-03  167
2014-05-04   2014-05-10  583
2014-05-11   2015-05-17  250
                       =1000