Get two sales item code exclusively from an invoice

[donalexander]

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 it doesn't matter if I have other sales item code as well. I would appreciate the help. Thank you in advance.

 

[fredericofonseca]

please put the code you tried so far and more importantly how you identify a invoice and their respective sales item code.

then we may be able to help you

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

I try to make an assumption: You have 1:n related data like order/order items or invoice/sales items. You're looking for invoices with sales items 111 and 222, but then you don't understand the mechanics of the where clause: It's always about one row, so if you filter in(111,222) of course you get what you see, it's an OR condition, as in item code = 111 OR item code = 222. You want both conditions fulfilled, but in two records, there is no simple operator doing that, unless you join the sales items twice. You could do that and than have a condition on one and another sales item, but you would cause a lot of possible pairs and that doesn't scale well.

One common solution is to use two EXISTS conditions, there should exist a sales item with code 111 AND another sales item (of the same invoice number) with code 222, so you really say so:

Select * from invoices WHERE
Exists Select 1 From salesitems where invoiceno = invoices.no and code=111
AND
Exists Select 1 From salesitems where invoiceno = invoices.no and code=222

Another solution more generally working for any number of wanted combinations of items codes is going the other way around, first put the wanted item codes into a temp table and inner join all sales itmes with this list (thereby filtering on just the intersting item codes), then group by invoice number and count the filtered items per invoice, if the count matches the number of wanted items all the wanted items are among the sales items, so:

declare @salesitems as table (invoiceno int, code int);
declare @wantedcount int;

insert into @salesitems values (1,111),(1,222),(2,111),(3,222);

create table #wanteditems (code int);
insert into #wanteditems values (111),(222);

select @wantedcount = count(*) from #wanteditems;

Select invoiceno from 
(select invoiceno from @salesitems si inner join #wanteditems wi on si.code = wi.code) filtered
group by filtered.invoiceno
having count(*)=@wantedcount;

Invoice number 1 is put into the sales items with codes 111 and 222, invoice 2 only has 111, invoice 3 only has code 222, the end result just shows invoice 1, as needed.

Bye, Olaf.

 

[donalexander]

Thanks OlafDoschke looks like what I needed. Thanks again for your help I appreciate the tip.