How to you perform multiple queries after the where clause

N2IT

Instead of running several and statements do you use other statements that perform more efficiently? Right now in one of my queries I have a query with a select statement with alias and several inner joins, however at the where clause all I can come up with product = 'string' and id = 123456 and startdate >= '01/01/2013 and enddate <= '12/31/2013'; Do you think a sub query with (select max would be a better way to go? Any ideas? Thanks!

NotHackingYou

Personally I go with multiple ANDs if I need to meet several criteria. For your query I would probably do WHERE product='string' AND id=123456 AND startDate BETWEEN startingDate AND endingDate (unless you have indexes on startdate and enddate). The reason I would try to use the BETWEEN is that I like the syntax better and then you are only hitting one column instead of startDate and endDate. Of course, this will have to work in your situation and you may or may not be able to relyon startDate alone.


A subquery would be appropriate if you needed to find the greatest ID#, for exmaple WHERE product='string' AND id= (SELECT MAX(id) FROM idTable) AND (Dates..) or the newest order WHERE product='string' and ID=12345 AND startDate = (SELECT MAX(StartDate) FROM orderTable)

My advice is to use sp_help against the table(s) to become familair with the available indexes and the data types. Additionally, always read the actual execution plan while developing the query so you can see anywhere you are lacking.

My understanding is that for the most part SQL Server is going interperet your query how it wants and pick and choose indexes as it wants (you can get around this, but this is an advanced concept) so two different queries may actually run in a very similair way.

You can PM me if you need more help and don't want to post your code publically.

N2IT

Carl thanks for replying. I ended up dropping a sub query to perform this task, although I have the and clause beginning and end commented out for more granular date queries. I went with and a.startdate = (select max(a1.startdate) from tableb as a1.column = b1.column and a1.startdate <= getdate() ) Of course my boss helped a little bit Carl thanks for offering the assistance though. I think this will work in the meantime.

NotHackingYou

Glad you got it going! Keep in mind with GETDATE() that it returns current date AND time. I think this will work for you because you want everything older than now but I wonder why you need this filter in the first place - do you have future records (something schedueld to start?)?

N2IT

We do report on forecasted numbers provided to us.