% Of Orders where 100% of the line items are in the top 500 parts

r270ba
r270ba used Ask the Experts™
on
I need to write a query that will give me a % of our orders that are COMPLETELY made up of the top 500 parts that have been ordered at least 4 months a year (to reduce any outliers).

The following code shows me all the items that have been ordered at least 4 months in a year, along with the invoice number they were ordered on.  I would like to include ALL the line items on that invoice and get a % of invoices that were 100% complete.

Does this make sense?
select  *
from    ( select    sales_doc_num,
                    item_number,
                    item_description,
                    quantity
          from      spv3SalesLineItemHistory
          where     spv3SalesLineItemHistory.Item_Number in (
                    select  item_number
                    from    spvItemMaster
                    where   spvItemMaster.Item_Class_Code = 'jte-parts' )
        ) A
        left join ( select  sales_doc_num,
                            sales_doc_type,
                            doc_date
                    from    spv3SalesDocumentHistory
                  ) B on a.sales_doc_num = b.sales_doc_num
        left join ( select  item_number,
                            standard_cost
                    from    spvItemMaster
                  ) C on a.item_number = c.item_number
        INNER  JOIN ( Select    SLIH.item_number
                      FROM      spv3SalesDocumentHistory SDH
                                inner join spv3SalesLineItemHistory SLIH on SDH.sales_doc_num = SLIH.sales_doc_num
                      WHERE SDH.doc_date > DATEADD(YYYY,-1,GETDATE())
                      Group By  SLIH.item_number
                      Having    COUNT(distinct month(SDH.doc_date)) >= 4
                    ) S ON S.item_number = A.item_number
where b.sales_doc_type='invoice'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris LuttrellSenior Database Architect

Commented:
to determine "top 500 parts" what is the ordering criteria?  I am thinking "sum(Quantity) desc", for the most sold parts.

Author

Commented:
sum(quantity) would be correct as long as they fall into the ordered at least 4 months catagory...
Chris LuttrellSenior Database Architect

Commented:
as long as you are running on 2005 or 2008 and can use a CTE try this query.  
(and the leading ";" is important, a CTE must start with a "WITH" that has no preceding SQL statements that it could possibly belong to, ie the statement did not end with the closing ; )

;WITH    Top500SalesItems
          AS ( Select TOP 500
                        SLIH.item_number
               FROM     spv3SalesDocumentHistory SDH
                        inner join spv3SalesLineItemHistory SLIH on SDH.sales_doc_num = SLIH.sales_doc_num
               WHERE    SDH.doc_date > DATEADD(YYYY, -1,
                                               '2004-07-31 00:00:00.000')
               Group By SLIH.item_number
               Having   COUNT(distinct month(SDH.doc_date)) >= 4
               ORDER BY SUM(SLIH.quantity) desc
             ) ,
        AllOrdersWithTop500Items
          AS ( SELECT DISTINCT
                        SDH.sales_doc_num
               FROM     spv3SalesDocumentHistory SDH
                        INNER JOIN spv3SalesLineItemHistory SLIH ON SDH.sales_doc_num = SLIH.sales_doc_num
                        INNER JOIN Top500SalesItems OrdersInTop500 ON OrdersInTop500.item_number = SLIH.item_number
               WHERE    SDH.doc_date > DATEADD(YYYY, -1,
                                               '2004-07-31 00:00:00.000')
             ) ,
        OrdersWithNonTop500Items
          AS ( SELECT DISTINCT
                        AllOrdersWithTop500Items.sales_doc_num
               FROM     AllOrdersWithTop500Items
                        INNER JOIN spv3SalesLineItemHistory SLIH ON AllOrdersWithTop500Items.sales_doc_num = SLIH.sales_doc_num
                        LEFT OUTER JOIN Top500SalesItems ProductsInTop500 ON ProductsInTop500.item_number = SLIH.item_number
               WHERE    ProductsInTop500.item_number IS NULL
             ) ,
        Top500ItemOnlyOrders
          AS ( SELECT   sales_doc_num
               FROM     AllOrdersWithTop500Items
               EXCEPT
               SELECT   sales_doc_num
               FROM     OrdersWithNonTop500Items
             )
    select  *
    from    ( select    sales_doc_num,
                        item_number,
                        item_description,
                        quantity
              from      spv3SalesLineItemHistory
              where     spv3SalesLineItemHistory.Item_Number in (
                        select  item_number
                        from    spvItemMaster
                        where   spvItemMaster.Item_Class_Code = 'jte-parts' )
            ) A
            left join ( select  sales_doc_num,
                                sales_doc_type,
                                doc_date
                        from    spv3SalesDocumentHistory
                      ) B on a.sales_doc_num = b.sales_doc_num
            left join ( select  item_number,
                                standard_cost
                        from    spvItemMaster
                      ) C on a.item_number = c.item_number
            INNER  JOIN Top500ItemOnlyOrders S ON S.item_number = A.item_number
    where   b.sales_doc_type = 'invoice'

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
When I executed I get the following error:

Msg 207, Level 16, State 1, Line 56
Invalid column name 'item_number'.

Chris LuttrellSenior Database Architect

Commented:
sorry, that last join was supposed to be on sales_doc_num now instead of item_number.  try this one.  it can be tough not having the full database to test a query against.
;WITH    Top500SalesItems
          AS ( Select TOP 500
                        SLIH.item_number
               FROM     spv3SalesDocumentHistory SDH
                        inner join spv3SalesLineItemHistory SLIH on SDH.sales_doc_num = SLIH.sales_doc_num
               WHERE    SDH.doc_date > DATEADD(YYYY, -1,
                                               '2004-07-31 00:00:00.000')
               Group By SLIH.item_number
               Having   COUNT(distinct month(SDH.doc_date)) >= 4
               ORDER BY SUM(SLIH.quantity) desc
             ) ,
        AllOrdersWithTop500Items
          AS ( SELECT DISTINCT
                        SDH.sales_doc_num
               FROM     spv3SalesDocumentHistory SDH
                        INNER JOIN spv3SalesLineItemHistory SLIH ON SDH.sales_doc_num = SLIH.sales_doc_num
                        INNER JOIN Top500SalesItems OrdersInTop500 ON OrdersInTop500.item_number = SLIH.item_number
               WHERE    SDH.doc_date > DATEADD(YYYY, -1,
                                               '2004-07-31 00:00:00.000')
             ) ,
        OrdersWithNonTop500Items
          AS ( SELECT DISTINCT
                        AllOrdersWithTop500Items.sales_doc_num
               FROM     AllOrdersWithTop500Items
                        INNER JOIN spv3SalesLineItemHistory SLIH ON AllOrdersWithTop500Items.sales_doc_num = SLIH.sales_doc_num
                        LEFT OUTER JOIN Top500SalesItems ProductsInTop500 ON ProductsInTop500.item_number = SLIH.item_number
               WHERE    ProductsInTop500.item_number IS NULL
             ) ,
        Top500ItemOnlyOrders
          AS ( SELECT   sales_doc_num
               FROM     AllOrdersWithTop500Items
               EXCEPT
               SELECT   sales_doc_num
               FROM     OrdersWithNonTop500Items
             )
    select  *
    from    ( select    sales_doc_num,
                        item_number,
                        item_description,
                        quantity
              from      spv3SalesLineItemHistory
              where     spv3SalesLineItemHistory.Item_Number in (
                        select  item_number
                        from    spvItemMaster
                        where   spvItemMaster.Item_Class_Code = 'jte-parts' )
            ) A
            left join ( select  sales_doc_num,
                                sales_doc_type,
                                doc_date
                        from    spv3SalesDocumentHistory
                      ) B on a.sales_doc_num = b.sales_doc_num
            left join ( select  item_number,
                                standard_cost
                        from    spvItemMaster
                      ) C on a.item_number = c.item_number
            INNER  JOIN Top500ItemOnlyOrders S ON S.sales_doc_num = A.sales_doc_num
    where   b.sales_doc_type = 'invoice'

Open in new window

Author

Commented:
Sorry for no reply yesterday.  I was out of town.  I will take a look at this, this morning and let you know how it works!
Chris LuttrellSenior Database Architect

Commented:
np, let me know if you run into problems.

Author

Commented:
Ok, so what I am looking at is the line items for all invoices where 100% of the invoiced line items were in the top 500 correct?  Therefore, I can take the original query and that query and see the number of invoices where 100% of the line items were in the top 500 and then the rest...Does that make sense?
Chris LuttrellSenior Database Architect

Commented:
First, I noticed one thing, everywhere I have "DATEADD(YYYY, -1, '2004-07-31 00:00:00.000')" change it to back to "DATEADD(YYYY,-1,GETDATE())" for your query.  I was running this against the AdventureWorks database for a test and the dates in there are old but I forgot to change that back to your date range.

I followed you up to "and then the rest...", not sure what you mean there, but the query I gave should satisfy the first part, All Line Items for All Invoices where All items are in the Top 500.

Breaking down what I am doing in the CTE.
1st CTE, Top500SalesItems, identifies the Top 500 Items based on sales in past year with sales in >= 4 months by quantity.
2nd CTE, AllOrdersWithTop500Items, identifies All Invoices that include a Top 500 item (may have non top 500 also).
3rd CTE, OrdersWithNonTop500Items, identifies any of the previous list of invoices that include items that are not in the top 500.
4th CTE, Top500ItemOnlyOrders, constructs the list of invoices that are in top 500 but does NOT have non-top 500 items.
Final Query then is your original query with the list of Top500ItemOnlyOrders as the ultimate filter in the last INNER JOIN.

Author

Commented:
So what then exactly are the results of the query showing?  I think that is where I am getting confused.  I appreciate the very detailed explanation of the query by the way!
Senior Database Architect
Commented:
the results should be showing all the items for all invoices in the past year that are made up of only your top 500 items.

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