SELECT * FROM

(SELECT A.WeekStarting, A.kWh/ CASE WHEN B.[AP7 Production]=0 THEN 1 ELSE  B.[AP7 Production] END as [kWh per Tonne]
FROM
(select min(dateadd(day, datediff(day,'19000101',t_stamp)/7*7, '19000101')) 
as WeekStarting,
max(AP7_main_incomer_kwh)-min(AP7_main_incomer_kwh) as kWh
from Ignition.dbo.Burton_Latimer_kWh
WHERE AP7_main_incomer_kwh != 0
and datepart(yy,t_stamp) = (select datepart(yy,getdate()))--Only do current year
and datediff(day,'19000101',t_stamp)/7 > 5901 -- miss out first EVER week of dodgy data as its of no use
group by datediff(day,'19000101',t_stamp)/7 )A
join
(select min(dateadd(day, datediff(day,'19000101',datecreated)/7*7, '19000101')) 
as WeekStarting,
SUM(CASE WHEN Plant = 'AP7' THEN Totalweight ELSE 0 END) *1000 AS [AP7 Production]
from IgnitionSSISControl.dbo.SSIS_ProductionDetail
WHERE datepart(yy,datecreated) = (select datepart(yy,getdate()))--Only do current year
and datediff(day,'19000101',datecreated)/7 > 5901 -- miss out first EVER week of dodgy data as its of no use
group by datediff(day,'19000101',datecreated)/7)B
on A.WeekStarting = B.WeekStarting )g

--order by A.WeekStarting

join

(
DECLARE @year AS CHAR(4)
SET @year = (select datepart(yy,getdate()))
DECLARE @firstDay DATETIME
SET @firstDay = CAST(@year + '0101' AS DATETIME)
;WITH dayCTE 
AS (
    SELECT DATEADD(dd,ROW_NUMBER() OVER (ORDER BY name) -1, @firstDay) AS yearday
    FROM    master.dbo.spt_values
)
,weekCTE
AS
(
    SELECT  yearday, 
            DATEPART(WW,yearday) AS weekno
    FROM dayCTE
    WHERE YEAR(yearday) = @year
)


SELECT  @year        AS [YEAR],
        weekno       AS WEEKNUMBER, 
        MIN(yearday) AS STARTDATE, 
        MAX(yearday) AS ENDDATE

FROM weekCTE
GROUP BY weekno)h
--ORDER BY weekno

ON g.Weekstarting = h.Startdate

DECLARE @year AS CHAR(4);
SET @year = ( SELECT    DATEPART(yy, GETDATE()));

DECLARE @firstDay DATETIME;
SET @firstDay = CAST(@year + '0101' AS DATETIME);

WITH    dayCTE
          AS ( SELECT   DATEADD(dd, ROW_NUMBER() OVER ( ORDER BY name ) - 1, @firstDay) AS yearday
               FROM     master.dbo.spt_values
             ),
        weekCTE
          AS ( SELECT   yearday,
                        DATEPART(WW, yearday) AS weekno
               FROM     dayCTE
               WHERE    YEAR(yearday) = @year
             )
    SELECT  *
    FROM    ( SELECT    A.WeekStarting,
                        A.kWh / CASE WHEN B.[AP7 Production] = 0 THEN 1
                                     ELSE B.[AP7 Production]
                                END AS [kWh per Tonne]
              FROM      ( SELECT    MIN(DATEADD(day, DATEDIFF(day, '19000101', t_stamp) / 7 * 7, '19000101')) AS WeekStarting,
                                    MAX(AP7_main_incomer_kwh) - MIN(AP7_main_incomer_kwh) AS kWh
                          FROM      Ignition.dbo.Burton_Latimer_kWh
                          WHERE     AP7_main_incomer_kwh != 0
                                    AND DATEPART(yy, t_stamp) = ( SELECT    DATEPART(yy, GETDATE())
                                                                )--Only do current year
                                    AND DATEDIFF(day, '19000101', t_stamp) / 7 > 5901 -- miss out first EVER week of dodgy data as its of no use
                          GROUP BY  DATEDIFF(day, '19000101', t_stamp) / 7
                        ) A
                        JOIN ( SELECT   MIN(DATEADD(day, DATEDIFF(day, '19000101', datecreated) / 7 * 7, '19000101')) AS WeekStarting,
                                        SUM(CASE WHEN Plant = 'AP7' THEN Totalweight
                                                 ELSE 0
                                            END) * 1000 AS [AP7 Production]
                               FROM     IgnitionSSISControl.dbo.SSIS_ProductionDetail
                               WHERE    DATEPART(yy, datecreated) = ( SELECT    DATEPART(yy, GETDATE())
                                                                    )--Only do current year
                                        AND DATEDIFF(day, '19000101', datecreated) / 7 > 5901 -- miss out first EVER week of dodgy data as its of no use
                               GROUP BY DATEDIFF(day, '19000101', datecreated) / 7
                             ) B ON A.WeekStarting = B.WeekStarting
            ) g --order by A.WeekStarting
            JOIN ( SELECT   @year AS [YEAR],
                            weekno AS WEEKNUMBER,
                            MIN(yearday) AS STARTDATE,
                            MAX(yearday) AS ENDDATE
                   FROM     weekCTE
                   GROUP BY weekno
                 ) h --ORDER BY weekno
            ON g.Weekstarting = h.Startdate;