January 13, 2017 at 7:48 am
I am trying to join two main data tables, I have my outer alias as well as ON relationship but I am getting >>incorrect syntax near B<<<
SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity, CASE WHEN quantity < 0 THEN 'N' ELSE 'P' END AS PointsGrpFROM dbo.Loyalty_Points_Trans ALeft outer join (SELECT top 100 C.AccountNo AS Acc, MONTH(T.CreatedOn) AS Mth, YEAR(T.CreatedOn) AS Yr, SUM(CONVERT(INT, T.Points) * CONVERT(INT, T.Quantity)) AS TotalPtFROM Customers AS C LEFT OUTER JOIN Addresses AS A ON C.RefID = A.ParentID LEFT OUTER JOIN (SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity FROM Loyalty_Points_Trans) AS T ON C.RefID = T.CID WHERE (C.Relationship = 8) AND (C.Grade IN ('Normal')) AND (YEAR(T.CreatedOn) = 2016) AND T.CID=C.RefIDGROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn) B On A.CID=B.RefID
January 13, 2017 at 7:51 am
You're missing a bracket after the GROUP BY in your subquery
GROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn) /*needs a close bracket here*/ B On A.CID=B.RefID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2017 at 8:06 am
GilaMonster - Friday, January 13, 2017 7:51 AMYou're missing a bracket after the GROUP BY in your subqueryGROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn) B /*needs a close bracket here*/ On A.CID=B.RefID
I am still having error.
January 13, 2017 at 8:41 am
Newbi - Friday, January 13, 2017 8:06 AMGilaMonster - Friday, January 13, 2017 7:51 AMYou're missing a bracket after the GROUP BY in your subqueryGROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn) B /*needs a close bracket here*/ On A.CID=B.RefIDI am still having error.
Does this solve the problem?
SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity, CASE WHEN quantity < 0 THEN 'N' ELSE 'P' END AS PointsGrpFROM dbo.Loyalty_Points_Trans A Left outer join (SELECT top 100 C.AccountNo AS Acc, MONTH(T.CreatedOn) AS Mth, YEAR(T.CreatedOn) AS Yr, SUM(CONVERT(INT, T.Points) * CONVERT(INT, T.Quantity)) AS TotalPt FROM Customers AS C LEFT OUTER JOIN Addresses AS A ON C.RefID = A.ParentID LEFT OUTER JOIN (SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity FROM Loyalty_Points_Trans) AS T ON C.RefID = T.CID WHERE (C.Relationship = 8) AND (C.Grade IN ('Normal')) AND (YEAR(T.CreatedOn) = 2016) AND T.CID = C.RefID GROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn)) B On A.CID = B.RefID;
Another way to write this for SQL Server 2005 or newer:
WITH ACte as (SELECT top 100 C.AccountNo AS Acc, MONTH(T.CreatedOn) AS Mth, YEAR(T.CreatedOn) AS Yr, SUM(CONVERT(INT, T.Points) * CONVERT(INT, T.Quantity)) AS TotalPtFROM Customers AS C LEFT OUTER JOIN Addresses AS A ON C.RefID = A.ParentID LEFT OUTER JOIN (SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity FROM Loyalty_Points_Trans) AS T ON C.RefID = T.CIDWHERE (C.Relationship = 8) AND (C.Grade IN ('Normal')) AND (YEAR(T.CreatedOn) = 2016) AND T.CID = C.RefIDGROUP BY C.AccountNo, MONTH(T.CreatedOn), YEAR(T.CreatedOn))SELECT CID, CONVERT(datetime, CreatedOn, 103) AS CreatedOn, Points, Quantity, CASE WHEN quantity < 0 THEN 'N' ELSE 'P' END AS PointsGrpFROM dbo.Loyalty_Points_Trans A Left outer join ACte B On A.CID = B.RefID;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy