Solved

Adding Additional Filter to A SQL Query

Posted on 2011-09-14
8
244 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:Huseyin KAHRAMAN
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN 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:Huseyin KAHRAMAN
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

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 article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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