HI everyone, hope you are well. I need the help in about SQL Query I have this q

Question

HI everyone,

hope you are well.

I need the help in about SQL Query

I have this query but I am getting an error, does anyone can help me with this issue, please?

best regard.

the SQL Query is:

SELECT

r.[DebtorNumber] AS 'Patient Id No.' ,

MAX(CONVERT(VARCHAR(10), CAST(aus.AuditDateTime as date),101)) AS 'Last Worked'

FROM

AgentRangeList a

LEFT JOIN RPT_Calculated r on r.DebtorNumber = a.DebtorNumber and a.ClientCode = r.ClientCode

JOIN AuditDetails aus on r.DebtorNumber = aus.DebtorNumber and r.ClientCode = aus.ClientCode

group by aus.AuditDateTime

and this is the error I am getting

Msg 8120, Level 16, State 1, Line 2
Column 'RPT_Calculated.DebtorNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Explanation / Answer

The reason for the error is column DebtorNumber not available in group by class. When you are using a group by then in SELECT clause you can only use those columns defind in Group by clause. So please change the query as below

SELECT

r.[DebtorNumber] AS 'Patient Id No.' ,

MAX(CONVERT(VARCHAR(10), CAST(aus.AuditDateTime as date),101)) AS 'Last Worked'  

FROM

AgentRangeList a

LEFT JOIN RPT_Calculated r on r.DebtorNumber = a.DebtorNumber and a.ClientCode = r.ClientCode

JOIN AuditDetails aus on r.DebtorNumber = aus.DebtorNumber and r.ClientCode = aus.ClientCode

group by aus.AuditDateTime, r.DebtorNumber

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.