Sum Actual in different period
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