Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Joins - Date Criteria being ignored

Posted on 2011-09-08
2
219 Views
Last Modified: 2012-05-12
Hi,

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:

origAppID    appFeeUsrID
100              7220
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) + '%'

SELECT dbo.dbAppointments.*,
dbo.dbuser.usrFullName,
dbo.dbClient.clNo + '/' + dbo.dbFile.fileNo AS clfileno,
dbo.dbFile.fileDesc,
dbo.dbClient.clName
FROM        
dbo.dbAppointments
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

WHERE
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
0
Comment
Question by:smsstech
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36502630
your OR and AND mixing in the where clause is the issue
please try
WHERE ( 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

Open in new window

0
 

Author Closing Comment

by:smsstech
ID: 36502663
Sigh.  Thanks my friend.  Perhaps I've been looking at this too long.  I definitely appreciate the help.  :)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question