Syntax Error? why doesn't this work??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Syntax Error? why doesn't this work???

Author

Topic

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-08-11 : 15:45:13

I am trint to join to selects , is that posible

SELECT
(SELECT
SUM(A.PdtTotalCount * (A.PdtFormLength / 12.00)) AS DailyFootage, A.PdtProdDate,
SUM(A.PdtTotalCount * A.PdtNumUp) AS QtyName,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='DN') THEN
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00)
ELSE 0
END)
AS DownTime,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='IT') THEN
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00)
ELSE 0
END)
AS IdleTime,
AVG ((7.5-(A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs))/7.5) as Efficiency,
SUM (A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs) AS ProductGroupHours
FROM DMAIL.TblProdDetail
WHERE PdtMachType='F'
GROUP BY PdtProdDate) AS A

Left Join

(SELECT
SUM(B.PdtTotalCount * (B.PdtFormLength / 12.00)) AS DailyFootage, B.PdtProdDate,
SUM(PdtTotalCount * PdtNumUp) AS QtyName,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='DN') THEN
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00)
ELSE 0
END)
AS DownTime,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='IT') THEN
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00)
ELSE 0
END)
AS IdleTime,
AVG ((7.5-(B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs))/7.5) as Efficiency,
SUM (B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs) AS ProductGroupHours
FROM DMAIL.TblProdDetail
WHERE PdtMachType='I'
GROUP BY PdtProdDate) AS B
ON A.PdtProdDate=B.PdtProdDate

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-11 : 15:47:07

Are you sure you want to JOIN them together? I think all you need to do is UNION them together.

Tara

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-08-11 : 15:51:39

I want to have the two result sets side by side, so i renamed the columns from the second query by addin an "I" to each column

SELECT
SUM(A.PdtTotalCount * (A.PdtFormLength / 12.00)) AS DailyFootage, A.PdtProdDate,
SUM(A.PdtTotalCount * A.PdtNumUp) AS QtyName,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='DN') THEN
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00)
ELSE 0
END)
AS DownTime,
SUM (CASE WHEN (A.PdtShiftID = 1 OR A.PdtShiftID = 2 and A.PdtOpCode='IT') THEN
(DATEDIFF(mi,A.PdtTimeStart, A.PdtTimeStop) / 60.00)
ELSE 0
END)
AS IdleTime,
AVG ((7.5-(A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs))/7.5) as Efficiency,
SUM (A.PdtPG1Hrs + A.PdtPG2Hrs + A.PdtPG3Hrs + A.PdtPG4Hrs + A.PdtPG5Hrs) AS ProductGroupHours
FROM DMAIL.TblProdDetail
WHERE PdtMachType='F'
GROUP BY PdtProdDate) AS A,

Left Join

(SELECT
SUM(B.PdtTotalCount * (B.PdtFormLength / 12.00)) AS DailyFootageI, B.PdtProdDate,
SUM(PdtTotalCount * PdtNumUp) AS QtyNameI,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='DN') THEN
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00)
ELSE 0
END)
AS DownTimeI,
SUM (CASE WHEN (B.PdtShiftID = 1 OR B.PdtShiftID = 2 and B.PdtOpCode='IT') THEN
(DATEDIFF(mi,B.PdtTimeStart, B.PdtTimeStop) / 60.00)
ELSE 0
END)
AS IdleTimeI,
AVG ((7.5-(B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs))/7.5) as EfficiencyI,
SUM (B.PdtPG1Hrs + B.PdtPG2Hrs + B.PdtPG3Hrs + B.PdtPG4Hrs + B.PdtPG5Hrs) AS ProductGroupHoursI
FROM DMAIL.TblProdDetail
WHERE PdtMachType='I'
GROUP BY PdtProdDate) AS B
ON A.PdtProdDate=B.PdtProdDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources