Viewing details with dates clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Viewing details with dates clause

Author

Topic

gfedz
Starting Member

5 Posts

Posted - 2010-11-12 : 13:04:15

I have a locations table, vehicles table, and a schedule table. I need to pull all the information about the vehicles from a certain location that are not scheduled for certain dates. Here is my syntax so far. I have 6 vehicles for this location, 2 of which are scheduled from 11/12/2010-11/14/2010. When I do this query, it only shows me the 4 others and not all. Any ideas on how to show all that are available for that date?

SELECT v.strType, v.intYear, v.strMake, v.strModel, v.strColor, v.intPass, v.intLocID, v.intVehicleID
FROM tblGSAVehicles AS v LEFT JOIN tblSchedule AS s ON v.intVehicleID = s.intVehicleID
WHERE CONVERT(VARCHAR(10), s.dtStartDate, 121) < '11/17/2010'
AND CONVERT(VARCHAR(10), s.dtStartDate, 121) > '11/19/2010' OR s.dtStartDate IS NULL
AND CONVERT(VARCHAR(10), s.dtEndDate, 121) < '11/17/2010'
AND CONVERT(VARCHAR(10), s.dtEndDate, 121) > '11/19/2010' OR s.dtEndDate IS NULL
AND v.intLocID = 23

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 13:15:01

Get rid of the CONVERT stuff.

<snip>
WHERE
((s.dtStartDate NOT BETWEEN '11/17/2010' AND '11/19/2010') OR s.dtStartDate IS NULL)
AND
((s.dtEndDate NOT BETWEEN '11/17/2010' AND '11/192010') OR s.dtEndDate IS NULL)
AND (v.intLocID = 23)

Try that, see if it works.

gfedz
Starting Member

5 Posts

Posted - 2010-11-12 : 13:25:14

I tried something similar to that before and didn't get the wanted results but that worked! Thank you for your time.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 14:03:30

DDL, sample data and expected results would help a bunch

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-12 : 16:39:48

You have a huge number of classic errors in one statement.

1) Data element names do not include the data type. That was BASIC over 30 years ago.
2) Data element names don't use vague names or reserved words
3) Only 1950's COBOL programmer handle temporal data as strings
4) We use ISO-8601 date formats, not dialect.
5) We research Industry standards BEFORE we code, so we know about VIN for vehicles

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Here is a guess, with assumptions about the start and end dates, and a parameter for a particular date.

SELECT V.vehicle_type, V.vehicle_year, -- part of VIN
V.vehicle_make, V.vehicle_model, -- part of VIN
V.vehicle_color, V.something_pass,
V.location_id, V.vin
FROM GSA_Vehicles AS V
LEFT OUTER JOIN
Motorpool_Schedule AS S
ON V.vin = S.vin
WHERE V.location_id = 23
AND @in_certain_date
NOT BETWEEN S.scheule_start_date AND schedule_end_date;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources