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
17 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
2 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now