Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error joining two query's Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 4:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:58 AM
Points: 40, Visits: 152
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

Post #1428516
Posted Friday, March 8, 2013 10:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1428694
Posted Saturday, March 9, 2013 2:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:58 AM
Points: 40, Visits: 152
That worked a treat, thanks so much.
Post #1428858
Posted Saturday, March 9, 2013 9:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
Not a problem. Thanks for the feedback.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1428904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse