SubQuery Returned more than 1 value error

[sanctified]

Hi Group,
I'm getting the following error in my SP when the tblHolidayDates has more than one record. Here is my SP:
begin

if datediff(day,getdate()-10 ,(Select HolidayDate from tblHolidayDates))= 3 AND
DATEPART(dw,(select holidaydate from tblHolidayDates)) = 1 -- Monday Date
SELECT su.SuspensionID, su.ConsentNo, si.SiteID,
si.NameNo + ' ' + si.Address1 AS SiteAddress, si.SiteDesc,
si.SuspensionStart, si.SuspensionEnd, si.Address1, si.BaysAttached,
rd.RefValue AS NoticeDays, si.MergeID
FROM Suspension su
INNER JOIN SiteData si ON su.SuspensionID = si.SuspensionID
INNER JOIN RefData rd ON si.NotReq = rd.RefCode
WHERE su.Status = 'ACT'
and si.Archived = 'N'
and (datediff(day, getdate()-10 , si.SuspensionStart) = rd.RefValue
OR (datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue + 2)
or datediff(day,(Select holidaydate from tblHolidayDates), si.SuspensionStart)=rd.RefValue + 1)
end

Any pointers much appreciated.

 

[vbkris]

sub queries can return back only one value:

>>if datediff(day,getdate()-10 ,(Select HolidayDate from tblHolidayDates))=


wht is the logic there? are u checking for ALL the dates that have a difference of more than 3 days???

Known is handfull, Unknown is worldfull

 

[sanctified]

Yes,however this should ever only return ONE value as:

1: the field is keyed
2: I'm comparing against the current date (yes I know maybe the parser is not syntax intelligent but surely 1 should allow for this select statement?)

thanks

 

[vbkris]

>>Select HolidayDate from tblHolidayDates

>>Yes,however this should ever only return ONE value as:

how? does the table tblHolidayDates has only ONE entry???

Known is handfull, Unknown is worldfull