Saturday, February 25, 2012

Looking for query help

Can anyone see why this only works if an address had no enddate? Something is going wrong in the WHERE Clause, if the enddate is null it works fine. What I need is to look at the enddate and if it is Null or >= today the record should be returned. I only want to use the Month and Day of the date.

Thank you for any help,


CREATE PROCEDURE [dbo].[sp_DataReaderName] @.SID int AS
SELECT
CASE
WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +" "+ C.[LastName]
ELSE A.[CompanyName]
END AS DRName,
C.Client_ID
FROM
tblClients C
INNER JOIN
tblClientAddresses A ON C.Client_ID = A.Client_ID
WHERE
(C.Client_ID = @.SID) AND
(A.MailTo=1) AND
(A.EndDate Is Null OR (DatePart(m,A.Enddate) >= DatePart(m,GETDATE()) AND DatePart(d,A.Enddate) >= DatePart(d,GETDATE())))
GO
Its late and I've not spotted anything obvious except...DON'T prefix with "sp_" you'll incur a needless performance cost. SQL Server will think its a Microsoft Proc and go straight to Master.

No comments:

Post a Comment