Relational division — some solutions


Here is an example question:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/26868ec7-54ad-42e9-9301-01b1337eb27a/ids-with-3-statuses?forum=transactsql


 DECLARE @tab TABLE
 (
 ID VARCHAR(3) ,
 OrderStatus VARCHAR(50)
 );

INSERT INTO @tab
VALUES ( 'ID1', 'Purchase Request' ),
 ( 'ID1', 'Pending' ),
 ( 'ID1', 'Processed' ),
 ( 'ID1', 'Processed' ),
 ( 'ID2', 'Pending' ),
 ( 'ID2', 'Pending' ),
 ( 'ID2', 'Pending' ),
 ( 'ID4', 'Purchase Request' ),
 ( 'ID3', 'Processed' );

;with mycte as (
SELECT ID, OrderStatus=CASE WHEN OrderStatus='Pending' THEN 1
WHEN OrderStatus='Processed' THEN 2
WHEN OrderStatus='Purchase Request' THEN 4
END
FROM @tab
GROUP By ID, OrderStatus)

SELECT ID from mycte
GROUP By ID
Having(SUM(OrderStatus)=7)

 
 

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s