Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Adding Additional Filter to A SQL Query

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
Kyle Witter
Asked:
Kyle Witter
3 Solutions
 
HainKurtSr. System AnalystCommented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
anjosCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kyle WitterAuthor Commented:
HainKurt,

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

Thanks for helping! Error
0
 
tim_csCommented:
Just update HainKurt's to use B.Work_Center.
0
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
Kyle WitterAuthor Commented:
Thank You!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now