Solved

Limit SQL Output in Excel

Posted on 2011-03-10
3
232 Views
Last Modified: 2012-05-11
I've got a Microsoft Query set up between a sql database and excel 2010 that I'm trying to filter out some information.  Attached is a copy of the report in its current state.  You'll see that the attached customer has two jobs in my system, all of which have multiple steps (Work Centers).  What I'd like to obtain is limiting the output of this script so that it only shows the first Work Center, which varies depending on the job.  Is this possible?  Attached is my SQL Code generated by Excel.  I tried using LIMIT 1 on the function at the end, but it tells me the code has errors when I apply it. "Job.Job" is the field that contains the job number, which is how I would like to limit it.  

Thank You for your help!

example.pdf
SELECT Job.Job, Job.Order_Date, Job.Customer, Job.Customer_PO, Job.Part_Number, Job.Customer_PO_LN, Job.Order_Quantity, Job.Status, Job.Sched_Start, Job.Sched_End, Job_Operation.Work_Center, Job_Operation.Status
FROM PRODUCTION.dbo.Job Job, PRODUCTION.dbo.Job_Operation Job_Operation
WHERE Job.Job = Job_Operation.Job AND ((Job.Status='Active') AND (Job_Operation.Status<>'C'))
ORDER BY Job.Customer

Open in new window

0
Comment
Question by:Kyle Witter
  • 2
3 Comments
 
LVL 7

Expert Comment

by:andymacf
ID: 35095398
Would the first work centre be the most recent one, if so, you could set your query to look for 'max' value on the relevant field and this will always pull in the first event.
0
 
LVL 1

Author Comment

by:Kyle Witter
ID: 35095440
The first work center is the location that the job is currently at in production.  This is the line that I would like to keep.  I guess I don't understand exactly how to set a max value to limit the output, though.

Do you have an example?  Thank you!
0
 
LVL 7

Accepted Solution

by:
andymacf earned 500 total points
ID: 35096818
Difficult to give you an example right away as I am away from my pc, but try this. In your SQL, try wrapping the value 'max(your value)… Select max(value) from
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

914 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

14 Experts available now in Live!

Get 1:1 Help Now