TSQL Newbie - HELP! - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

TSQL Newbie - HELP!

Author

Topic

RAVSKINS
Starting Member

21 Posts

Posted - 2009-04-08 : 17:31:13

Where to begin. I'm relatively new to SQL, and I've been working on this query for several days now with no luck. I'm hoping that I can get some feedback from some more experienced posters/DBAs (you). I need to get this fixed as quickly as possible.

Goal: To pull data from various tables (HomeSlice (H), HomeSkillet (S), RavensTitans (RT), LawrenceTaylor (LT)), filtered by the following criteria.

I need all entries where IsTerminated = 0,
with an EffDate of anytime in 2008,
and LaborCategoryId is not NULL,
and S.LastName and H.LastName are not blank,
And Level_Type_ID is either of the three values defined (725FBD2A-E284-491E-9E46-FF5E3FBC9C55, 522C49EC-93A4-4413-A2C6-54E3B4365C50, and "NULL"),
and the S.LastName does not contain the string "TERM".

Problem: The returned results of the query are not between the specified dates (I have dates from 2009 to 1950 instead of only 2008), I only get the "NULL" level types (no 725FBD2A-E284-491E-9E46-FF5E3FBC9C55 and 522C49EC-93A4-4413-A2C6-54E3B4365C50), and I still get NULL values in my LaborCategoryId column.

I'm using the query below - any help would be GREATLY appreciated!

SELECT DISTINCT MAX(H.EffDate) AS EffectiveDate, S.LastName, S.FirstName, LT.Level_Type, RT.Name AS LaborCategory
FROM dbo.HomeSlice H
INNER JOIN dbo.HomeSkillet S ON H.ID = S.ID
LEFT OUTER JOIN dbo.RavensTitans RT ON RT.Id = H.LaborCategoryId
LEFT OUTER JOIN dbo.LawrenceTaylor LT ON S.Level_Type_ID = LT.Level_Type_ID
WHERE S.IsTerminated = '0'
AND H.EffDate BETWEEN 2008-01-01 AND 2008-12-31
AND H.LaborCategoryID IS NOT NULL
AND S.LastName <> ''
AND S.FirstName <> ''
AND S.Level_Type_ID IN ('725FBD2A-E284-491E-9E46-FF5E3FBC9C55', '522C49EC-93A4-4413-A2C6-54E3B4365C50')
OR S.Level_Type_ID IS NULL
AND S.LastName NOT LIKE '%TERM%'
GROUP BY S.LastName, S.FirstName, RT.Name, LT.Level_Type
ORDER BY S.LastName, S.FirstName, RT.Name, LT.Level_Type

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-08 : 17:45:51

you need quotes here:
AND H.EffDate BETWEEN '2008-01-01' AND '2008-12-31'

you need to enclose the OR logic in paranthesis:
AND (S.Level_Type_ID IN ('725FBD2A-E284-491E-9E46-FF5E3FBC9C55', '522C49EC-93A4-4413-A2C6-54E3B4365C50')
OR S.Level_Type_ID IS NULL)

Be One with the Optimizer
TG

propanecan
Yak Posting Veteran

60 Posts

Posted - 2009-04-08 : 17:48:48

Is Effdate a datetime datatype. Try using single quotes around the dates.

AND H.EffDate BETWEEN '2008-01-01' AND '2008-12-31'

RAVSKINS
Starting Member

21 Posts

Posted - 2009-04-08 : 17:51:16

quote:
Originally posted by TG

you need quotes here:
AND H.EffDate BETWEEN '2008-01-01' AND '2008-12-31'

you need to enclose the OR logic in paranthesis:
AND (S.Level_Type_ID IN ('725FBD2A-E284-491E-9E46-FF5E3FBC9C55', '522C49EC-93A4-4413-A2C6-54E3B4365C50')
OR S.Level_Type_ID IS NULL)

Be One with the Optimizer
TG

You Sir are my new best friend. Thank you - worked perfectly.

I'm completely Hetero, but I am willing to have your baby.

RAVSKINS
Starting Member

21 Posts

Posted - 2009-04-08 : 17:52:02

quote:
Originally posted by propanecan

Is Effdate a datetime datatype. Try using single quotes around the dates.

AND H.EffDate BETWEEN '2008-01-01' AND '2008-12-31'

Thanks for the reply.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-08 : 18:03:11

>>I'm completely Hetero, but I am willing to have your baby.

I had several responses float through my brain but I'll just leave it at: Thanks for the laugh!

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources