SQL: Apply 2 condition on the quantiy field of the invoice lines when quantity>Value and sum(quantiy) <10000 fro a customer

APHComputersLtd
APHComputersLtd used Ask the Experts™
on
Hi Dear Experts,
in the Invoice line table i need to implement the below conditions on the quantity field. first make sure quantity is not exceed than a certain amount and the total line amount for all the invoices for the customer don't exceed 10000.
Please help as my subqureies don't seem to be working.
Many thanks in  advance.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
Which database?
Top Expert 2009

Commented:
Not really clear on your requirements. You only want to query the records where quantity is less than a given amount for that invoice, and you want the sum of those records only where the sum is <= 10000

select invoice, sum(quantity) as tot from a
  where quantity <= 1000 group by invoice
  having sum(quantity) < 10000;

Author

Commented:
thanks for your reply, the Database is sql server and it seems i am using the same as your syntax but some how it doesn't take into acount this   (SUM(INV1.Quantity) < 10000)
and it includes the customer who bought over 10000 .
SELECT     SUM(INV1.Quantity) AS Expr1, OINV.CardCode
FROM         OINV INNER JOIN
                      INV1 ON OINV.DocEntry = INV1.DocEntry INNER JOIN
                      OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN
                      OITM ON OINV.DocEntry = OITM.DocEntry INNER JOIN
                      OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod
WHERE     (DATEDIFF(dd, OINV.DocDueDate, GETDATE()) < 365) AND (INV1.Quantity < 3500) AND (OITB.U_APH_RDCOGRP = N'K/G')
GROUP BY OINV.CardCode
HAVING      (SUM(INV1.Quantity) < 10000)
Thanks.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Top Expert 2009

Commented:
Well, I am still confused. The customer may have bought over 10000, but you specify in the where clause only purchases of < 3500. What about purchases of > 3500. Your query will only calculate based on individual rows with qty < 3500 and ignore other rows.

Author

Commented:
It must include document lines only where the customer does not have any transactions that exceed 3500 units and the total sales to that customer of the specific item type are less than 10000 in the last 12 months
Top Expert 2009
Commented:
I don't quite understand your table structure but it sounds like you may want:



select * from oinv a
  where 3500 >= (select max(quantity) maxqty from oinv b where a.customer_id = b.customer_id)
  group by customer, item_type
  having sum(qty) < 10000

Author

Commented:
Yes, but i need to texclude totally those lines that their totals exceed 10000 simultanously.
I beleive this is what i am looking for:
SELECT     SUM(Quantity) AS TotalQty
FROM         INV1 AS a
GROUP BY ItemCode
HAVING      (SUM(Quantity) < 10000) AND (MAX(Quantity) < 3500)
Do you agree?
Thanks.
 
Top Expert 2009

Commented:
>>HAVING      (SUM(Quantity) < 10000) AND (MAX(Quantity) < 3500)

Actually, yes that will do the same thing, not sure why I didn't consider that. :)

But since I'm not clear on your schema I cannot say whether it is what you are actually looking for. Some test cases would be the way to go to prove it.

Author

Commented:
Thanks very much for your help.

Author

Commented:
The solution was easy to understand and does the job.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial