This can be a nifty way of building crosstab (2D) data:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_TableValuedFunctionExample]
(
@Month INT,
@Year INT
)
RETURNS @ScriptsPerPatient TABLE
(
PatientID INT ,
NumberOfScripts INT
)
AS
BEGIN
INSERT INTO @ScriptsPerPatient
( PatientID ,
NumberOfScripts
)
SELECT PatientID ,
COUNT(SCRIPTID) AS NumberOfScripts
FROM ScriptData
WHERE Month = @Month and Year = @Year
GROUP BY PatientID
ORDER BY NumberOfScripts DESC
RETURN
END
GO
Example usage:
SELECT
tbl_Patients.PatientID,
dt1.NumberOfScripts as AprilsScripts,
dt2.NumberOfScripts as MaysScript
FROM tbl_Patients
LEFT OUTER JOIN udf_TableValuedFunctionExample(4, 2010) as dt1 ON tbl_Patients.PatientID = dt1.PatientID
LEFT OUTER JOIN udf_TableValuedFunctionExample(5, 2010) as dt2 ON tbl_Patients.PatientID = dt1.PatientID
Edited by admin on 26-06-2010 17:55
Jordon Pilling | Heavencore Administrator
|
