Query Help

[Former Member]

Dear Experts,

I want to create query for,

Customer who has spent less than or equal to £10k revenue within las 12 months.

Can any one tell me?

Thanks,

[former_member184146 · Accepted solution]

Try this

SELECT T0.CardCode, MAX(T0.Cardname) as Customer,  SUM(T0.doctotal) as "Amount(LC)"

FROM OINV T0

WHERE

T0.[DocDate]  > =[%0] and  T0.[DocDate] <=[%1]

GROUP BY T0.CardCode

having sum(T0.Doctotal) <= '10000'

Order by SUM(T0.doctotal) DESC

--Manish

[Former Member]

Hi,

Try:

SELECT T0.CardCode, MAX(T0.Cardname) as Customer,  SUM(T0.doctotal) as 'Amount(LC)'

FROM OINV T0

WHERE DateDiff(mm,T0.[DocDate],Getdate()) Between 1 and 12

GROUP BY T0.CardCode

having sum(T0.Doctotal) <= 10000

Order by SUM(T0.doctotal) DESC

Thanks,

Gordon

[Former Member]

Hi Santosh ,

Please check the below mentioned query for your reference.

SELECT T0.CardCode, MAX(T0.Cardname) as Customer,  SUM(T0.doctotal) as "Amount(LC)"

FROM dbo.OINV T0

WHERE (T0.Doctotal) < $10k

and  t0.docdate >= [%0\] and t0.docdate <= [%1\]

GROUP BY T0.CardCode

Order by SUM(T0.doctotal) DESC