Sales query for repeating customer in a month

[Former Member]

Dear All,

Please help me to find out  the Repeating customers in a month with customer name and doc total.And from this report i shall identify how many customers repeatedly came .Please help me with a query to find the same.

Regards,

Sri

[Johan_H · Accepted solution]

Hi Sri,

Please give this a try:

/* select * from OINV x */

DECLARE @M AS DATETIME = /* x.DocDate */ '[%0]'

SELECT c.CardCode

      ,c.CardName

   ,ISNULL(COUNT(DISTINCT h.DocNum),0) AS NrOfInvoices

FROM OCRD c

     LEFT OUTER JOIN OINV h ON c.CardCode = h.CardCode

WHERE MONTH(h.DocDate) = MONTH(@M)

  AND YEAR(h.DocDate) = YEAR(@M)

GROUP BY c.CardCode

        ,c.CardName

HAVING ISNULL(COUNT(DISTINCT h.DocNum),0) > 1

Regards,

Johan