February 2, 2005 at 12:38 pm
I have an order detail table that I need to retrieve a report on order size.
The table has all the usual order info stuff, order number, product number, quantity, date and so on.
So the report will show the number of orders with a total item quantity of 1, or 2, or 3, or 4, or 5, or 6, or 7, or 8, or 9, or 10+.
This part I have gotten figued out.
SELECT
COUNT(distinct a.ordcnt) as '# Orders',
a.Order_Size
FROM
(
SELECT
OrderNumber as ordcnt,
Case When SUM(Quantity) >= 10 Then 10 ELSE SUM(Quantity) END Order_Size
FROM
impGiftPurchaseDetail
GROUP BY
OrderNumber
) a
The part that is giving me the problems is this; there are two types of product classes (the product ID can be used as a reference for which class a product belongs to) and the report must also show a breakdown of how many items from each product class were purchased in each 'Order Size' .
For Example
Order Size TotalOrders ProductClassAItems ProductClassBItems
1 item 5 3 2
2 items 3 1 5
and so on....
I tried using the same logic as with retrieving the order size but it only works for the orders with 1 item after that the numbers just don't add up properly.
Any help would be greatly appreciated (if this makes sence)
February 4, 2005 at 12:38 pm
Don't worry figured it out using a bunch of nested SQL statements and mulitiple stored procs for the report. Thanks for trying if you did.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply