• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

Display records where count is greater than 3 all in one query instead of multiple views SQL Server

Hi all.

Currently I have 2 views that tell me if an invoice whose Status = 2 has more than 3 purchase orders assigned to it. I'd like to have one query do all of this instead of 2 views.

Table1: InvoiceNumber, Status
Table2: InvoiceNumber, PurchaseOrderNumber, ItemNumber

Sample data:
Table1 =
9099, 2
9098, 2
9097, 1

Table2=
9099, PO1, 787
9099, PO1, 654
9099, PO2, 124
9099, PO3, 445
9099, PO4, 457
9098, PO5, 789
9098, PO6, 111
9097, PO1, 455

So the query should display the following result: InvoiceNumber = 9099

I would love to be able to display the invoice number and the purchase orders:
9099, PO1
9099, PO2
9099, PO3
9099, PO4

But if that's too complicated or would still require multiple views then I'd be ok with just the invoice number.

Any ideas?

Thank you in advance.
0
printmedia
Asked:
printmedia
1 Solution
 
ZberteocCommented:
Here:
-- create invoice table
if object_id('tempdb..#invoice') is not null
	drop table #invoice
create table #invoice (InvoiceNumber int, Status int)
insert into #invoice 
select 
	*
from
	(values 
		(9099, 2),
		(9098, 2),
		(9097, 1)
	) q(InvoiceNumber,Status)

-- create order table
if object_id('tempdb..#order') is not null
	drop table #order
create table #order (InvoiceNumber int, PurchaseOrderNumber varchar(10), ItemNumber int)
insert into #order
select 
	*
from
	(values 
		(9099, 'PO1', 787),
		(9099, 'PO1', 654),
		(9099, 'PO2', 124),
		(9099, 'PO3', 445),
		(9099, 'PO4', 457),
		(9098, 'PO5', 789),
		(9098, 'PO6', 111),
		(9097, 'PO1', 455)
	) q(InvoiceNumber, PurchaseOrderNumber, ItemNumber)

-- check tables content
select * from #invoice
select * from #order

-- query to return those invoices that have 3 or more distinct orders
select distinct
	InvoiceNumber, 
	PurchaseOrderNumber
from 
	#order i
where
	(select count(distinct PurchaseOrderNumber) from #order where InvoiceNumber=i.InvoiceNumber)>=3

Open in new window

0
 
printmediaAuthor Commented:
Worked great. Thanks!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now