SQL Joins - Date Criteria being ignored
Posted on 2011-09-08
I've got a stored procedure that returns data into a form. This was working well until I was tasked with also adding another table.
I have a main dbAppointments table which has data like this:
appID fileID feeusrID appDate
100 283283 7459 2010-02-06 01:00:00.000
101 285674 7125 2010-05-06 02:00:00.000
102 287444 7459 2010-06-08 02:00:00.000
and I have a secondary table udApptExtraInvite which has data like this:
101 7459; 7220
I need all appID's and origAppID's that have, for example, 7459 as the dbAppointments.feeusrID or contained within udApptExtraInvite.appFeeUsrID and I need date filtering. All origAppID's from udApptExtraInvite are viable appID's in dbAppointments.
Note, there are appID's in the dbAppointments table with a feeusrID of 7459 that are not in the udApptExtraInvite table as origAppID.
This is part of the procedure for testing. My issue is the correct data comes back but when I add the date filter, the script basically is only removing the applicable entries from the udApptExtraInvite table and not filtering anything further. If I remove "OR AEI.appFeeUsrID like @FEES" the date filtering works correctly.
How do I resolve this? Any suggestions would be appreciated.
Declare @FEEUSRID as bigint
SET @FEEUSRID = '7459'
Declare @FEES as varchar(50)
set @fees = '%' + convert(varchar, @FEEUSRID) + '%'
dbo.dbClient.clNo + '/' + dbo.dbFile.fileNo AS clfileno,
INNER JOIN dbo.dbFile ON dbo.dbAppointments.fileID = dbo.dbFile.fileID
INNER JOIN dbo.dbUser ON dbo.dbAppointments.feeusrID = dbo.dbUser.usrID
INNER JOIN dbo.dbClient ON dbo.dbFile.clID = dbo.dbClient.clID
LEFT JOIN udapptextrainvite AEI ON dbo.dbAppointments.appID = AEI.origAppID
dbo.dbAppointments.feeusrid = coalesce(7459, dbo.dbAppointments.feeusrid)
OR AEI.appFeeUsrID like @FEES
AND (dbo.dbappointments.appdate > '2011-09-30 01:00:00.000')
order by appid