Exists statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Exists statement

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-11-07 : 15:55:25

I have a situation where I ran a summary report using the same udf that a stored procedure also uses to generate a weekly report. On my summary report I'm getting results for a date range where the weekly report does not have those results. Here is a snippet of the stored procedure:

if exists(
Select OfficeNum,EntityCode,AcctNum,PatientName,ConsultDate,[Initial Writeup to be Filed Date]
from dbo.[udf_MedicaidConsults] (@OfficeNumber, @StartDate, @EndDate)
) begin

set @SQL = 'Select AcctNum,PatientName,ConsultDate,[Initial Writeup to be Filed Date]
from dbo.[udf_MedicaidConsults]'+' '+'('''+ @OfficeNumber +''',''' + convert(varchar(10),@StartDate,101) +''',''' + convert(varchar(10),@EndDate,101) +''')
order by ConsultDate DESC, PatientName
/*<title>' + @Title1 + '</title><comments>' + @Desc + '</comments>*/;'
end else begin
set @SQL = 'Select ''No Patient Returned '' /*<title>' + @Title1 + '</title><comments>' + @Desc + '</comments>*/;'
end

The query I used for summary is:
select * from dbo.[udf_MedicaidConsults] ('315473','04/01/08','11/01/08')

Does the EXISTS statement check for every field in the SELECT statement to determine if there are records? If so, this may be the reason because most patients who keep an consult may not have an AcctNum at the time of the appointment. And in my second query, I'm not looking for existing conditions.

EDIT: It cannot be because a patient does not have an AcctNum. I have reports that were sent out with records where the patient didn't have an account number. So I'm quite stump why using the same udf, I'm getting results on one and not the other.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-07 : 18:12:01

The exists only checks for the exist of a tuple. So, it doesn't check each column. It only EXISTS if a row exits.

Any chance it is a date issue? You could pass in yout dates in the ANSI standard (YYYYMMDD) and see if that helps anything.

Does the report do and extra work besides simply returning data from a UDF?

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-11-10 : 09:58:15

It is not a date issue as many of the patients that did not show up on the original weekly report had consult dates that were in the middle of the week which should have covered any > or < issues. Here is the udf if anyone can detect any problems:

ALTER FUNCTION [dbo].[udf_MedicaidConsults](@OfficeNumber varchar(6), @StartDate datetime, @EndDate datetime)

RETURNS @MedCons TABLE (
OfficeNum varchar(8),
EntityCode varchar(8),
AcctNum varchar(12),
PatientName varchar(50),
ConsultDate datetime,
[Initial Writeup to be Filed Date] datetime
)

AS
BEGIN

Insert @MedCons
Select
c.ConfigOfficeNumber as 'OfficeNum',
c.ConfigECode as 'EntityCode',
p.PatsOfficeAccount as 'AcctNum',
p.PatsFirstName+' '+p.PatsLastName as 'PatientName',
convert(varchar(10),appt.dt_appt,101) as 'ConsultDate',
convert(varchar(10),appt.dt_appt,101) as 'Initial Writeup to be Filed Date'
from tPats p
inner join vw_MedicaidPatients mp
on mp.OfficeNumber = p.OfficeNumber
and mp.PatsNumber = p.PatsNumber
inner join tConfig c
on c.ConfigOfficeNumber = p.OfficeNumber
left join tbl_appt appt
on appt.OfficeNumber = mp.OfficeNumber
and appt.PatsNumber = mp.PatsNumber
where p.OfficeNumber = @OfficeNumber
and appt.task_cd in (2,24,902)
and appt.stat_cd in (1,7)
and convert(varchar(10),appt.dt_appt,101) >= @StartDate
and convert(varchar(10),appt.dt_appt,101) < Dateadd(dd,1,@EndDate)
order by c.ConfigECode, p.PatsFirstName

RETURN
END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-10 : 11:45:51

quote:
Originally posted by andros30

and convert(varchar(10),appt.dt_appt,101) >= @StartDate
and convert(varchar(10),appt.dt_appt,101) < Dateadd(dd,1,@EndDate)

Nothing jumps out at me that would cause an issue. But, why are you converting appt.dt_appt to a varchar? I assume it is stored as a datetime.

If you are worried about the time portion, then you should remove that from your variable, not from the column. SQL cannot take advantage of of anyh indexes you might have on those date columns if you convert or cast it in the where clause. For example:


and appt.dt_appt >= SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate), 0
and appt.dt_appt < SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Dateadd(dd,1,@EndDate)), 0)

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-11-10 : 12:00:03

The time was unimportant in this situation so I wanted to strip out the time. Since it writes to Excel, I didn't want it to show up as 00:00:000 after the date. Sure I could format it later but the report sends automatically to the recipients so it would be too late.

After retesting my stored procedure, I coming to the conclusion that those patients must not have met a condition at the time of the weekly report and then were update after. I re-ran the stored procedure entering a date where patients where missing for on the original report and now they show up.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources