where clause help

Question

Post reply

where clause help

kat35601

SSC Eights!

Points: 825
More actions
October 28, 2015 at 9:11 am

#321732

in my where clause I want to include items where
"redtag='Y"
omit items like
"OrbeData_KithKitchens.dbo.WIP_master.STATUS not IN ('1','25','50')"
I also want to omit items that have a status of 6 with a lastupdate less than 3 days ago.
"(OrbeData_KithKitchens.dbo.WIP_master.STATUS = 6 AND OrbeData_KithKitchens.dbo.WIP_master.lastupdate >= getdate() - 3 )"
I also want to include items with
"(AND m1_DC.dbo.SalesOrders.uompschedulenumber LIKE 'S%' AND m1_DC.dbo.SalesOrders.ompclosed <> - 1)"
my problem is with the "(OrbeData_KithKitchens.dbo.WIP_master.STATUS = 6 AND OrbeData_KithKitchens.dbo.WIP_master.lastupdate >= getdate() - 3 )"
however I try it I either get items with a status of 6 only or I get to many items. Below is what I have landed on last but it's still wrong.
SELECT *
FROM WIP_master
LEFT JOIN m1_DC.dbo.SalesOrders ON m1_DC.dbo.SalesOrders.ompSalesOrderID = OrbeData_KithKitchens.dbo.WIP_master.ompSalesOrderID
LEFT JOIN m1_DC.dbo.UTRUCK ON m1_DC.dbo.UTRUCK.UTRTRUCKNUMBER = m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER
WHERE (redtag='Y'or m1_DC.dbo.SalesOrders.uompschedulenumber LIKE 'S%')
and m1_DC.dbo.SalesOrders.ompclosed <> - 1
and OrbeData_KithKitchens.dbo.WIP_master.STATUS NOT IN ('1','25','50')
AND redtagclosed != 'Y'
and (OrbeData_KithKitchens.dbo.WIP_master.STATUS != 6 AND OrbeData_KithKitchens.dbo.WIP_master.lastupdate <= getdate() - 3)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- is wm.lastupdate a datetime?

SELECT *

FROM WIP_master wm

LEFT JOIN m1_DC.dbo.SalesOrders so

ON so.ompSalesOrderID = wm.ompSalesOrderID

LEFT JOIN m1_DC.dbo.UTRUCK t

ON t.UTRTRUCKNUMBER = so.UOMPTRUCKNUMBER

WHERE (redtag='Y' OR so.uompschedulenumber LIKE 'S%')

AND so.ompclosed <> - 1

AND wm.[STATUS] NOT IN ('1' ,'25','50')

AND redtagclosed <> 'Y'

AND (wm.[STATUS] <> 6 AND wm.lastupdate <= CAST(GETDATE() - 3 AS DATE))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

kat35601 (10/28/2015)
in my where clause I want to include items where
"redtag='Y"
omit items like
"OrbeData_KithKitchens.dbo.WIP_master.STATUS not IN ('1','25','50')"
I also want to omit items that have a status of 6 with a lastupdate less than 3 days ago.
"(OrbeData_KithKitchens.dbo.WIP_master.STATUS = 6 AND OrbeData_KithKitchens.dbo.WIP_master.lastupdate >= getdate() - 3 )"
I also want to include items with
"(AND m1_DC.dbo.SalesOrders.uompschedulenumber LIKE 'S%' AND m1_DC.dbo.SalesOrders.ompclosed <> - 1)"
my problem is with the "(OrbeData_KithKitchens.dbo.WIP_master.STATUS = 6 AND OrbeData_KithKitchens.dbo.WIP_master.lastupdate >= getdate() - 3 )"
however I try it I either get items with a status of 6 only or I get to many items. Below is what I have landed on last but it's still wrong.
SELECT *
FROM WIP_master
LEFT JOIN m1_DC.dbo.SalesOrders ON m1_DC.dbo.SalesOrders.ompSalesOrderID = OrbeData_KithKitchens.dbo.WIP_master.ompSalesOrderID
LEFT JOIN m1_DC.dbo.UTRUCK ON m1_DC.dbo.UTRUCK.UTRTRUCKNUMBER = m1_DC.dbo.SalesOrders.UOMPTRUCKNUMBER
WHERE (redtag='Y'or m1_DC.dbo.SalesOrders.uompschedulenumber LIKE 'S%')
and m1_DC.dbo.SalesOrders.ompclosed <> - 1
and OrbeData_KithKitchens.dbo.WIP_master.STATUS NOT IN ('1','25','50')
AND redtagclosed != 'Y'
and (OrbeData_KithKitchens.dbo.WIP_master.STATUS != 6 AND OrbeData_KithKitchens.dbo.WIP_master.lastupdate <= getdate() - 3)

There are a number of challenges here for sure.

First is using select *. You should only select the columns you actually need.

You are referencing columns in another server but your 4 part naming convention is incorrect unless your remote database is named dbo and the schema is WIP_master. Then of course even if the naming was correct you should be using aliases and referencing columns with 4 part naming has been deprecated. Now add to that the logic that OrbeData_KithKitchens.dbo.WIP_master isn't even in your query (unless that is the same table as WIP_master).

Then you have inequality conditions in you where clause. This is nonSARGable and prevents index seeks. You also are mixing the syntax for inequality within the same query. Yes != and <> can either be used but within a single query you should strive to be consistent.

Then you have date math. You should use DATEADD instead of relying on the default behavior. It makes your code more robust and less ambiguous. As posted this query just isn't even close to actually even running.

Here is where the real fun begins. You haven't provided any actual information about what the problem is. We don't know what you are trying to do. In order for that to happen we would need to know what the table structures are like and have some sample data to work with. Here is an excellent article that talks about the kinds of things you need to post for people to be able to help. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Sean Lange (10/28/2015)
kat35601 (10/28/2015)
...
....
Then you have inequality conditions in you where clause. This is nonSARGable and prevents index seeks.
...

Not always:

CREATE TABLE #MyTable (ID INT IDENTITY(1,1) NOT NULL, Inequality CHAR(1))

INSERT INTO #MyTable (Inequality)

SELECT CASE WHEN bool = 0 THEN 'N' ELSE 'Y' END

FROM (SELECT bool = ABS(CHECKSUM(NEWID()))%2 FROM sys.COLUMNS) d

CREATE UNIQUE CLUSTERED INDEX ucx_ID ON #MyTable (ID)

CREATE NONCLUSTERED INDEX ix_Inequality ON #MyTable (Inequality)

SELECT * FROM #MyTable WHERE Inequality <> 'N'

-- Index Seek (NonClustered) [#MyTable].[ix_Inequality] Cost: 100 %

-- Actual Number of Rows 3494

-- Estimated Number of Rows 3494

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

ChrisM@Work (10/28/2015)
Sean Lange (10/28/2015)
kat35601 (10/28/2015)
...
....
Then you have inequality conditions in you where clause. This is nonSARGable and prevents index seeks.
...
Not always:
CREATE TABLE #MyTable (ID INT IDENTITY(1,1) NOT NULL, Inequality CHAR(1))
INSERT INTO #MyTable (Inequality)
SELECT CASE WHEN bool = 0 THEN 'N' ELSE 'Y' END
FROM (SELECT bool = ABS(CHECKSUM(NEWID()))%2 FROM sys.COLUMNS) d
CREATE UNIQUE CLUSTERED INDEX ucx_ID ON #MyTable (ID)
CREATE NONCLUSTERED INDEX ix_Inequality ON #MyTable (Inequality)
SELECT * FROM #MyTable WHERE Inequality <> 'N'
-- Index Seek (NonClustered) [#MyTable].[ix_Inequality] Cost: 100 %
-- Actual Number of Rows 3494
-- Estimated Number of Rows 3494

Sure there are some edge cases but generally speaking....

Add to that the trainwreck all over the rest of the query and I would be surprised if it was sargable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kat35601 SSC Eights! Points: 825 More actions · Answer 5

Yes lastupdate is date time. Wow thanks for all the comments I used * in the select here because I did not want to copy and past the 28 columns as they have nothing to do with my problem.

My problem is with (OrbeData_KithKitchens.dbo.WIP_master.STATUS = 6 AND OrbeData_KithKitchens.dbo.WIP_master.lastupdate >= getdate() - 3) I want to omit from my results items that meet this

but where I fail is I get only items with a status of 6 which the only items I want this to effect is those with a status of 6 and of those items with a status of 6 then I only want to see the one from 3 days ago foward. any other status other than these NOT IN ('1' ,'25','50') should show up. Thanks for the help

drew.allen SSC Guru Points: 76985 More actions · Answer 6

kat35601 (10/28/2015)
Yes lastupdate is date time. Wow thanks for all the comments I used * in the select here because I did not want to copy and past the 28 columns as they have nothing to do with my problem.
My problem is with (OrbeData_KithKitchens.dbo.WIP_master.STATUS = 6 AND OrbeData_KithKitchens.dbo.WIP_master.lastupdate >= getdate() - 3) I want to omit from my results items that meet this
but where I fail is I get only items with a status of 6 which the only items I want this to effect is those with a status of 6 and of those items with a status of 6 then I only want to see the one from 3 days ago foward. any other status other than these NOT IN ('1' ,'25','50') should show up. Thanks for the help

Try

NOT (OrbeData_KithKitchens.dbo.WIP_master.STATUS = 6 AND OrbeData_KithKitchens.dbo.WIP_master.lastupdate >= getdate() - 3)

NOT can be used as a unary operator to negate the following expression, and sometimes this is the easiest way to code those criteria.

This is also equivalent to

(OrbeData_KithKitchens.dbo.WIP_master.STATUS <> 6 OR OrbeData_KithKitchens.dbo.WIP_master.lastupdate < getdate() - 3)

You made two mistakes when trying to distribute the NOT over your clause. The negative of >= is < not <=, and you need to change the operator from an AND to an OR.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA