HELP: Building SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

HELP: Building SQL Query

Author

Topic

cinewest
Starting Member

8 Posts

Posted - 2007-09-28 : 01:33:28

My situation is as follows:
I have two tables: InvoiceHeader and InvoiceItemList

InvoiceHeader provides me with InvoiceNumber
InvoiceItemList provides me with ProductID, Description and Qty

I am trying to run two different queries that will answer following questions:

1. How many products were sold were ProductID=X and ProductID=Y on a given invoice for a given date range? In other words, for the date range provided, how many ProductID=X were sold were ProductID=Y also existed?

2. How many products were sold were ProductID=X existed on an invoice but ProductID=Y did not exist.

Ultimately, I need to be able to ascertain how many times ProductID=X was sold when ProductID=Y was present and how many times it was sold without ProductID=Y.

I can do a simple query that shows me how many times X or Y were sold, but not how many times X was sold when Y was present vs when Y is not present.

Hope this makes sense. Thank you for your assistance.

cinewest
Starting Member

8 Posts

Posted - 2007-09-28 : 01:49:41

example of what I have and desired results:

[InvoiceHeader]
InvoiceID,InvoiceNo, ClientID
1, 1004, 20
2, 1007, 33
3, 1009, 55
4, 1011, 22
5, 1012, 23
6, 1014, 20
7, 1015, 33
8, 1016, 33

[InvoiceItemList]
InvoiceID, ProductID, Description, Qty
1, X, Widget, 1
1, Y, Widget, 1
1, Z, Widget, 1
2, X, Widget, 1
3, Y, Widget, 1
4, X, Widget, 1
4, Z, Widget, 1
5, X, Widget, 1
6, X, Widget, 1
6, Z, Widget, 1
7, Y, Widget, 1
8, X, Widget, 1
8, Y, Widget, 1

ideally, the results should be:

X sold with Y = 2
InvoiceID, ClientID, ItemID, Description, Qty
1004, 20, X, Widget, 1
1016, 33, X, Widget, 1

X sold without Y = 4
InvoiceID, ClientID, ItemID, Description, Qty
1007, 33, X, Widget, 1
1011, 22, X, Widget, 1
1012, 23, X, Widget, 1
1014, 20, X, Widget, 1

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-28 : 02:17:16

DECLARE @InvoiceHeader TABLE (InvoiceID int, InvoiceNo int, ClientID int)
INSERT INTO @InvoiceHeader SELECT 1, 1004, 20
UNION SELECT 2, 1007, 33
UNION SELECT 3, 1009, 55
UNION SELECT 4, 1011, 22
UNION SELECT 5, 1012, 23
UNION SELECT 6, 1014, 20
UNION SELECT 7, 1015, 33
UNION SELECT 8, 1016, 33

DECLARE @InvoiceItemList TABLE (InvoiceID int, ProductID char(1), Description char(6), Qty int)
INSERT INTO @InvoiceItemList SELECT 1, 'X', 'Widget', 1
UNION SELECT 1, 'Y', 'Widget', 1
UNION SELECT 1, 'Z', 'Widget', 1
UNION SELECT 2, 'X', 'Widget', 1
UNION SELECT 3, 'Y', 'Widget', 1
UNION SELECT 4, 'X', 'Widget', 1
UNION SELECT 4, 'Z', 'Widget', 1
UNION SELECT 5, 'X', 'Widget', 1
UNION SELECT 6, 'X', 'Widget', 1
UNION SELECT 6, 'Z', 'Widget', 1
UNION SELECT 7, 'Y', 'Widget', 1
UNION SELECT 8, 'X', 'Widget', 1
UNION SELECT 8, 'Y', 'Widget', 1

DECLARE @Target char(1), @TargetWith char(1), @TargetWithout char(1)

SELECT @Target = 'X', @TargetWith = 'Y', @TargetWithout = 'Y'

SELECT A.InvoiceNo AS InvoiceID, A.ClientID, B.ProductID AS ItemID, B.Description, B.Qty
FROM @InvoiceHeader A INNER JOIN @InvoiceItemList B ON B.InvoiceID = A.InvoiceID
WHERE B.ProductID = @Target AND A.InvoiceID IN (SELECT Z.InvoiceID FROM @InvoiceItemList Z WHERE Z.ProductID = @TargetWith)
ORDER BY A.InvoiceID, B.ProductID

SELECT A.InvoiceNo AS InvoiceID, A.ClientID, B.ProductID AS ItemID, B.Description, B.Qty
FROM @InvoiceHeader A INNER JOIN @InvoiceItemList B ON B.InvoiceID = A.InvoiceID
WHERE B.ProductID = @Target AND A.InvoiceID NOT IN (SELECT Z.InvoiceID FROM @InvoiceItemList Z WHERE Z.ProductID = @TargetWithout)
ORDER BY A.InvoiceID, B.ProductID

cinewest
Starting Member

8 Posts

Posted - 2007-09-28 : 03:33:47

Thank you Koji Matsumura - the solution worked like a charm!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources