Hi Guys, I am converting Access SQL to SQL Server and I have two problems:
First of all I cant get the city, state and zip to concatenate. It will not take the "As Location" and it will not allow me to concatenate the fields in the Group By area.
Second I m trying to replace a First() function with a Top() function but Im not having much success. If it is not the first statement I get an error (Incorrect syntax near the keyword 'TOP'.)
Any ideas how I can accomplish what it is I want to do?
Thanx much,
Trudye
BEGIN
SELECT Date AS DateEntered,
LTRIM(RTrim([ResultsCustNa
me])) AS CustName,
Timestamp, LTRIM(RTrim([ResultsPH])) AS Phone,
TOP (1) tblEscalationData.Explain_
followup AS FirstOfExplain_followup,
[ResultsAddr1] AS Address1, [ResultsAddr2] AS Address2,
[ResultsCity] AS City, [ResultsST] AS [State], [ResultsZip] AS Zip,
ERROR
INTO zTmp_Training
FROM tblEscalationData
LEFT JOIN qry_IssuesPrint
ON tblEData.ResultsPKey = qry_IssuesPrint.ResultsPKe
y
LEFT JOIN LOOKUP_DATA
ON tblEData.DSLOAN = LOOKUP_DATA.DSLOAN
LEFT JOIN qry_IssuesPrintDef
ON tblEData.ResultsPKey = qry_IssuesPrintDef.Results
PKey
INNER JOIN qry_CurrentStatus
ON tblEData.ResultsPKey = qry_CurrentStatus.ResultsP
Key
LEFT JOIN tblErrors
ON Error = SUBID
WHERE tblEData.CSRReporting= 1 AND
qry_CurrentStatus.Date
Between @Start_Date And @End_Date
AND Source = @CallCtr
AND ERROR Is Not Null
ORDER BY LTrim(RTrim([ResultsCustNa
me]))
END
--qry_CSRTraining (Group BY Clause)
BEGIN
SELECT DateEntered, LTrim(RTrim([CustName])), [Timestamp],
LTrim(RTrim([Phone])),
[Address1] + ' ' [Address2],
[City] + ' ' + [State] + ' ' + [Zip] As Location,
[Name], ERROR
FROM zTmp_Training
GROUP BY DateEntered, [CustName], [Timestamp],
[Phone], [Address1], [Address2], Location,
[Name], ERROR
END
Start Free Trial