Dell, here is like it is look like:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: AY
-- Create date: 7/28/2011
-- To create Table for UC417 report
-- =========================================
ALTER PROCEDURE [dbo].[MFS_Prior_Prev_Month_Balance]
@Location nvarchar(50) = null
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
Begin
if @Location = 'DuPage' Delete from [Prior_Prev_Mnth_SelfPay_Balance_DuPage]
if @Location = 'Blue Island' Delete from [Prior_Prev_Mnth_SelfPay_Balance_BlueIsland]
end
if @Location = 'DuPage'
INSERT INTO Prior_Prev_Mnth_SelfPay_Balance_Dupage
([Location]
,ClientOID
,ClientID
,ClientFName
,ClientLName
,NbrOfOpenCC
,SelfPayBalance
,UnappliedPayment
,ClaimBalanceForward
,TotalBalance)
SELECT "MFS_V_Agency_Location_Current"."NAME_LINE_1" As Location,
"Client"."OID" As ClientOID,
"Client"."ID" As ClientID,
"Client"."FName" As ClientFName,
"Client"."LName" As ClientLName,
max("MFS_V_Number_of_Current_Open_CC_per_Client"."NumberOfOpenCc") as NbrOfOpenCC,
Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."SelfPayBal") as SelfPayBalance,
Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."UnapPayment") as UnappliedPayment,
Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."ClaimBalanceForwardSum") AS ClaimBalanceForward,
max((isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."SelfPayBal",0)
+
isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."UnapPayment", 0)+
isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."ClaimBalanceForwardSum",0))) As TotalBalance
FROM ((((."Client" "Client" LEFT OUTER JOIN
dbo."CostCtrGrp_Helper" "CostCtrGrp_Helper" ON
"Client"."OID"="CostCtrGrp_Helper"."Client_OID") LEFT OUTER JOIN
dbo."MFS_V_Number_of_Current_Open_CC_per_Client"
"MFS_V_Number_of_Current_Open_CC_per_Client"
ON "Client"."OID"="MFS_V_Number_of_Current_Open_CC_per_Client"."Client_OID") LEFT OUTER JOIN
dbo."MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"
"MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt" ON
"Client"."OID"="MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."Client_OID") LEFT OUTER JOIN
dbo."CostCtrGrp" "CostCtrGrp" ON
"CostCtrGrp_Helper"."CostCtrGrp_OID"="CostCtrGrp"."OID") LEFT OUTER JOIN
dbo."MFS_V_Agency_Location_Current" "MFS_V_Agency_Location_Current" ON
"CostCtrGrp"."AGENCY_LOCATION_MONIKER"="MFS_V_Agency_Location_Current"."OID"
Group by "MFS_V_Agency_Location_Current"."NAME_LINE_1",
"Client"."OID",
"Client"."ID",
"Client"."FName",
"Client"."LName"
having "MFS_V_Agency_Location_Current"."NAME_LINE_1" is not null
and "MFS_V_Agency_Location_Current"."NAME_LINE_1" = @Location
if @Location = 'Blue Island'
INSERT INTO Prior_Prev_Mnth_SelfPay_Balance_BlueIsland
([Location]
,ClientOID
,ClientID
,ClientFName
,ClientLName
,NbrOfOpenCC
,SelfPayBalance
,UnappliedPayment
,ClaimBalanceForward
,TotalBalance)
SELECT "MFS_V_Agency_Location_Current"."NAME_LINE_1" As Location,
"Client"."OID" As ClientOID,
"Client"."ID" As ClientID,
"Client"."FName" As ClientFName,
"Client"."LName" As ClientLName,
max("MFS_V_Number_of_Current_Open_CC_per_Client"."NumberOfOpenCc") as NbrOfOpenCC,
Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."SelfPayBal") as SelfPayBalance,
Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."UnapPayment") as UnappliedPayment,
Max("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."ClaimBalanceForwardSum") AS ClaimBalanceForward,
max((isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."SelfPayBal",0)
+
isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."UnapPayment", 0)+
isnull("MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."ClaimBalanceForwardSum",0))) As TotalBalance
FROM ((((."Client" "Client" LEFT OUTER JOIN
dbo."CostCtrGrp_Helper" "CostCtrGrp_Helper" ON
"Client"."OID"="CostCtrGrp_Helper"."Client_OID") LEFT OUTER JOIN
dbo."MFS_V_Number_of_Current_Open_CC_per_Client"
"MFS_V_Number_of_Current_Open_CC_per_Client"
ON "Client"."OID"="MFS_V_Number_of_Current_Open_CC_per_Client"."Client_OID") LEFT OUTER JOIN
dbo."MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"
"MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt" ON
"Client"."OID"="MFS_V_Prior_Prev_Mnth_SelfPay_ClmBalForw_UnplPmt"."Client_OID") LEFT OUTER JOIN
dbo."CostCtrGrp" "CostCtrGrp" ON
"CostCtrGrp_Helper"."CostCtrGrp_OID"="CostCtrGrp"."OID") LEFT OUTER JOIN
dbo."MFS_V_Agency_Location_Current" "MFS_V_Agency_Location_Current" ON
"CostCtrGrp"."AGENCY_LOCATION_MONIKER"="MFS_V_Agency_Location_Current"."OID"
Group by "MFS_V_Agency_Location_Current"."NAME_LINE_1",
"Client"."OID",
"Client"."ID",
"Client"."FName",
"Client"."LName"
having "MFS_V_Agency_Location_Current"."NAME_LINE_1" is not null
and "MFS_V_Agency_Location_Current"."NAME_LINE_1" = @Location
SET ANSI_WARNINGS ON
SET NOCOUNT OFF
RETURN
Thanks.
Alec.