Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2013
    Posts
    30

    Unanswered: Set two sales item codes 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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    ; WITH s1 AS (
    SELECT *
       FROM (VALUES
          (1, 111), (2, 222), (3, 111), (3, 222)
    ,     (4, 444), (5, 444), (5, 111), (6, 444)
    ,     (6, 222), (7, 444), (7, 222), (7, 111)
          ) AS z (InvoiceNumber, SalesItemCode)
    ), s2 AS (
    SELECT InvoiceNumber
    ,  Sum(CASE WHEN 111 = SalesItemCode THEN 1 ELSE 0 END) AS c111
    ,  Sum(CASE WHEN 222 = SalesItemCode THEN 1 ELSE 0 END) AS c222
    ,  Count(*) AS c
       FROM s1
       GROUP BY InvoiceNumber
    )
    SELECT InvoiceNumber
       FROM s2
       WHERE  0 < c111
          AND 0 < c222
          AND c = c111 + c222  -- Remove this line if InvoiceNumber 7 should also be included
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2011
    Posts
    3
    Code:
    select t1.* 
    from sales t1
    inner join(
    	select invoiceno
    	from sales a inner join sales b on a.invoiceno = b.invoiceno and a.itemcode = 111 and b.itemcode = 222
    )t2 t1.invoiceno = t2.invoiceno

  4. #4
    Join Date
    Mar 2013
    Posts
    30
    Thanks Pat and thanks sandeepmittal11 I appreciate the tips. I really needed help on this. Thanks again.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I always prefer the EXISTS subselect in these type cases.

    Code:
    select t1.* 
    from sales t1
    where exists (
    	select 1 from sales a inner join sales b on a.invoiceno = b.invoiceno and a.itemcode = 111 and b.itemcode = 222
    )
    Dave

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Dave: I think that you'll get every row from my test set using your SQL statement. While I'm not certain which of the two possible answers I proposed is what the OP wants, I'm certain that all rows is not what they want.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I just altered the prior example to an exists, did no checking of if that worked or not. And after quick look at it just now, seems there is a slight change required, you do get what you pay for here.

    Code:
    select t1.* 
    from sales t1
    where exists (select 1 from sales a where a.invoiceno = t1.invoiceno and a.itemcode = 111)
      and t1.itemcode = 222
    Dave

  8. #8
    Join Date
    Mar 2013
    Posts
    30
    Thanks Dave I appreciate the tip!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •