February 7, 2013 at 7:29 am
Here is my function and the SELECT statement that I'm expecting to return 41 records. What am I missing?
CREATE FUNCTION dbo.ufnCHFPatients(@StartDate datetime,
@EndDate datetime)
RETURNS @retCHFPatients TABLE
(
-- Columns returned by the function
MonthNumber int,
UnitNumber nvarchar(50) NULL,
PatientName nvarchar(50) NULL,
FirstDischarge datetime NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@MonthNumber int,
@UnitNumber nvarchar(50),
@PatientName nvarchar(50),
@FirstDischarge datetime;
SELECT
@MonthNumber = month(a.DischargeDateTime),
@UnitNumber = a.UnitNumber,
@PatientName = a.Name,
@FirstDischarge=min(a.DischargeDateTime)
from AbstractData a
inner join AbsDrgDiagnoses b on
a.SourceID=b.SourceID
and a.AbstractID=b.AbstractID
inner join (select SourceID, AbstractID
from AbsDrgDiagnoses
where Diagnosis in ('402.01','402.11','402.91','404.01','404.03','404.11','404.13','404.91','404.93','428.0','428.10','428.20',
'428.21','428.22','428.23','428.30','428.31','428.32','428.33','428.40','428.41','428.42','428.43','428.90')
and DiagnosisSeqID='1') c on
b.SourceID=c.SourceID
and b.AbstractID=c.AbstractID
where a.DischargeDateTime>=@StartDate and a.DischargeDateTime<=@EndDate
and a.PtStatus='IN'
and a.LocationID!='A3/TCU'
and b.DiagnosisSeqID='1'
group by month(a.DischargeDateTime), a.UnitNumber, a.Name
return;
end;
select *
from dbo.ufnCHFPatients('2012-11-01','2012-12-31')
February 7, 2013 at 7:59 am
We do not have access to your environment. If you would provide test tables (CREATE TABLE statements), test data (INSERT statements) and your expected results based on that test data I would be, as I am sure others will as well, happy to help you.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 7, 2013 at 8:07 am
Dont you have it insert the data into the return table @retCHFPatients?
so I think your select should read something like
INSERT INTO @retCHFPatients
SELECT
@MonthNumber = month(a.DischargeDateTime),
a.UnitNumber,
a.Name,
min(a.DischargeDateTime)
from AbstractData a
inner join AbsDrgDiagnoses b on
a.SourceID=b.SourceID
and a.AbstractID=b.AbstractID
inner join (select SourceID, AbstractID
from AbsDrgDiagnoses
where Diagnosis in ('402.01','402.11','402.91','404.01','404.03','404.11','404.13','404.91','404.93','428.0','428.10','428.20',
'428.21','428.22','428.23','428.30','428.31','428.32','428.33','428.40','428.41','428.42','428.43','428.90')
and DiagnosisSeqID='1') c on
b.SourceID=c.SourceID
and b.AbstractID=c.AbstractID
where a.DischargeDateTime>=@StartDate and a.DischargeDateTime<=@EndDate
and a.PtStatus='IN'
and a.LocationID!='A3/TCU'
and b.DiagnosisSeqID='1'
group by month(a.DischargeDateTime), a.UnitNumber, a.Name
Return @retCHFPatients
I have to admit my TVF function knowledge is limited as I rarely use them so the syntax may be off.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2013 at 9:50 am
CREATE TABLE AbstractData
(SourceID varchar(3),
AbstractID int,
UnitNumber varchar(6),
PatientName varchar(50),
PtStatus varchar(5),
LocationID varchar(10),
DischargeDateTime datetime
)
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1234','167890','xxx,yyyy', 'IN','A2','2012-01-01')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1235','527890','zzz,yyyy', 'IN','A2','2012-01-02')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1236','563890','ddd,yyyy', 'IN','A5','2012-01-03')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1237','567490','xxx,eeee', 'IN','A2','2012-01-04')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1238','567850','ffff,yyyy', 'IN','A5','2012-01-05')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1239','567896','xxx,ggy', 'IN','B2','2012-01-05')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1214','567870','xee,yyyy', 'IN','A5','2012-01-06')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1224','567890','xxx,ygggy', 'IN','B2','2012-01-01')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1244','569890','xcc,yyyy', 'IN','A2','2012-01-21')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1254','507890','xxx,ccyy', 'IN','B2','2012-01-21')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1264','467890','xtt,yyyy', 'IN','A5','2012-01-22')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1274','557890','xxx,ytty', 'IN','A2','2012-01-23')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1834','566890','xuu,yyyy', 'IN','B2','2012-01-31')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1294','567790','xxx,yiiy', 'IN','A2','2012-01-29')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1034','567880','xpp,yyyy', 'IN','A5','2012-01-23')
INSERT INTO xxAbstractData (SourceID,AbstractID,UnitNumber,PatientName,PtStatus,LocationID,DischargeDateTime ) VALUES ('BRO', '1204','567899','xxx,jjjy', 'IN','A2','2012-01-19')
CREATE TABLE AbsDrgDiagnoses
(SourceID varchar(3),
AbstractID int,
DiagnosisSeqID int,
Diagnosis varchar(10)
)
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1234','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1235','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1236','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1237','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1238','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1239','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1214','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1224','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1244','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1254','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1264','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1274','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1834','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1294','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1034','1','428.21')
INSERT INTO xxAbsDrgDiagnoses (SourceID,AbstractID,DiagnosisSeqID,Diagnosis) VALUES ('BRO', '1204','1','428.21')
February 7, 2013 at 10:03 am
Try this, it is an inline table valued function instead of a multi-statement table valued function:
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = object_id('dbo.ufnCHFPatients'))
DROP FUNCTION dbo.ufnCHFPatients;
GO
CREATE FUNCTION dbo.ufnCHFPatients(
@StartDate datetime,
@EndDate DATETIME
)
RETURNS TABLE
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
RETURN(
SELECT
month(a.DischargeDateTime) AS MonthNumber,
a.UnitNumber AS UnitNumber,
a.NAME AS PatientName,
min(a.DischargeDateTime) AS FirstDischarge
from
AbstractData a
inner join AbsDrgDiagnoses b
ON a.SourceID=b.SourceID
and a.AbstractID=b.AbstractID
inner join (select SourceID, AbstractID
from AbsDrgDiagnoses
where Diagnosis in ('402.01','402.11','402.91','404.01','404.03','404.11','404.13','404.91','404.93','428.0','428.10','428.20',
'428.21','428.22','428.23','428.30','428.31','428.32','428.33','428.40','428.41','428.42','428.43','428.90')
and DiagnosisSeqID='1') c
ON b.SourceID=c.SourceID
and b.AbstractID=c.AbstractID
WHERE
a.DischargeDateTime >= @StartDate
and a.DischargeDateTime < DATEADD(dd, 1, @EndDate) -- want to be sure to get all records on last day
and a.PtStatus='IN'
and a.LocationID!='A3/TCU'
and b.DiagnosisSeqID='1'
group by
month(a.DischargeDateTime),
a.UnitNumber,
a.Name
);
GO
select *
from dbo.ufnCHFPatients('2012-11-01','2012-12-31')
February 7, 2013 at 10:28 am
Perfect! Thanx.
Could you give me a short, simple explanation of the difference between the two?
February 7, 2013 at 10:32 am
I strongly recommend fully qualifying all table names with the owning schema, typically "dbo" but of course could be different in your specific environment.
For example:
...
from dbo.AbstractData a
inner join dbo.AbsDrgDiagnoses b on
...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 7, 2013 at 10:37 am
ScottPletcher (2/7/2013)
I strongly recommend fully qualifying all table names with the owning schema, typically "dbo" but of course could be different in your specific environment.For example:
...
from dbo.AbstractData a
inner join dbo.AbsDrgDiagnoses b on
...
In general I agree with that but it's worth noting that in some environments, e.g. multi-tenant multi-schema databases, you do not want to fully-qualify your objects, you want to rely on the Database User's default schema.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 7, 2013 at 11:00 am
Here is a working version of your Scalar UDF:
CREATE FUNCTION dbo.ufnCHFPatients
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS @retCHFPatients TABLE
(
-- Columns returned by the function
MonthNumber INT,
UnitNumber NVARCHAR(50) NULL,
PatientName NVARCHAR(50) NULL,
FirstDischarge DATETIME NULL
)
AS -- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
INSERT INTO @retCHFPatients
(
UnitNumber,
MonthNumber,
FirstDischarge,
PatientName
)
SELECT a.UnitNumber,
MONTH(a.DischargeDateTime),
MIN(a.DischargeDateTime),
a.PatientName
FROM AbstractData a
INNER JOIN AbsDrgDiagnoses b ON a.SourceID = b.SourceID
AND a.AbstractID = b.AbstractID
WHERE a.DischargeDateTime >= @StartDate
AND a.DischargeDateTime <= @EndDate
AND a.PtStatus = 'IN'
AND a.LocationID != 'A3/TCU'
AND b.DiagnosisSeqID = 1
AND b.Diagnosis IN ( '402.01', '402.11', '402.91', '404.01', '404.03', '404.11', '404.13', '404.91', '404.93', '428.0', '428.10', '428.20',
'428.21', '428.22', '428.23', '428.30', '428.31', '428.32', '428.33', '428.40', '428.41', '428.42', '428.43', '428.90' )
GROUP BY MONTH(a.DischargeDateTime),
a.UnitNumber,
a.PatientName;
RETURN;
END;
GO
SELECT *
FROM dbo.ufnCHFPatients('20120101', '20121231');
GO
Converting this to an Inline TVF:
CREATE FUNCTION dbo.ufnCHFPatientsTVF
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS TABLE
AS -- Returns the first name, last name, job title, and contact type for the specified contact.
RETURN
( SELECT MONTH(a.DischargeDateTime) AS MonthNumber,
a.UnitNumber,
a.PatientName,
MIN(a.DischargeDateTime) AS FirstDischarge
FROM AbstractData a
INNER JOIN AbsDrgDiagnoses b ON a.SourceID = b.SourceID
AND a.AbstractID = b.AbstractID
WHERE a.DischargeDateTime >= @StartDate
AND a.DischargeDateTime <= @EndDate
AND a.PtStatus = 'IN'
AND a.LocationID != 'A3/TCU'
AND b.DiagnosisSeqID = 1
AND b.Diagnosis IN ( '402.01', '402.11', '402.91', '404.01', '404.03', '404.11', '404.13', '404.91', '404.93', '428.0', '428.10', '428.20',
'428.21', '428.22', '428.23', '428.30', '428.31', '428.32', '428.33', '428.40', '428.41', '428.42', '428.43', '428.90' )
GROUP BY MONTH(a.DischargeDateTime),
a.UnitNumber,
a.PatientName
);
GO
SELECT *
FROM dbo.ufnCHFPatientsTVF('20120101', '20121231');
GO
The difference between a Scalar-valued Function (SVF) and an Inline Table-valued Function (iTVF) in terms of performance can be the difference between a query that takes seconds versus one that takes minutes. SVFs are useful but limiting. Choose iTVFs wherever possible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 7, 2013 at 11:41 am
Pardon my ignorance but, I thought I was creating a TVF. I knew that I would be returning more than one value.
The whole point of me creating this is because I need to report on data that currently uses stored procedures with parameters. Is this the correct way to do this? Basically, replace the sp's with TVFs?
February 7, 2013 at 11:56 am
NineIron (2/7/2013)
Pardon my ignorance but, I thought I was creating a TVF. I knew that I would be returning more than one value.The whole point of me creating this is because I need to report on data that currently uses stored procedures with parameters. Is this the correct way to do this? Basically, replace the sp's with TVFs?
You created a Multi-statement Table-Valued Function (mTVF). Inline Table-Valued Functions (iTVF) perform much better.
The general reason is that SQL Server must treat can mTVFs as a black-box, i.e. executed first and then joined to, whereas with an iTVF SQL Server can rewrite the outer query to include the query inside the iTVF, then optimize the entire query, then execute it. The difference is subtle but the difference in performance can be quite drastic.
PS Sorry about misspeaking before. SVFs and mTVFs are both treated as black boxes by SQL Server and I said Scalar by mistake.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 7, 2013 at 12:01 pm
Thanx. One more question........can I join TVFs the same way as a table or view?
February 7, 2013 at 12:03 pm
Another question. Is this how I use the function? Both parameters separated by comma?
inner join dbo.ufnCHFPatients(@StartDate,@EndDate)
February 7, 2013 at 12:13 pm
Yes and yes (to your last two questions)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2013 at 12:20 pm
Thanx. I'm on my way...............................
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply