Create FUNCTION [dbo].[fnGetProductionProjection] --'2012','may','COR'
(
--DECLARE
 @fYear INT = 2011,
 @fMonth VARCHAR(15) = 'May',
 @rLocation CHAR(3) = 'COR'
) 
RETURNS @tbl TABLE (
    fLocation VARCHAR(3),
    fType TINYINT,
    Specialty VARCHAR(15),
    fProductionByDrDays MONEY,
    fBudgetByDrDays MONEY,
    fProjection MONEY,
    fBudget MONEY,
    fProduction MONEY,
    fDrDays INT,
    fGoalDrDays INT,
    fOfficeDrDays INT
)
AS
BEGIN 
DECLARE 
    @FromDate AS DATETIME, 
    @ToDate AS DATETIME
DECLARE @LocationList AS TABLE (fLocation NVARCHAR(6))

SET @FromDate = CONVERT(DATE,LTRIM(@fyear)+'/'+@fMonth+'/01')
SET @ToDate   = CONVERT(DATE,DATEADD(MINUTE,-1, DATEADD(MONTH,1,@FromDate)))
IF @rLocation <> 'COR' 
    INSERT INTO @LocationList (fLocation)
    VALUES (@rLocation )
ELSE
    INSERT INTO @LocationList
    SELECT DimLocation.fLocation
    FROM DimLocation
    WHERE DimLocation.fLocation <> 'COR';

 
WITH tProduction AS (
SELECT
 Z.Flocation,
 Z.fSpecialty,
 isnull(SUM(Z.fProdAmt),0) AS fProdAmt,
 isnull(SUM(Z.fWorkedDays),0) AS fWorkedDays
FROM
    (
    SELECT 
        Y.Flocation,
        Y.DimProviderID,
        isnull(SUM(Y.fProdAmt),0) fProdAmt, 
        isnull(SUM(Y.fWorkedDays),0) fWorkedDays,
        y.fSpecialty 
    FROM
        (
        SELECT
            DimLocation.Flocation,
            a.DimProviderID,
            isnull(SUM(a.Fprodamt),0) AS fProdAmt,
            isnull(COUNT(DISTINCT a.Ftxdate),0) AS fWorkedDays,
            a.dimSpecialtyId as fSpecialty
        FROM
            (
            SELECT
                fctDxTxDetail.dimLocationId,
                fctDxTxDetail.dimProviderId,
                fctDxTxDetail.Fprodamt,
                fctDxTxDetail.Ftxdate,
                (CASE WHEN lkpProvider.RDH = 1
                      THEN ( SELECT DimSpecialities.dimSpecId
                             FROM DimSpecialities
                             WHERE DimSpecialities.fDesc = 'RDH' )
                      ELSE fctDxTxDetail.dimSpecialtyId
                 END ) AS dimSpecialtyId
            FROM
                fctDxTxDetail 
                INNER JOIN lkpProvider
                    ON fctDxTxDetail.dimProviderId = lkpProvider.dimlkpProviderId 
            ) AS a
            INNER JOIN DimLocation
                ON a.dimLocationId = DimLocation.dimLocationId 
        WHERE
            DimLocation.fLocation IN (SELECT fLocation FROM @LocationList )
            AND A.Ftxdate IS NOT NULL
            AND a.Ftxdate BETWEEN @FromDate and @ToDate 
        GROUP BY
            DimLocation.Flocation, 
            a.DimProviderID,
            a.dimSpecialtyId 
        UNION ALL
        SELECT
            X.fLocation,
            X.dimProviderId,
            SUM(X.eProd) AS fProdAmt,
            0 AS fWorkedDays,
            x.fSpecialty 
        FROM
            (
            SELECT 
                DimLocation.fLocation,
                b.dimProviderId, 
                b.dimPatientId, 
                b.ProductionAdjustmentAmount as eProd,
                B.dimSpecialtyId as fSpecialty
            FROM 
                (
                SELECT
                    fctProdAdj.dimLocationId,
                    fctProdAdj.dimProviderId,
                    fctProdAdj.dimPatientId,
                    fctProdAdj.ProductionAdjustmentAmount,
                    fctProdAdj.TxNumber,
                    fctProdAdj.fDate,
                    fctProdAdj.ftxdate,
                    -- fctProdAdj.FSuffix,
                    (CASE WHEN lkpProvider.RDH = 1
                          THEN ( SELECT DimSpecialities.dimSpecId
                                 FROM DimSpecialities
                                 WHERE DimSpecialities.fDesc = 'RDH' )
                          ELSE fctProdAdj.dimSpecialtyId
                     END ) AS dimSpecialtyId
                FROM
                    fctProdAdj 
                    INNER JOIN lkpProvider
                        ON fctProdAdj.dimProviderId = lkpProvider.dimlkpProviderId 
                ) as b
                INNER JOIN DimLocation
                    ON b.dimLocationId = DimLocation.dimLocationId 
            WHERE
                DimLocation.fLocation IN (SELECT fLocation FROM @LocationList )
                AND b.TxNumber <> 0
                AND b.fDate BETWEEN @FromDate AND @ToDate -- and FSuffix=0 
                and (B.fTxDate is null or B.fTxDate >= dateadd(year,-1,@FromDate)) 
            ) AS X
        GROUP BY 
            X.fLocation,
            X.dimProviderId,
            x.fSpecialty 
        ) AS Y
    GROUP BY 
        Y.Flocation,
        Y.DimProviderID ,
        y.fSpecialty 
    ) AS Z
GROUP BY 
    Z.Flocation,
    Z.fSpecialty
 ),
tOfficeDays AS
(
SELECT
    X.Flocation,
    X.fSpecialty,
    isnull(SUM(X.fOfficeDays),0) AS fOfficeDays 
FROM
    (
    SELECT
        DimLocation.fLocation, 
        a.DimProviderID, 
        a.dimSpecialtyId as fSpecialty,
        isnull(COUNT(DISTINCT a.Ftxdate),0) AS fOfficeDays
    FROM 
        (
        SELECT
            FctTxdxProdn.dimLocationId,
            FctTxdxProdn.DimProviderID,
            FctTxdxProdn.Ftxdate,
            (CASE WHEN lkpProvider.RDH = 1
                  THEN ( SELECT DimSpecialities.dimSpecId
                         FROM DimSpecialities
                         WHERE DimSpecialities.fDesc = 'RDH' )
                  ELSE FctTxdxProdn.dimSpecId
             END ) AS dimSpecialtyId 
        FROM
            FctTxdxProdn 
            INNER JOIN lkpProvider
                ON FctTxdxProdn.DimProviderID = lkpProvider.dimlkpProviderId 
        ) AS a
        INNER JOIN DimLocation
            ON a.dimLocationId = DimLocation.dimLocationId 
        INNER JOIN lkpProvider AS b
            ON A.DimProviderID = b.dimlkpProviderId 
    WHERE
        DimLocation.fLocation IN ( SELECT fLocation FROM @LocationList )
        AND A.Ftxdate IS NOT NULL
        AND a.Ftxdate BETWEEN @FromDate and @ToDate 
        and b.fOffProd = 0
    GROUP BY
        DimLocation.fLocation, 
        a.dimSpecialtyId,
        a.DimProviderID 
    ) AS X
GROUP BY
    X.Flocation,
    X.fSpecialty 
)
INSERT INTO @tbl 
SELECT
    DimGoal.fLocation,
    DimSpecialities.fType,
    DimSpecialities.fDesc as Specialty,
    ISNULL(tProduction.fProdAmt/CASE WHEN tProduction.fWorkedDays = 0
                                     THEN 1
                                     ELSE tProduction.fWorkedDays
                                END,0) AS fProductionByDrDays,
    ISNULL(DimGoal.fBProd / (CASE WHEN ISNULL(DimGoal.fDrDays,0) = 0
                                  THEN 1
                                  ELSE ISNULL(DimGoal.fDrDays,0)
                             END),0) AS fBudgetByDrDays,
    ISNULL(CASE WHEN ISNULL(tProduction.fProdAmt / tOfficeDays.fOfficeDays * DimGoal.fDrDays,0) = 0
                THEN DimGoal.fBProd
                ELSE tProduction.fProdAmt / tOfficeDays.fOfficeDays * DimGoal.fDrDays
           END,0) AS fProjection,
    ISNULL(DimGoal.fBProd,0) AS fBudget,
    isnull(tProduction.fProdAmt,0) AS fProduction,
    ISNULL(tProduction.fWorkedDays,0) AS fDrDays,
    isnull(DimGoal.fDrDays,0) as fGoalDrDays,
    isnull(tOfficeDays.fOfficeDays,0) as fOfficeDrDays
FROM
    DimGoal 
    INNER JOIN DimSpecialities
        ON DimGoal.fOrthoGen = DimSpecialities.ftype 
    LEFT JOIN tProduction
        ON DimSpecialities.dimSpecId = tProduction.fSpecialty AND
           DimGoal.fLocation = tProduction.Flocation 
    LEFT JOIN tOfficeDays
        ON DimSpecialities.dimSpecId = tOfficeDays.fSpecialty AND
           DimGoal.fLocation = tOfficeDays.Flocation 
WHERE
    DimGoal.fLocation IN ( SELECT fLocation FROM @LocationList) 
    AND DimGoal.fYear = YEAR(@FromDate) AND DimGoal.fMonth = MONTH(@FromDate)
ORDER BY 
    DimGoal.fLocation,
    DimSpecialities.fType ;
RETURN
END