Invoice Count

[Former Member]

Hi Gurus,

i need a query to show me a supplier who has invoiced me 5 or less time in a month

Regards

Francis

[KennedyT21]

what do you mean.

what is the requirement, you need the balance of the supplier also?

Rgds

[Former Member]

yes

97yesyes

[KennedyT21]

try this

SELECT T0.[CardCode],

       T0.[CardName],

       COUNT(T0.docnum) as COUNT , Balance =( SELECT Balance  FROM ocrd WHERE cardcode=t0.cardcode)

FROM   OPCH T0

WHERE  T0.[DocDate] BETWEEN [%0] AND [%1]

AND CANCELED = 'N' AND t0.docstatus='O'

GROUP BY

       T0.[CardCode],

       T0.[CardName]

HAVING COUNT(docnum) <= 5

[kothandaraman_nagarajan]

Hi,

Please try this:

SELECT T0.[CardCode],

       T0.[CardName],

       COUNT(T0.docnum) as Count

FROM   OPCH T0

WHERE  T0.[DocDate] BETWEEN [%0] AND [%1]

GROUP BY

       T0.[CardCode],

       T0.[CardName]

HAVING COUNT(docnum) <= '5'

Thanks

[frank_wang6]

SELECT T0.[CardCode],

       T0.[CardName],

       COUNT(T0.docnum) as Count

FROM   OPCH T0

WHERE  T0.[DocDate] BETWEEN [%0] AND [%1]

AND CANCELED = 'N'

GROUP BY

       T0.[CardCode],

       T0.[CardName]

HAVING COUNT(docnum) <= 5

I think removing canceled document will be better.

Frank

[Former Member]

hi frank,

This shows me greater than 5 but i changed can it show the balance

27

[Former Member]

hi frank,

This shows me greater than 5 but i changed can it show the balance

28

[Former Member]

hi Nagarajan,

This shows me greater than 5 but i changed can it show the balance

31

[KennedyT21]

Try this

SELECT T0.[CardCode],

       T0.[CardName],

       COUNT(docnum)

FROM   OPCH T0

WHERE  T0.[DocDate] BETWEEN [%0] AND [%1]

GROUP BY

       T0.[CardCode],

       T0.[CardName]

HAVING COUNT(docnum) <= '5'