We have a table of orders that can have multiple lines per order. All lines on an order have to have the same Product Type when submitted. So, so therefore if an order that has multiple lines has differing Product Types, it is split into two or more orders.

I am using Microsoft SQL Server Mgmt Studio for querying the table. I am no expert in SQL and am having trouble coming up with a query to show the count for the number of orders that were split.

Here is an example of the way our orders are stored in our table(we'll call it dbo.OrderTable). We use Purchase_Order_Number and Account_Number as a super key.

The SQL should return 2 since there are 2 orders that have different ProductTypes. Any help on this would be greatly appreciated.

CodePudding user response：

select count(*) from (
    select PurchaseOrderNumber from dbo.OrderTable
    group by PurchaseOrderNumber
    having count(distinct ProductType) > 1
) t

CodePudding user response：

I think something like the following should do the trick:

SELECT COUNT(distinct t1.PurchaseOrderNumber)
FROM yourtable t1
   INNER JOIN yourtable t2
       ON t1.PurchaseOrderNumber = t2.PurchaseOrderNumber
       AND t1.ProductType <> t2.ProductType

This is just self joining on the table for the same order, but different product types and then counting the number of distinct PurchaseOrderNumbers that survive the join criteria.

CodePudding user response：

The ROW_NUMBER function should provide an overview of the number of orders required for each line

Please consider the following sample code

create table table1 (orderId nvarchar(450), orderType nchar(1))
insert table1 values 
  ('217344',   'H')
, ('217344',   'C')
, ('BD624919', 'H')
, ('BD624920', 'H')
, ('BD624920', 'H')
, ('1269407',  'H')
, ('1269407',  'C')
, ('1269407',  'R')
select orderId, orderType, row_number() over (partition by orderId order by orderType) numberOfOrders  from table1

Result