A better way to write this, Without the Cursor

[hexOffender]

This needs to loop through a bunch of report rows and run the function dbo.Turnaround(VisitID)to calculate a time.
In the code below, the function has a few more parameters, but I can rewrite the function to only take the VisitID, I was using it for other reports. The Cursor is really ugly and not good.

--Delivery Record Date and Time to Delivery Record Dictation Date and Time
DECLARE fileCsr CURSOR READ_ONLY FOR
--Declare @From as datetime
--set @From ='20100601'
--declare @Thru as datetime
--set @Thru='20100605'

select distinct AD.VisitID,AD.Name,AD.AccountNumber,AD.UnitNumber,AD.Sex,
AD.BirthDateTime,AD.LocationID,
OeResults1.DictEsByUserName,
OeResults.ReportName,OeResults1.DictDateTime--
--Turnaround
FROM AbstractData as AD
Inner JOIN NurQueryResults as NQR on NQR.SourceID = AD.SourceID and NQR.VisitID = AD.VisitID
Inner Join OeResults ON AD.VisitID=OeResults.PatientVisitID
Inner Join OeResults1 ON OeResults1.ResultID=OeResults.ResultID
WHERE AD.LocationID IN('FBP','FBPI')
and OeResults.ReportName IN('*DELIVERY NOTE*','DELIVERY NOTE')
and NQR.DateTime Between '20100501'and'20100601'
--Order By AD.AccountNumber
Declare @VisitID varchar(15)
Declare @UnitNumber varchar(10)
Declare @Name varchar (40)
Declare @Account as varchar(20)
Declare @LocationID varchar(10)
Declare @Sex varchar(1)
Declare @BirthDateTime datetime
Declare @ReportName varchar(20)
Declare @DictDateTime datetime
Declare @Provider as varchar(50)




OPEN fileCsr
FETCH NEXT FROM fileCsr INTO @VisitID, @Name,@Account, @UnitNumber, @Sex,@BirthDateTime , @LocationID,@Provider,@ReportName, @DictDateTime
WHILE @@FETCH_STATUS = 0
BEGIN
--Pt name, Acct #, Unit #, Sex, DOB, Room ID, Location, Report Name, From and Thru variables, Calculated Turnaround Time.

Select @Name as Name,@Account as AccountNumber,@UnitNumber as UnitNumber,@Sex as Sex,@BirthDateTime as BirthDateTime,
@LocationID as Location,@Provider as Provider, @ReportName as ReportName,@DictDateTime as DictDateTime,
dbo.Turnaround(@VisitID,'NUR.OBDR17','NUR.OBDR99','*DELIVERY NOTE*')as Turnaround


FETCH NEXT FROM fileCsr INTO @VisitID, @Name,@Account, @UnitNumber, @Sex,@BirthDateTime , @LocationID,@Provider,@ReportName, @DictDateTime
END --WHILE
close fileCsr
DEALLOCATE fileCsr

 

[markros]

In SQL Server 2005 and up use CROSS APPLY. What exactly this function returns? It is TVF ?

Try

select distinct AD.VisitID,AD.Name,AD.AccountNumber,AD.UnitNumber,AD.Sex,
AD.BirthDateTime,AD.LocationID,
OeResults1.DictEsByUserName,
OeResults.ReportName,OeResults1.DictDateTime--
--Turnaround
FROM AbstractData as AD
Inner JOIN NurQueryResults as NQR on NQR.SourceID = AD.SourceID and NQR.VisitID = AD.VisitID 
Inner Join OeResults ON AD.VisitID=OeResults.PatientVisitID
Inner Join  OeResults1 ON OeResults1.ResultID=OeResults.ResultID 

CROSS APPLY dbo.Turnaround(NQR.VisitID, other parameters) F

WHERE AD.LocationID IN('FBP','FBPI')
 and OeResults.ReportName IN('*DELIVERY NOTE*','DELIVERY NOTE')
 and NQR.DateTime Between '20100501'and'20100601'

PluralSight Learning Library

 

[hexOffender]

The function returns a datediff from when a medical report was dictated to when it is returned to the system. What is a TVF?

I get an Invalid Object name for the function, do I need a select before the function name?

 

[markros]

If it's a scalar function, then you need to include it into the select using

select ..., dbo.Turnaround(VisitID, ...) as ..

PluralSight Learning Library

 

[bborissov]

TVF stands for Table Valued Function

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[hexOffender]

Ok I know this thread is a week old or so, but I just got around to this again. I rewrote the function to be a TVF, but I get this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Heres the query:

select distinct AD.VisitID,AD.Name,AD.AccountNumber,AD.UnitNumber,AD.Sex,
AD.BirthDateTime,AD.LocationID,
OeResults1.DictEsByUserName,
OeResults.ReportName,OeResults1.DictDateTime, F.Turnaround

FROM AbstractData as AD
Inner JOIN NurQueryResults as NQR on NQR.SourceID = AD.SourceID and NQR.VisitID = AD.VisitID
Inner Join OeResults ON AD.VisitID=OeResults.PatientVisitID
Inner Join OeResults1 ON OeResults1.ResultID=OeResults.ResultID

CROSS APPLY dbo.Taround(AD.VisitID,'NUR.BDAY','NUR.TIME','*NEWBORN EXAMINATION*') F

WHERE AD.LocationID='NUR'
and OeResults.ReportName='*NEWBORN EXAMINATION*'
and NQR.DateTime Between '20100501'and'20100601'

I ran all of the expected data through the TVF, and none of the results returned more than one result, so I know the function is correct. Every result was one column, one row as expected.

 

[markros]

The code as you have it here is OK. The problem may be in the TVF code itself. Can you post it?

PluralSight Learning Library

 

[hexOffender]

Here is the TVF:

ALTER FUNCTION [dbo].[Taround]
(
-- Add the parameters for the function here
@VisitID varchar(20),
@QueryID1 varchar(10),
@QueryID2 varchar(10),
@ReportName varchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT distinct DATEDIFF(minute,(
SELECT distinct Response
FROM NurQueryResults AS NQR
WHERE NQR.VisitID=@VisitID
AND NQR.QueryID = @QueryID1)+ ' '+
(SELECT STUFF((SELECT distinct Response
FROM NurQueryResults AS NQR
WHERE NQR.VisitID=@VisitID
AND NQR.QueryID = @QueryID2),3,0,':')+':00.000'),OeResults1.DictDateTime)as Turnaround
From NurQueryResults AS NQR
Inner Join AbstractData as AD on NQR.SourceID = AD.SourceID and NQR.VisitID = AD.VisitID
Inner Join OeResults ON AD.VisitID=OeResults.PatientVisitID
Inner Join OeResults1 ON OeResults1.ResultID=OeResults.ResultID
WHERE (NQR.VisitID=@VisitID)and OeResults.ReportName=@ReportName)

 

[markros]

Your TVF function is the problem. You have two subqueries here (one inside the other). Re-work this function to get rid of subqueries - use JOIN with derived tables instead.

PluralSight Learning Library

 

[hexOffender]

Why does it work if I use the TVF in a select statement by itself ?

 

[markros]

Pure luck, may be. I think this function needs a re-write.

PluralSight Learning Library