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