Solved

Adding Additional Filter to A SQL Query

Posted on 2011-09-14
8
240 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

707 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

18 Experts available now in Live!

Get 1:1 Help Now