CODEWITHSUNDEEP
sqlsql-server
user2270911
user2270911

Reputation: 305

Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'month'

I just inherited the program below from a colleague and I am having trouble figuring out what the issue is. I am using MS SQL server and I believe he was using different software. I keep on getting the error message: Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'month' when I run the query which uses the Temp tables and a table in our system. Again, I can't seem to figure out how to correct the syntax issue. 

CREATE TABLE #Temp_Contact (
    Person_ID INT,
    Contact_Date DATETIME,
    Success INT)
GO

INSERT INTO #Temp_Contact (Person_ID, Contact_Date, Success)
SELECT Person_ID, Contact_Date,
       gw_ppp.dbo.fnWasContacted(Contact_Method, Contact_Result, Participant)
FROM gw_dw.dbo.DimContacts_Child
GO

CREATE TABLE #Temp_Months (Month VARCHAR(30))
INSERT INTO #Temp_Months 
SELECT 'January' UNION ALL
SELECT 'February' UNION ALL
SELECT 'March' UNION ALL
SELECT 'April' UNION ALL
SELECT 'May' UNION ALL
SELECT 'June' UNION ALL
SELECT 'July' UNION ALL
SELECT 'August' UNION ALL
SELECT 'September' UNION ALL
SELECT 'October' UNION ALL
SELECT 'November' UNION ALL
SELECT 'December';

This is the query which generates the error message

SELECT lft.Person_ID,  
m.Month month, 
gw_PPP.dbo.fnFmtContact(src.cnt) result
  FROM gw_dw.dbo.DimContacts_Child AS lft
  JOIN  #Temp_Months m
  on m.Month=month (lft.Contact_Date)
  LEFT OUTER JOIN
    (SELECT Person_ID, 
     DATENAME(month, MONTH(Contact_Date)) as Month, 
     sum(Success) as cnt
     FROM #Temp_Contact
     GROUP BY Person_ID, DATENAME(month, MONTH(Contact_Date))) AS src
  ON (lft.Person_ID = src.Person_ID AND month (lft.Contact_Date) = src.month)

Upvotes: 1

Views: 5009

Answers (2)

Guffa
Guffa

Reputation: 700382

You are missing a comma here:

GROUP BY Person_ID, DATENAME(month, MONTH(Contact_Date)), month) AS src
                                                        ^

You have two JOIN clauses, but only one ON clause. You need one for each join.

You are missing the month field in the #Temp_Contact table.

Upvotes: 0

Pascalz
Pascalz

Reputation: 2378

SELECT Person_ID, month, sum(Success) cnt
     FROM #Temp_Contact

I don't see month in #temp_Contact

CREATE TABLE #Temp_Contact (
    Person_ID INT,
    Contact_Date DATETIME,
    Success INT)
GO

Upvotes: 1

Related Questions