March 8, 2013 at 4:45 am
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
March 8, 2013 at 10:34 am
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
March 9, 2013 at 2:24 am
That worked a treat, thanks so much. 🙂
March 9, 2013 at 9:30 am
Not a problem. Thanks for the feedback.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply