Error joining two query's

• brett.y

  Ten Centuries

  Points: 1310

  More actions

  March 8, 2013 at 4:45 am

  #290847

  I have two working query's but when I try to join them it errors.

  I have simply used :

  SELECT * FROM

  (

  QUERY 1

  )G

  JOIN

  (QUERY 2

  )H

  ON G.xxxxx = H.xxxxx

  If I use the above with query 1 joining query 1 it works (get two identical tables joined nicely together) but there is something about query 2, I think it could be that local variables are used??? I got query 2 from the internet and it does the job I want.

  Can anyone help with this please????

  Below is the query that faults with 'Incorrect syntax' messages

  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

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  March 8, 2013 at 10:34 am

  #1595068

  Your intuition was correct. You cannot declare variables inside parentheses where only a derived table should exist. You also cannot use WITH to begin a CTE inside parenethese where a derived table should exist, but you can achieve what you want with some simple re-arranging of your SQL.

  Try this:

  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;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• brett.y

  Ten Centuries

  Points: 1310

  More actions

  March 9, 2013 at 2:24 am

  #1595202

  That worked a treat, thanks so much.

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  March 9, 2013 at 9:30 am

  #1595241

  Not a problem. Thanks for the feedback.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato