HeavenCore | IT Solutions Built on Open Source Software
View Thread
 Print Thread
Function to return table (ideal for joining)
admin
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 18:55
Jordon Pilling | Heavencore Administrator
 
Jump to Forum