Get two sales item code exclusively from invoice

  • 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.

  • 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.

  • 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;

  • Eirikur,

    Great! and Thank You I believe this is what I am looking for.

  • 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';

  • Palotaiarpad thanks I will give that a try. I appreciate your help. Thanks again.

  • 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!

  • 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';

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Great! Thanks Luis I appreciate the tip.

  • Great! Thanks Jacob I appreciate the tip.

  • I wanted to thank you all who gave me tips with little information I provided.

  • 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