• I don't have time right now to do anything real with the code, but if anyone is interested I have reformatted the code to make it a little bit easier to figure out what is going on the code.

    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