Problems with distinct records and dates

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)

Problems with distinct records and dates

Author

Topic

gfedz
Starting Member

5 Posts

Posted - 2010-11-22 : 09:33:16

I have a query that is suppose to get all the vehicles from a location that are not scheduled for that day. When I use this query, it keeps giving me the same vehicle even after it was already scheduled. Any ideas how to show which vehicles are not scheduled for desired dates?


SELECT distinct TOP 1 v.intVehicleID, t.strVehicleType, v.intYear, v.strMake, v.strModel, v.strColor, v.intPass
FROM tblGSAVehicles AS v LEFT JOIN tblSchedule AS s ON v.intVehicleID = s.intVehicleID
     INNER JOIN tblVehicleType AS t ON v.intVehicleTypeID = t.intVehicleTypeID
WHERE v.intLocID = 1 AND v.bitInUse = 1 AND t.intVehicleTypeID = 4
     AND ((s.dtStartDate NOT BETWEEN '11/22/2010' AND '11/24/2010') OR s.dtStartDate IS NULL)
     AND ((s.dtEndDate NOT BETWEEN '11/22/2010' AND '11/24/2010') OR s.dtEndDate IS NULL)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-22 : 11:20:55

Is startdate OR enddate NULL?

No, you're never too old to Yak'n'Roll if you're too young to die.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-11-22 : 11:49:32

[code]
-- Assumming dtStartDate and dtEndDate are smalldatetime and allow NULLs
--either
SELECT --TOP (1)
*
FROM tblGSAVehicles V
JOIN tblVehicleType T
ON V.intVehicleTypeID = T.intVehicleTypeID
WHERE NOT EXISTS
(
SELECT *
FROM tblSchedule S
WHERE S.intVehicleID = V.intVehicleTypeID
AND COALESCE(S.dtEndDate, 65535.0) >= '20101122'
AND COALESCE(S.dtStartDate, 0.0) < '20101125'
)
-- or
SELECT --TOP (1)
*
FROM tblGSAVehicles V
JOIN tblVehicleType T
ON V.intVehicleTypeID = T.intVehicleTypeID
LEFT JOIN tblSchedule S
ON V.intVehicleID = S.intVehicleTypeID
-- FROM evaluated before WHERE so these filters should be here
AND COALESCE(S.dtEndDate, 65535.0) >= '20101122'
AND COALESCE(S.dtStartDate, 0.0) < '20101125'
WHERE S.intVehicleTypeID IS NULL
[/code]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-22 : 11:55:18

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. You did none of these things, so let's try to clean up the code.

Do you know about VIN? Do you know why things like "type_id" make no sense? A vehicle type is an attribute, like make and model. These are all encoded in the VIN. Why is this attribute in another table?

What do you think that DISTINCT TOP 1 is going to do? It tells my your schema is so de-normalize that you have redundant duplicates in your query.

What do NULLs in the start and end dates of a reservation mean? A NULL end date is used to show an uncompleted event and it is coalesced to the current daet for reports.

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

--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

gfedz
Starting Member

5 Posts

Posted - 2010-11-23 : 11:10:45

I'm pretty new working with databases. Here is the build of tblSchedule.


CREATE TABLE [dbo].[tblSchedule](
	[intSchedID] [int] IDENTITY(1,1) NOT NULL,
	[intVehicleID] [int] NOT NULL,
	[intDriverID] [int] NOT NULL,
	[intReasonID] [int] NOT NULL,
	[strVehicleRequestNumber] [varchar](50) NOT NULL,
	[intAdminID] [int] NULL,
	[dtAdminAlter] [datetime] NULL,
	[strAdminNotes] [varchar](50) NULL,
	[dtStartDate] [datetime] NOT NULL,
	[dtEndDate] [datetime] NOT NULL,
	[tStartTime] [varchar](8) NOT NULL,
	[tEndTime] [varchar](8) NOT NULL,
	[strDescription] [varchar](255) NULL,
	[bitAccepted] [bit] NOT NULL,
	[bitPending] [bit] NOT NULL,
	[bitDenied] [bit] NOT NULL,
	[intRequestBy] [int] NOT NULL,
	[dtRequestDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tblSchedule] PRIMARY KEY CLUSTERED 
(
	[intSchedID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

[url=http://b.imagehost.org/view/0436/tblSchedule]

[/url]

jcelko - If you have nothing constructive to say, please do not post on my topics. You do not know the way my organization works or how they want things done. The db is built for ease of use for the programmers that manipulate these db. The reason a column would be named strDescription, for example, is so the programmers know the field will be a string, not an integer or whatever else. Same with dtStartDate, they know it is a date. And the VIN issue, that is a whole other rant that I'm not going into. Again, people post topics for help on finding solutions for their questions. They do not post questions for people to criticize their structure of their db, or the logic behind what they are doing.

gfedz
Starting Member

5 Posts

Posted - 2010-11-23 : 13:12:26

I got it figured out. Thank you for all the help.


SELECT top 1 v.intVehicleID, t.strVehicleType, v.intYear, v.strMake, v.strModel, v.strColor, v.intPass
  FROM tblGSAVehicles AS v INNER JOIN 
       tblVehicleType AS t ON v.intVehicleTypeID = t.intVehicleTypeID  WHERE v.intLocID = 1 AND 
       v.bitInUse = 1 AND 
       t.intVehicleTypeID = 4 AND 
       v.intVehicleID not in (
         select s.intVehicleID 
           FROM tblSchedule AS s
           WHERE (s.dtStartDate IS NULL OR s.dtStartDate NOT BETWEEN convert(datetime, '11/23/2010', 101) AND convert(datetime, '11/25/2010', 101)) 
                 AND 
                 (s.dtEndDate IS NULL OR s.dtEndDate NOT BETWEEN convert(datetime, '11/23/2010', 101) AND convert(datetime, '11/25/2010', 101))
       )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources