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

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