Solved

SQL Joins - Date Criteria being ignored

Posted on 2011-09-08
2
210 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 142

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Authentication and Win NT Authentication Issues 20 41
STDEVP in SQL 2 38
Grid querry results 41 56
Update in Sql 7 0
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now