Solved

Adding Additional Filter to A SQL Query

Posted on 2011-09-14
8
241 Views
Last Modified: 2012-05-12
I have a query that EE members helped me build, but I'm having difficulty adding to it.  I need to add a filter to this output beyond what's already there.  Specifically I need to filter Where Work_Center = SORT or NEST or TAP or SHIP or CUT.  If anyone could point me in the right direction I would appreciate it.

Kyle
SELECT     *
FROM         (SELECT     b.Job, c.Work_Date, d .Type, a.Customer, a.Part_Number, a.Description, a.Rev, a.Top_Lvl_Job, b.Work_Center, b.Operation_Service, 
                                              b.Description AS WC_Description, a.Profit_Pct, a.Commission_Pct, a.Unit_Price, a.Order_Quantity, b.Est_GA_Burden, b.Est_Machine_Burden, 
                                              b.Est_Labor_Burden, b.Est_Run_Labor, b.Est_Setup_Labor, b.Act_GA_Burden, b.Act_Machine_Burden, b.Act_Labor_Burden, b.Act_Run_Labor, 
                                              b.Act_Setup_Labor, a.Est_GA_Burden AS Total_Est_GA_Burden, a.Est_Machine_Burden AS Total_Est_Mach_Burden, 
                                              a.Est_Labor_Burden AS Total_Est_Labor_Burden, a.Est_Service AS Total_Est_Service, a.Est_Material AS Total_Est_Material, 
                                              a.Est_Labor AS Total_Est_Labor, a.Assembly_Level, a.Act_Material, a.Act_Service, c.Operation_Complete, row_number() OVER (partition BY b.Job, 
                                              b.Work_center, b.Description
                       ORDER BY c.Work_date) rn
FROM         dbo.Job a INNER JOIN
                      dbo.Job_Operation b ON a.Job = b.Job INNER JOIN
                      dbo.Job_Operation_Time c ON b.Job_Operation = c.Job_Operation INNER JOIN
                      dbo.Work_Center d ON b.Work_Center = d .Work_Center
WHERE     c.Operation_Complete = 1) t1 
WHERE     rn = 1

Open in new window

0
Comment
Question by:Kyle Witter
8 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 36536778
add this

Work_Center in ('SORT','NEST','TAP','SHIP','CUT')

into line 14

ie: Line 14:

WHERE     c.Operation_Complete = 1) t1
-->
WHERE Work_Center in ('SORT','NEST','TAP','SHIP','CUT') and c.Operation_Complete = 1) t1
0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 300 total points
ID: 36536780
SELECT     *
FROM         (SELECT     b.Job, c.Work_Date, d .Type, a.Customer, a.Part_Number, a.Description, a.Rev, a.Top_Lvl_Job, b.Work_Center, b.Operation_Service,
                                              b.Description AS WC_Description, a.Profit_Pct, a.Commission_Pct, a.Unit_Price, a.Order_Quantity, b.Est_GA_Burden, b.Est_Machine_Burden,
                                              b.Est_Labor_Burden, b.Est_Run_Labor, b.Est_Setup_Labor, b.Act_GA_Burden, b.Act_Machine_Burden, b.Act_Labor_Burden, b.Act_Run_Labor,
                                              b.Act_Setup_Labor, a.Est_GA_Burden AS Total_Est_GA_Burden, a.Est_Machine_Burden AS Total_Est_Mach_Burden,
                                              a.Est_Labor_Burden AS Total_Est_Labor_Burden, a.Est_Service AS Total_Est_Service, a.Est_Material AS Total_Est_Material,
                                              a.Est_Labor AS Total_Est_Labor, a.Assembly_Level, a.Act_Material, a.Act_Service, c.Operation_Complete, row_number() OVER (partition BY b.Job,
                                              b.Work_center, b.Description
                       ORDER BY c.Work_date) rn
FROM         dbo.Job a INNER JOIN
                      dbo.Job_Operation b ON a.Job = b.Job INNER JOIN
                      dbo.Job_Operation_Time c ON b.Job_Operation = c.Job_Operation INNER JOIN
                      dbo.Work_Center d ON b.Work_Center = d .Work_Center
WHERE     c.Operation_Complete = 1
                  and b.Work_Center in ('Sort', 'Nest', 'Tap', 'Ship', 'Cut')

) t1
WHERE     rn = 1
0
 
LVL 3

Expert Comment

by:anjos
ID: 36536832
Wouldn't it work if you added the following under WHERE:
AND (Work_Center = 'SORT'
            OR Work_Center = 'NEST '
            OR Work_Center = 'TAP '
            OR Work_Center = 'SHIP'
            OR Work_Center = 'CUT')
0
 
LVL 1

Author Comment

by:Kyle Witter
ID: 36536836
HainKurt,

Attached is the error your change outputs.  This is the problem I was running into myself.

Thanks for helping! Error
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 100 total points
ID: 36536853
Just update HainKurt's to use B.Work_Center.
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 100 total points
ID: 36536925
if you have same column name (Work_Center) in other tables, use alias

WHERE     c.Operation_Complete = 1) t1
-->
WHERE b.Work_Center in ('SORT','NEST','TAP','SHIP','CUT') and c.Operation_Complete = 1) t1
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36536941
saw your screen shot after submitting :) after checking select statement, you are getting it from alias b
so just add "b." in front of that column...
0
 
LVL 1

Author Closing Comment

by:Kyle Witter
ID: 36536966
Thank You!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this article I will describe the Backup & Restore 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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 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

19 Experts available now in Live!

Get 1:1 Help Now