December 27, 2015 at 12:35 pm
Hello,
I am using 2008R2 SQL Server I am trying to get sales data with two particular sales item code (111, 222) what I am looking for if an invoice having both these sales item code exclusively with the invoice number (invno) Unfortunately I get invoices with 111 sales item code only or separately or with item code with 222 sales as well. I have tried using In(111,222), but that doesn't work. I just want to see sales only with sales item code 111 and 222. I would appreciate the help. Thank you in advance.
December 27, 2015 at 1:40 pm
To receive an accurate and quick answer, please offer us a script that creates tables, inserts sample data, and shows the SQL attempted. Also offers us the expected results. Use the links on the left of the edit frame to set your code format to SQL.
As a wild guess, consider https://support.microsoft.com/en-us/kb/176480.
December 27, 2015 at 11:16 pm
Another wild guess
😎
USE tempdb;
GO
IF OBJECT_ID(N'dbo.TBL_SALES_ITEMS') IS NOT NULL DROP TABLE dbo.TBL_SALES_ITEMS;
CREATE TABLE dbo.TBL_SALES_ITEMS
(
SI_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_SALES_ITEMS_SI_ID PRIMARY KEY CLUSTERED
,invno INT NOT NULL
,SIC CHAR(3) NOT NULL
);
INSERT INTO dbo.TBL_SALES_ITEMS(invno, SIC)
VALUES
( 1,'111')
,( 2,'112')
,( 3,'131')
,( 1,'114')
,( 2,'111')
,( 3,'311')
,( 1,'222')
,( 2,'242')
,( 3,'222')
,( 1,'232');
SELECT
SI.invno
FROM dbo.TBL_SALES_ITEMS SI
WHERE SI.SIC IN ('111','222')
GROUP BY SI.invno
HAVING COUNT(SI.SI_ID) = 2;
December 28, 2015 at 1:12 am
Eirikur,
Great! and Thank You I believe this is what I am looking for.
December 28, 2015 at 2:13 am
It works as long an item occurs only once in an invoice. For me a self join is a better solution:
SELECT distinct
SI_a.invno
FROM
dbo.TBL_SALES_ITEMS SI_a,
dbo.TBL_SALES_ITEMS SI_b
WHERE
SI_a.invno=SI_b.invno and
SI_a.SIC='111' and
SI_b.SIC='222';
December 28, 2015 at 2:25 am
Palotaiarpad thanks I will give that a try. I appreciate your help. Thanks again.
December 28, 2015 at 8:55 am
I read the original request as requiring the invoice to have both items 111 and 222, and ONLY items 111 and 222. Is that correct?
Either way, the approach using GROUP BY and HAVING like Eirikur showed can be modified to handle the scenario with an item appearing multiple times on an invoice, and will be able to do this in one pass through the data (as opposed to a self join, which could end up being MUCH worse than that).
Something like this:
SELECT invno FROM TBL_SALES_ITEMS
GROUP BY invno
HAVING SUM(CASE WHEN SIC='111' THEN 1 ELSE 0 END)>0
AND SUM(CASE WHEN SIC='222' THEN 1 ELSE 0 END)>0
-- AND SUM(CASE WHEN SIC NOT IN ('111','222') THEN 1 ELSE 0 END)=0 --Uncomment this line if the requirement is to have ONLY codes 111 and 222;
Cheers!
December 28, 2015 at 9:31 am
A small correction from Eirikur's code and two additional options.
SELECT
SI.invno
FROM dbo.TBL_SALES_ITEMS SI
WHERE SI.SIC IN ('111','222')
GROUP BY SI.invno
HAVING COUNT( DISTINCT SI.SIC) = 2; --Added distinct and changed the column.
SELECT
SI.invno
FROM dbo.TBL_SALES_ITEMS SI
WHERE SI.SIC IN ('111','222')
GROUP BY SI.invno
HAVING MAX( SI.SIC) = '222'
AND MIN( SI.SIC) = '111';
SELECT
SI.invno
FROM dbo.TBL_SALES_ITEMS SI
WHERE SI.SIC = '111'
INTERSECT
SELECT
SI.invno
FROM dbo.TBL_SALES_ITEMS SI
WHERE SI.SIC = '222';
December 28, 2015 at 12:17 pm
Great! Thanks Luis I appreciate the tip.
December 28, 2015 at 12:18 pm
Great! Thanks Jacob I appreciate the tip.
December 28, 2015 at 12:18 pm
I wanted to thank you all who gave me tips with little information I provided.
December 28, 2015 at 1:15 pm
Luis Cazares (12/28/2015)
A small correction from Eirikur's code and two additional options.
SELECT
SI.invno
FROM dbo.TBL_SALES_ITEMS SI
WHERE SI.SIC IN ('111','222')
GROUP BY SI.invno
HAVING COUNT( DISTINCT SI.SIC) = 2; --Added distinct and changed the column.
SELECT
SI.invno
FROM dbo.TBL_SALES_ITEMS SI
WHERE SI.SIC IN ('111','222')
GROUP BY SI.invno
HAVING MAX( SI.SIC) = '222'
AND MIN( SI.SIC) = '111';
SELECT
SI.invno
FROM dbo.TBL_SALES_ITEMS SI
WHERE SI.SIC = '111'
INTERSECT
SELECT
SI.invno
FROM dbo.TBL_SALES_ITEMS SI
WHERE SI.SIC = '222';
Thanks for the correction Luis, did cross my mind but then again this was a wild guess:-P
😎
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply