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
38 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 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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 …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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