Amila Amila - 6 months ago 11
SQL Question

DISTINCT value from stored procedure

CREATE PROCEDURE [dbo].[GetIdleCustomerlist](
@num_months Int
)

AS
BEGIN

SELECT DISTINCT
cust.cust_code as cust_code ,
cust.name as cust_name,
MAX (invoice.created_date) as Last_invoice_date

FROM [dbo].[customer] cust LEFT JOIN [dbo].[crm_invoice_header] invoice on cust.cust_code = invoice.cust_code

GROUP BY cust.cust_code ,cust.name,invoice.created_date

HAVING (( MAX (invoice.created_date)< DATEADD(MONTH, -@num_months ,GETDATE())) OR invoice.created_date IS NULL)

ORDER BY CAST (cust.cust_code AS int) ASC

END


I want to remove duplicate customers code, but DISTINCT keyword gives me this error.

Msg 145, Level 15, State 1, Procedure GetIdleCustomerlist, Line 21
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Answer

Remove DISTINCT and remove invoice.created_date in the GROUP BY clause:

SELECT
    cust.cust_code AS cust_code,
    cust.name AS cust_name,
    MAX(invoice.created_date) AS Last_invoice_date      
FROM dbo.customer cust
LEFT JOIN dbo.crm_invoice_header invoice
    ON cust.cust_code = invoice.cust_code
WHERE
    invoice.created_date < DATEADD(MONTH, -@num_months, GETDATE())
    OR invoice.created_date IS NULL
GROUP BY
    cust.cust_code, cust.name
ORDER BY
    ORDER BY CAST(cust.cust_code AS INT)

You can also move the conditions in the HAVING to WHERE

Comments