COUNT DISTINCT WITHIN WINDOW

Question

Hi All,

I am trying to get a distinct count within a window. For example:

use tempdb;

IF OBJECT_ID('T1', 'U') IS NOT NULL
DROP TABLE T1;

CREATE TABLE T1 (
	ShipperID int primary key,
	OrderID int,
	PickID int
	);

INSERT INTO T1 
VALUES (47243, 703, 10653), (67268, 703, 40673), (6729,  703, 40697)

/*
ShipperID	OrderID	PickID
47243		703		10653
67268		703		40673
67269		703		40697
*/


INSERT INTO T1 
VALUES (67274, 30743, 40709), (67275, 30743, 40709), (67276, 30743, 40709)

/*
ShipperID	OrderID	PickID
67274		30743	40709
67275		30743	40709
67276		30743	40709
*/

SELECT ShipperID,
OrderID,
PickID,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY PickID) AS BackOrderCount, 
ROW_NUMBER() OVER (PARTITION BY  OrderID, PickID ORDER BY ShipperID) AS SplitShipCount
FROM 
T1 

I want my BackOrderCount for OrderID 30743 rows to be 1 i.e. I only want my BackOrderCount to increment for each unique PickID within an OrderID.

Any assistance much appreciated.

Thanks.

Clay

Answer 1

Try below

;with cte as
(
SELECT ShipperID,OrderID,
PickID,
ROW_NUMBER() OVER (PARTITION BY ShipperID ORDER BY OrderID) AS BackOrderCount,
ROW_NUMBER() OVER (PARTITION BY  OrderId,PickID ORDER BY ShipperID) AS SplitShipCount
FROM 
T1 
)
select * from cte where BackOrderCount = 1 and SplitShipCount =1 

if this output is not correct then please provide your desired output results.

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Thanks for the reply Eshani - perhaps I didn't fully explain myself.

The BackOrderCount should reset for each new OrderID and increment for each PickID within an OrderID

In my example query - I am trying to use row_number() which increments for each new row - a new row is created for a new PickID or ShipperID.

 

SELECT ShipperID,
OrderID,
PickID,
ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY PickID) AS BackOrderCount, 
ROW_NUMBER() OVER (PARTITION BY  OrderID, PickID ORDER BY ShipperID) AS SplitShipCount
FROM 
T1 

ShipperID	OrderID	PickID	BackOrderCount	SplitShipCount
47243		703		10653		1					1
67268		703		40673		2					1
6729		703		40697		3					1
67274		30743		40709		1					1
67275		30743		40709		2					2
67276		30743		40709		3					3

Each PickID against an OrderID is considered a 'BackOrder'.

Each ShipperID against an OrderID, PickID combo is considered a 'SplitShip'.

Thanks for your help.

  

Answer 3

BTW - my solution needs to run on SQL2008.

Thanks.

Answer 4

Maybe this will help...

Answer 5

I've got a solution - not sure it's a great one!!!

;WITH OrdersNPicks AS (
	SELECT DISTINCT OrderID, 
				PickID 
	FROM T1),
BackOrderCounts AS (
	SELECT OrderID,
		PickID, 
		ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY PickID) AS BackOrderNumber
	FROM 
	OrdersNPicks)
SELECT t.ShipperID,
t.OrderID,
t.PickID,
c.BackOrderNumber,
ROW_NUMBER() OVER (PARTITION BY  t.OrderID, t.PickID ORDER BY t.ShipperID) AS SplitShipCount
FROM 
T1 t
JOIN BackOrderCounts c ON c.OrderID = t.OrderID AND c.PickID = t.PickID

In case there is a better way -- I will leave this open for a short while...

Answer 6

Hi Clay,

The simple answer is DENSE_RANK function.

SELECT ShipperID,
OrderID,
PickID,
DENSE_RANK() OVER (PARTITION BY OrderID ORDER BY OrderID,PickID) AS BackOrderCount,
ROW_NUMBER() OVER (PARTITION BY  OrderID, PickID ORDER BY ShipperID) AS SplitShipCount
FROM
T1

To know detail about Dense Rank refer http://technet.microsoft.com/en-us/library/ms173825.aspx

---

Regards, RSingh

Answer 7

Thanks RSingh,

Thought I was missing something simple.

Much appreciated.