Solved

Adding Additional Filter to A SQL Query

Posted on 2011-09-14
8
246 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
[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
8 Comments
 
LVL 56

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

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 56

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 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