?
Solved

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

Posted on 2016-10-25
2
Medium Priority
?
56 Views
Last Modified: 2016-10-25
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
Comment
Question by:printmedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 41859256
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
 

Author Closing Comment

by:printmedia
ID: 41859266
Worked great. Thanks!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question