August 1, 2012 at 8:49 am
So I am struggling to even get started on this issue. Basically I have a sales table laid out like this
•Sales_saleID
•Sales_customerID
•Sales_status
A customer can have multiple sales orders at any one point in time however I need to produce a list showing my customers who only have pending orders if they have a completed order I don’t want to see that customer in the list at all. I then only want to see the customer once so I guess a group by clause. Any help with this would be much appreciated.
August 1, 2012 at 9:10 am
How about this:
IF OBJECT_ID('dbo.Sales1') IS NOT NULL
DROP TABLE dbo.Sales1;
CREATE TABLE dbo.sales1
(
Sales_saleID int,
Sales_customerID int,
Sales_status varchar(10)
);
INSERT INTO dbo.sales1 VALUES ( 1, 1, 'Pending' );
INSERT INTO dbo.sales1 VALUES ( 2, 1, 'Completed' );
INSERT INTO dbo.sales1 VALUES ( 3, 2, 'Completed' );
INSERT INTO dbo.sales1 VALUES ( 4, 2, 'Pending' );
INSERT INTO dbo.sales1 VALUES ( 5, 3, 'Pending' );
INSERT INTO dbo.sales1 VALUES ( 6, 3, 'Pending' );
WITH Completed AS
(
SELECT DISTINCT Sales_customerID FROM dbo.sales1 WHERE Sales_status = 'Completed'
),
Pending AS
(
SELECT DISTINCT Sales_customerID FROM dbo.sales1 WHERE Sales_status = 'Pending'
)
SELECT Sales_customerID FROM Pending
WHERE Sales_customerID NOT IN (SELECT Sales_customerID FROM Completed)
August 1, 2012 at 9:29 am
laurie-789651 (8/1/2012)
How about this:
IF OBJECT_ID('dbo.Sales1') IS NOT NULL
DROP TABLE dbo.Sales1;
CREATE TABLE dbo.sales1
(
Sales_saleID int,
Sales_customerID int,
Sales_status varchar(10)
);
INSERT INTO dbo.sales1 VALUES ( 1, 1, 'Pending' );
INSERT INTO dbo.sales1 VALUES ( 2, 1, 'Completed' );
INSERT INTO dbo.sales1 VALUES ( 3, 2, 'Completed' );
INSERT INTO dbo.sales1 VALUES ( 4, 2, 'Pending' );
INSERT INTO dbo.sales1 VALUES ( 5, 3, 'Pending' );
INSERT INTO dbo.sales1 VALUES ( 6, 3, 'Pending' );
WITH Completed AS
(
SELECT DISTINCT Sales_customerID FROM dbo.sales1 WHERE Sales_status = 'Completed'
),
Pending AS
(
SELECT DISTINCT Sales_customerID FROM dbo.sales1 WHERE Sales_status = 'Pending'
)
SELECT Sales_customerID FROM Pending
WHERE Sales_customerID NOT IN (SELECT Sales_customerID FROM Completed)
Hey Laurie!
Good idea, but I wonder what the effect of hitting the table twice would be on a big system?
Whereas this only hits the table once: -
SELECT Sales_customerID
FROM (SELECT Sales_customerID,
SUM(CASE WHEN Sales_status = 'Completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN Sales_status = 'Pending' THEN 1 ELSE 0 END) AS pending
FROM dbo.sales1
GROUP BY Sales_customerID) a
WHERE completed = 0 AND pending > 0;
August 1, 2012 at 9:31 am
A GROUP BY with CASE expressions is likely to perform better, because it requires fewer scans and logical reads of the base table.
SELECT Sales_CustomerID
FROM @sales1
GROUP BY Sales_CustomerID
HAVING COUNT(CASE WHEN Sales_Status = 'Pending' THEN 1 END ) > 0
AND COUNT(CASE WHEN Sales_Status = 'Completed' THEN 1 END ) = 0
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 1, 2012 at 10:15 am
Thats great thanks, it seems to work best using Cadavre's version
but now where would i stick my join in to display the company name, as soon as i try and join up the company table it no longer works
August 1, 2012 at 10:46 am
Hard to trouble shoot when we can't see what you see.
Care to post the code and any other info that may be important, like error messages?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply