January 6, 2012 at 2:01 pm
Hello All,
I have a code and I am trying to use Merge statement.
MERGE DBO.DASHBOARD as target
using(select @cut_date) as source (effective_Date)
when matched then
update bb
set bb.cut_date = a.cut_date
from dbo.dashboard bb
inner join
(select
@cut_date cut_date
,@invoice_gen invoice_gen
,@unclaimed_payment unclaimed_payment
,@payment_received payment_received
,@tdu_charge tdu_charge
,@coll_payment_received coll_payment_received
,@meter_active meter_active
,@meter_Cancelled meter_Cancelled
,@meter_churn meter_churn
,@meter_intransit meter_intransit
,ecp_tot_cte.tot_unstat
,ecp_tot_cte.active_unstat
,ecp_tot_cte.inactive_unstat
,ecp_tot_cte.tot_notdue
,ecp_tot_cte.active_notdue
,ecp_tot_cte.inactive_notdue
,ecp_tot_cte.tot_1_10
,ecp_tot_cte.active_1_10
,ecp_tot_cte.inactive_1_10
,ecp_tot_cte.tot_11_30
,ecp_tot_cte.active_11_30
,ecp_tot_cte.inactive_11_30
,ecp_tot_cte.tot_31_60
,ecp_tot_cte.act_31_60
,ecp_tot_cte.in_31_60
,ecp_tot_cte.tot_61_90
,ecp_tot_cte.act_61_90
,ecp_tot_cte.in_61_90
,ecp_tot_cte.tot_91_120
,ecp_tot_cte.act_91_120
,ecp_tot_cte.in_91_120
,ecp_tot_cte.tot_over_
,ecp_tot_cte.act_over_121
,ecp_tot_cte.in_over_121
,tot_cte.tot_unstat
,tot_cte.active_unstat
,tot_cte.inactive_unstat
,tot_cte.tot_notdue
,tot_cte.active_notdue
,tot_cte.inactive_notdue
,tot_cte.tot_1_10
,tot_cte.active_1_10
,tot_cte.inactive_1_10
,tot_cte.tot_11_30
,tot_cte.active_11_30
,tot_cte.inactive_11_30
,tot_cte.tot_31_60
,tot_cte.act_31_60
,tot_cte.in_31_60
,tot_cte.tot_61_90
,tot_cte.act_61_90
,tot_cte.in_61_90
,tot_cte.tot_91_120
,tot_cte.act_91_120
,tot_cte.in_91_120
,tot_cte.tot_over_
,tot_cte.act_over_121
,tot_cte.in_over_121
FROM ecp_tot_cte
CROSS JOIN tot_cte ) a
On a.cut_date=bb.effective_date
when not matched then
INSERT INTO dbo.dashboard (
[Effective_Date]
,[Invoice_gen]
,[unclaimed_payments]
,[Tot_Payments_received]
,[tdu_charge]
,[payments_received]
,[meter_cnt_active]
,[meter_cnt_cancelled]
,[meter_cnt_churn]
,[meter_cnt_intransit]
,[ag_tot_unstat]
,[ag_active_unstat]
,[ag_inactive_unstat]
,[ag_tot_notdue]
,[ag_active_notdue]
,[ag_inactive_notdue]
,[ag_tot_1_10]
,[ag_active_1_10]
,[ag_inactive_1_10]
,[ag_tot_11_30]
,[ag_active_11_30]
,[ag_inactive_11_30]
,[ag_tot_31_60]
,[ag_act_31_60]
,[ag_in_31_60]
,[ag_tot_61_90]
,[ag_act_61_90]
,[ag_in_61_90]
,[ag_tot_91_120]
,[ag_act_91_120]
,[ag_in_91_120]
,[ag_tot_over_121]
,[ag_act_over_121]
,[ag_in_over_121]
,[ecp_tot_unstat]
,[ecp_active_unstat]
,[ecp_inactive_unstat]
,[ecp_tot_notdue]
,[ecp_active_notdue]
,[ecp_inactive_notdue]
,[ecp_tot_1_10]
,[ecp_active_1_10]
,[ecp_inactive_1_10]
,[ecp_tot_11_30]
,[ecp_active_11_30]
,[ecp_inactive_11_30]
,[ecp_tot_31_60]
,[ecp_act_31_60]
,[ecp_in_31_60]
,[ecp_tot_61_90]
,[ecp_act_61_90]
,[ecp_in_61_90]
,[ecp_tot_91_120]
,[ecp_act_91_120]
,[ecp_in_91_120]
,[ecp_tot_over_121]
,[ecp_act_over_121]
,[ecp_in_over_121]
)
select
@cut_date
,@invoice_gen
,@unclaimed_payment
,@payment_received
,@tdu_charge
,@coll_payment_received
,@meter_active
,@meter_Cancelled
,@meter_churn
,@meter_intransit
,tot_cte.tot_unstat
,tot_cte.active_unstat
,tot_cte.inactive_unstat
,tot_cte.tot_notdue
,tot_cte.active_notdue
,tot_cte.inactive_notdue
,tot_cte.tot_1_10
,tot_cte.active_1_10
,tot_cte.inactive_1_10
,tot_cte.tot_11_30
,tot_cte.active_11_30
,tot_cte.inactive_11_30
,tot_cte.tot_31_60
,tot_cte.act_31_60
,tot_cte.in_31_60
,tot_cte.tot_61_90
,tot_cte.act_61_90
,tot_cte.in_61_90
,tot_cte.tot_91_120
,tot_cte.act_91_120
,tot_cte.in_91_120
,tot_cte.tot_over_
,tot_cte.act_over_121
,tot_cte.in_over_121
, ecp_tot_cte.tot_unstat
,ecp_tot_cte.active_unstat
,ecp_tot_cte.inactive_unstat
,ecp_tot_cte.tot_notdue
,ecp_tot_cte.active_notdue
,ecp_tot_cte.inactive_notdue
,ecp_tot_cte.tot_1_10
,ecp_tot_cte.active_1_10
,ecp_tot_cte.inactive_1_10
,ecp_tot_cte.tot_11_30
,ecp_tot_cte.active_11_30
,ecp_tot_cte.inactive_11_30
,ecp_tot_cte.tot_31_60
,ecp_tot_cte.act_31_60
,ecp_tot_cte.in_31_60
,ecp_tot_cte.tot_61_90
,ecp_tot_cte.act_61_90
,ecp_tot_cte.in_61_90
,ecp_tot_cte.tot_91_120
,ecp_tot_cte.act_91_120
,ecp_tot_cte.in_91_120
,ecp_tot_cte.tot_over_
,ecp_tot_cte.act_over_121
,ecp_tot_cte.in_over_121
FROM ecp_tot_cte
CROSS JOIN tot_cte
ecp_tot_cte and tot_cte are both CTE's which has some calculations. They are defined above the merge statement. Here I am trying to do is when the cut_date is matched update the records in the table. When not matched insert the records. Here I am getting lot of errors near the MERGE Statement. Please assist.
Thanks
January 6, 2012 at 2:16 pm
And the error messages are...????
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2012 at 2:18 pm
Incorrect syntax near bb
Incorrect syntax near a.
January 6, 2012 at 2:44 pm
USE [ESG]
GO
/****** Object: StoredProcedure [dbo].[usp_Dashboard] Script Date: 01/06/2012 15:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Dashboard]
AS
--TRUNCATE TABLE dbo.dashboard
/*Cut_Date*/
declare @cut_date date
--set @cut_date = cast(('30-NOV-2011') as date)
set @cut_date = cast(dateadd(d,-2,getdate()) as date)
/*Invoice Generated*/
declare @invoice_gen decimal(12,2)
set @invoice_gen = (
select SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) line_tot
from billing_payment
where TYPE_DESC != 'Payment')
-- where TYPE_DESC != 'Level Pay Contract'
-- and type_desc != 'Transfer to Payment Contract'
-- and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',
-- 'Returned Cash - Revenue - Non Regulated',
-- 'Returned Cash - Revenue - Regulated',
-- 'Cash - Revenue - Non Regulated',
-- 'Cash - Revenue - Regulated',
-- 'Reversed Cash - Revenue - Regulated',
-- 'Reversed Cash - Revenue - Non Regulated',
-- 'Deposit Charge - Non Regulated'))
/*Payments Received*/
declare @payment_received decimal(12,2)
set @payment_received = (
select -1.0 * ABS(round(SUM( (CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) ),2)) 'Payments Recieved'
from billing_payment
where TYPE_DESC = 'Payment')
/*Unclaimed Payments*/
declare @unclaimed_payment decimal(12,2)
set @unclaimed_payment = (
select SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 'unclaimed Payments'
from billing_payment
where (customer_tkn = 42372942
and CUST_STATEMENT_TKN is null
and CURRENT_STMT_DATE is null))
/*Payments_received --- Collections Phase*/
Declare @coll_payment_received decimal(12,2)
set @coll_payment_received= cast((@payment_received - @unclaimed_payment) as varchar(14));
/*Tdu charges*/
declare @tdu_charge decimal(12,2)
set @tdu_charge = (
select round(SUM(CALC_PRICE_AMT + price_tax_amt),2) 'tdu_charge'
from billing_payment
where TYPE_desc = 'Regular Bill')
/*Meter Count --- Active*/
declare @meter_Active int
set @meter_Active =
(select COUNT(*)as meter_cnt
from (
select --case when (x.flow_end_date <= x.recission_date) or ( x.flow_start_date is null and x.flow_end_Date is not null) then 'CANCELLED'
--when x.flow_end_date IS null and x.flow_start_date IS not null then 'ACTIVE'
--when (x.flow_end_date > x.recission_date) and x.flow_start_date IS not null and x.flow_end_date IS not null then 'CHURN'
--when x.flow_start_date is null then 'IN_TRANSIT'
--else null
--end status
case when x.flow_end_date <= x.recission_date then 'CANCELLED'
--or ( cca.flow_start_date is null and cca.flow_end_Date is not null) then 'CANCELLED'
when x.flow_end_date IS null and x.flow_start_date IS not null then 'ACTIVE'
when (x.flow_end_date > x.recission_date) then 'CHURN'
--and cca.flow_start_date IS not null and cca.flow_end_date IS not null then 'CHURN'
when x.flow_start_date is null and x.flow_end_date is null then 'IN_TRANSIT'
else null
end status
from
(select cal2.sale_Date,
cal2.recission_date,
cal2.flow_start_date,
cal2.flow_end_Date,
cal2.esiid,
ROW_NUMBER() over (partition by account_number order by esiid_end_date asc) rowcnt
--cs.flow_dir,
-- cs.pro_date,
-- cal2.flow_end_Date
from dbo.vclean_cust_account_lcd_cancel_switch cal2) x
-- left outer join dbo.cancel_switch cs on cal2.esiid = cs.esiid) x
where rowcnt = 1)y
where y.status = 'ACTIVE'
group by y.status
)
/*Meter Count --- Cancelled*/
Declare @meter_cancelled int
set @meter_cancelled=(
select COUNT(*)as meter_cnt
from (
select --case when (x.flow_end_date <= x.recission_date) or ( x.flow_start_date is null and x.flow_end_Date is not null) then 'CANCELLED'
--when x.flow_end_date IS null and x.flow_start_date IS not null then 'ACTIVE'
--when (x.flow_end_date > x.recission_date) and x.flow_start_date IS not null and x.flow_end_date IS not null then 'CHURN'
--when x.flow_start_date is null then 'IN_TRANSIT'
--else null
--end status
case when x.flow_end_date <= x.recission_date then 'CANCELLED'
--or ( cca.flow_start_date is null and cca.flow_end_Date is not null) then 'CANCELLED'
when x.flow_end_date IS null and x.flow_start_date IS not null then 'ACTIVE'
when (x.flow_end_date > x.recission_date) then 'CHURN'
--and cca.flow_start_date IS not null and cca.flow_end_date IS not null then 'CHURN'
when x.flow_start_date is null and x.flow_end_date is null then 'IN_TRANSIT'
else null
end status
from
(select cal2.sale_Date,
cal2.recission_date,
cal2.flow_start_date,
cal2.flow_end_Date,
cal2.esiid,
ROW_NUMBER() over (partition by account_number order by esiid_end_date asc) rowcnt
from dbo.clean_cust_account_lcd_cancel_switch cal2) x
where rowcnt = 1)y
WHERE y.status = 'CANCELLED'
group by y.status
)
/*Meter Count --- Churn*/
declare @meter_churn int
set @meter_churn =
(select COUNT(*)as meter_cnt
from (
select --case when (x.flow_end_date <= x.recission_date) or ( x.flow_start_date is null and x.flow_end_Date is not null) then 'CANCELLED'
--when x.flow_end_date IS null and x.flow_start_date IS not null then 'ACTIVE'
--when (x.flow_end_date > x.recission_date) and x.flow_start_date IS not null and x.flow_end_date IS not null then 'CHURN'
--when x.flow_start_date is null then 'IN_TRANSIT'
--else null
--end status
case when x.flow_end_date <= x.recission_date then 'CANCELLED'
--or ( cca.flow_start_date is null and cca.flow_end_Date is not null) then 'CANCELLED'
when x.flow_end_date IS null and x.flow_start_date IS not null then 'ACTIVE'
when (x.flow_end_date > x.recission_date) then 'CHURN'
--and cca.flow_start_date IS not null and cca.flow_end_date IS not null then 'CHURN'
when x.flow_start_date is null and x.flow_end_date is null then 'IN_TRANSIT'
else null
end status
from
(select cal2.sale_Date,
cal2.recission_date,
cal2.flow_start_date,
cal2.flow_end_Date,
cal2.esiid,
ROW_NUMBER() over (partition by account_number order by esiid_end_date asc) rowcnt
from dbo.clean_cust_account_lcd_cancel_switch cal2) x
where rowcnt = 1)y
WHERE y.status = 'CHURN'
group by y.status
)
/*Meter Count -- In Transit*/
declare @meter_intransit int
set @meter_intransit =
(select COUNT(*)as meter_cnt
from (
select --case when (x.flow_end_date <= x.recission_date) or ( x.flow_start_date is null and x.flow_end_Date is not null) then 'CANCELLED'
--when x.flow_end_date IS null and x.flow_start_date IS not null then 'ACTIVE'
--when (x.flow_end_date > x.recission_date) and x.flow_start_date IS not null and x.flow_end_date IS not null then 'CHURN'
--when x.flow_start_date is null then 'IN_TRANSIT'
--else null
--end status
case when x.flow_end_date <= x.recission_date then 'CANCELLED'
--or ( cca.flow_start_date is null and cca.flow_end_Date is not null) then 'CANCELLED'
when x.flow_end_date IS null and x.flow_start_date IS not null then 'ACTIVE'
when (x.flow_end_date > x.recission_date) then 'CHURN'
--and cca.flow_start_date IS not null and cca.flow_end_date IS not null then 'CHURN'
when x.flow_start_date is null and x.flow_end_date is null then 'IN_TRANSIT'
else null
end status
from
(select cal2.sale_Date,
cal2.recission_date,
cal2.flow_start_date,
cal2.flow_end_Date,
cal2.esiid,
ROW_NUMBER() over (partition by account_number order by esiid_end_date asc) rowcnt
from dbo.clean_cust_account_lcd_cancel_switch cal2) x
where rowcnt = 1)y
WHERE y.status = 'IN_TRANSIT'
group by y.status
)
/*CTE to store All Aging Measures*/
;with tot_cte (tot_unstat,active_unstat,inactive_unstat,tot_notdue,active_notdue,inactive_notdue,tot_1_10,active_1_10,inactive_1_10,tot_11_30,active_11_30,inactive_11_30,tot_31_60,act_31_60,in_31_60,tot_61_90,act_61_90,in_61_90,tot_91_120,act_91_120,in_91_120,tot_over_,act_over_121,in_over_121)
AS
(
select
-- UNSTATMENTED
SUM(fff.act_uns) + SUM(fff.inact_uns) as tot_unstat,
SUM(fff.act_uns) as active_unstat,
SUM(fff.inact_uns) as inactive_unstat,
-- NOT DUE
SUM(fff.act_notdue) + SUM(fff.inact_notdue) as tot_notdue,
SUM(fff.act_notdue) as active_notdue,
SUM(fff.inact_notdue) as inactive_notdue,
-- 1 to 10
SUM(fff.act_1_10) + SUM(fff.inact_1_10) as tot_1_10,
SUM(fff.act_1_10) as active_1_10,
SUM(fff.inact_1_10) as inactive_1_10,
-- 11 to 30
SUM(fff.act_11_30) + SUM(fff.inact_11_30) as tot_11_30,
SUM(fff.act_11_30) as active_11_30,
SUM(fff.inact_11_30) as inactive_11_30,
-- 31 to 60
SUM(fff.act_31_60) + SUM(fff.in_31_60) as tot_31_60,
SUM(fff.act_31_60) as act_31_60,
SUM(fff.in_31_60) as in_31_60,
-- 61 to 90
SUM(fff.act_61_90) + SUM(fff.in_61_90) as tot_61_90,
SUM(fff.act_61_90) as act_61_90,
SUM(fff.in_61_90) as in_61_90,
-- 91 to 120
SUM(fff.act_91_120) + SUM(fff.in_91_120) as tot_91_120,
SUM(fff.act_91_120) as act_91_120,
SUM(fff.in_91_120) as in_91_120,
-- 121 PLUS
SUM(fff.act_over_121) + SUM(fff.in_over_121) as tot_over_,
SUM(fff.act_over_121) as act_over_121,
SUM(fff.in_over_121) as in_over_121
from (
select
case when agr.status = 'ACTIVE' then agr.Unstatemented -- unstatement
else0
end act_uns,
case when agr.status = 'INACTIVE' thenagr.Unstatemented
else 0
end inact_uns,
case when agr.status = 'ACTIVE' then agr.current_acct -- not due
else 0
end act_notdue,
case when agr.status = 'INACTIVE' thenagr.current_acct
else0
end inact_notdue,
case when agr.status = 'ACTIVE' then agr.aged_1_10
else 0
end act_1_10,
case when agr.status = 'INACTIVE' then agr.aged_1_10
else 0
end inact_1_10,
case when agr.status = 'ACTIVE' then agr.aged_11_30
else 0
end act_11_30,
case when agr.status = 'INACTIVE' thenagr.aged_11_30
else 0
end inact_11_30,
case when agr.status = 'ACTIVE' then agr.aged_31_60
else0
end act_31_60,
case when agr.status = 'INACTIVE' thenagr.aged_31_60
else0
end in_31_60,
case when agr.status = 'ACTIVE' then agr.aged_61_90
else0
end act_61_90,
case when agr.status = 'INACTIVE' thenagr.aged_61_90
else0
end in_61_90,
case when agr.status = 'ACTIVE' then agr.aged_91_120
else0
end act_91_120,
case when agr.status = 'INACTIVE' thenagr.aged_91_120
else0
end in_91_120,
case when agr.status = 'ACTIVE' then agr.aged_121plus
else0
end act_over_121,
case when agr.status = 'INACTIVE' thenagr.aged_121plus
else0
end in_over_121
from (
SELECT x.account_number,
x.status,
SUM(CASE WHEN x.Arrears_Date IS NULL THEN x.Charge_Amt ELSE 0 END) Unstatemented,
SUM(CASE WHEN @cut_date - x.Arrears_Date < 17 THEN x.Charge_Amt ELSE 0 END) current_acct,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 17 AND 26 THEN x.Charge_Amt ELSE 0 END) AS aged_1_10,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 27 AND 46 THEN x.Charge_Amt ELSE 0 END) AS aged_11_30,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 47 AND 76 THEN x.Charge_Amt ELSE 0 END) AS aged_31_60,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 77 AND 106 THEN x.Charge_Amt ELSE 0 END) AS aged_61_90,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 107 AND 136 THEN x.Charge_Amt ELSE 0 END) AS aged_91_120,
SUM(CASE WHEN @cut_date - x.Arrears_Date >= 137 THEN x.Charge_Amt ELSE 0 END) AS aged_121plus,
SUM(x.charge_amt) as tot
FROM (
select agg.ACCOUNT_NUMBER,
agg.Arrears_Date,
agg.Charge_Amt,
ea.flow_start_DATE as CONFIRMED_START_DATE,
ea.flow_end_DATE as CONFIRMED_DROP_DATE,
case
when ea.flow_start_DATE is not null and ea.flow_end_DATE IS null then 'ACTIVE'
else 'INACTIVE'
end status
from
dbo.vclean_cust_account_lcd_cancel_switch ea
left join
(select x.account_number
,x.arrears_Date
,x.charge_amt + x.payment_amt charge_amt
from (
SELECT bc.ACCOUNT_NUMBER
,ac.Arrears_Date
,SUM(case when bc.type_Desc != 'Payment' then bc.Calc_Price_Amt + bc.Price_Tax_Amt + bc.Adjustment_Amt
else 0
end ) Charge_Amt
-- ,SUM(case when TYPE_DESC != 'Level Pay Contract'
--and type_desc != 'Transfer to Payment Contract'
--and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',
-- 'Returned Cash - Revenue - Non Regulated',
-- 'Returned Cash - Revenue - Regulated',
-- 'Cash - Revenue - Non Regulated',
-- 'Cash - Revenue - Regulated',
-- 'Reversed Cash - Revenue - Regulated',
-- 'Reversed Cash - Revenue - Non Regulated',
-- 'Deposit Charge - Non Regulated')
-- then bc.Calc_Price_Amt + bc.Price_Tax_Amt + bc.Adjustment_Amt
-- else 0
-- end ) Charge_Amt
,sum(case when bc.type_Desc = 'Payment' then bc.adjustment_amt
else 0
end) payment_amt
FROM Billing_Charge bc
left outer JOIN ( -- determine oldest statment date
SELECT ACCOUNT_NUMBER,
Account_Charge_Tkn,
MIN(Current_Stmt_Date) Arrears_Date
FROM Billing_Charge
where current_stmt_DATE is not null
GROUP BY ACCOUNT_NUMBER, Account_Charge_Tkn
) ac
ON ac.ACCOUNT_NUMBER = bc.ACCOUNT_NUMBER
AND ac.Account_Charge_Tkn = bc.Account_Charge_Tkn
GROUP BY bc.account_number, ac.Arrears_Date) x ) agg
--where x.Charge_Amt + x.payment_amt > 0.01 )agg
on ea.account_number = agg.account_number
where agg.account_number is not null )x
GROUP BY x.ACCOUNT_NUMBER,x.status )agr) fff
)
/*CTE to store all ECP Measures*/
, ecp_tot_cte (tot_unstat,active_unstat,inactive_unstat,tot_notdue,active_notdue,inactive_notdue,tot_1_10,active_1_10,inactive_1_10,
tot_11_30,active_11_30,inactive_11_30,tot_31_60,act_31_60,in_31_60,tot_61_90,act_61_90,in_61_90,tot_91_120,act_91_120,in_91_120,tot_over_,
act_over_121,in_over_121)
AS
(
select
-- UNSTATMENTED
SUM(fff.act_uns) + SUM(fff.inact_uns) as tot_unstat,
SUM(fff.act_uns) as active_unstat,
SUM(fff.inact_uns) as inactive_unstat,
-- NOT DUE
SUM(fff.act_notdue) + SUM(fff.inact_notdue) as tot_notdue,
SUM(fff.act_notdue) as active_notdue,
SUM(fff.inact_notdue) as inactive_notdue,
-- 1 to 10
SUM(fff.act_1_10) + SUM(fff.inact_1_10) as tot_1_10,
SUM(fff.act_1_10) as active_1_10,
SUM(fff.inact_1_10) as inactive_1_10,
-- 11 to 30
SUM(fff.act_11_30) + SUM(fff.inact_11_30) as tot_11_30,
SUM(fff.act_11_30) as active_11_30,
SUM(fff.inact_11_30) as inactive_11_30,
-- 31 to 60
SUM(fff.act_31_60) + SUM(fff.in_31_60) as tot_31_60,
SUM(fff.act_31_60) as act_31_60,
SUM(fff.in_31_60) as in_31_60,
-- 61 to 90
SUM(fff.act_61_90) + SUM(fff.in_61_90) as tot_61_90,
SUM(fff.act_61_90) as act_61_90,
SUM(fff.in_61_90) as in_61_90,
-- 91 to 120
SUM(fff.act_91_120) + SUM(fff.in_91_120) as tot_91_120,
SUM(fff.act_91_120) as act_91_120,
SUM(fff.in_91_120) as in_91_120,
-- 121 PLUS
SUM(fff.act_over_121) + SUM(fff.in_over_121) as tot_over_,
SUM(fff.act_over_121) as act_over_121,
SUM(fff.in_over_121) as in_over_121
from (
select
case when agr.status = 'ACTIVE' then agr.Unstatemented -- unstatement
else 0
end act_uns,
case when agr.status = 'INACTIVE' then agr.Unstatemented
else 0
end inact_uns,
case when agr.status = 'ACTIVE' then agr.current_acct -- not due
else 0
end act_notdue,
case when agr.status = 'INACTIVE' then agr.current_acct
else 0
end inact_notdue,
case when agr.status = 'ACTIVE' then agr.aged_1_10
else 0
end act_1_10,
case when agr.status = 'INACTIVE' then agr.aged_1_10
else 0
end inact_1_10,
case when agr.status = 'ACTIVE' then agr.aged_11_30
else 0
end act_11_30,
case when agr.status = 'INACTIVE' then agr.aged_11_30
else 0
end inact_11_30,
case when agr.status = 'ACTIVE' then agr.aged_31_60
else 0
end act_31_60,
case when agr.status = 'INACTIVE' then agr.aged_31_60
else 0
end in_31_60,
case when agr.status = 'ACTIVE' then agr.aged_61_90
else 0
end act_61_90,
case when agr.status = 'INACTIVE' then agr.aged_61_90
else 0
end in_61_90,
case when agr.status = 'ACTIVE' then agr.aged_91_120
else 0
end act_91_120,
case when agr.status = 'INACTIVE' then agr.aged_91_120
else 0
end in_91_120,
case when agr.status = 'ACTIVE' then agr.aged_121plus
else 0
end act_over_121,
case when agr.status = 'INACTIVE' then agr.aged_121plus
else 0
end in_over_121
from (
SELECT x.account_number,
x.status,
SUM(CASE WHEN x.Arrears_Date IS NULL THEN x.Charge_Amt ELSE 0 END) Unstatemented,
-- SUM(CASE WHEN CAST('04-DEC-2011' AS DATE) - x.Arrears_Date < 17 THEN x.Charge_Amt ELSE 0 END) current_acct,
SUM(CASE WHEN @cut_date - x.Arrears_Date < 17 THEN x.Charge_Amt ELSE 0 END) current_acct,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 17 AND 26 THEN x.Charge_Amt ELSE 0 END) AS aged_1_10,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 27 AND 46 THEN x.Charge_Amt ELSE 0 END) AS aged_11_30,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 47 AND 76 THEN x.Charge_Amt ELSE 0 END) AS aged_31_60,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 77 AND 106 THEN x.Charge_Amt ELSE 0 END) AS aged_61_90,
SUM(CASE WHEN @cut_date - x.Arrears_Date BETWEEN 107 AND 136 THEN x.Charge_Amt ELSE 0 END) AS aged_91_120,
SUM(CASE WHEN @cut_date - x.Arrears_Date >= 137 THEN x.Charge_Amt ELSE 0 END) AS aged_121plus,
SUM(x.charge_amt) as tot
FROM (
select ea.ACCOUNT_NUMBER,
ea.current_stmt_date as Arrears_Date,
ea.line_total as charge_amt,
ea.flow_start_date as CONFIRMED_START_DATE,
ea.flow_end_date as CONFIRMED_DROP_DATE,
case
when ea.flow_START_DATE is not null and ea.flow_end_DATE IS null then 'ACTIVE'
else 'INACTIVE'
end status
from dbo.ecp_data ea
join (select --balance bue by account
x.account_number,
x.Charge_Amt + x.payment_amt balance
from (
select bc.account_number
,SUM(case when bc.type_Desc != 'Payment' then bc.Calc_Price_Amt + bc.Price_Tax_Amt + bc.Adjustment_Amt
else 0
end ) Charge_Amt
-- ,SUM(case when TYPE_DESC != 'Level Pay Contract'
--and type_desc != 'Transfer to Payment Contract'
--and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',
-- 'Returned Cash - Revenue - Non Regulated',
-- 'Returned Cash - Revenue - Regulated',
-- 'Cash - Revenue - Non Regulated',
-- 'Cash - Revenue - Regulated',
-- 'Reversed Cash - Revenue - Regulated',
-- 'Reversed Cash - Revenue - Non Regulated',
-- 'Deposit Charge - Non Regulated')
-- then bc.Calc_Price_Amt + bc.Price_Tax_Amt + bc.Adjustment_Amt
-- else 0
-- end ) Charge_Amt
,sum(case when bc.type_Desc = 'Payment' then bc.adjustment_amt
else 0
end) payment_amt
from dbo.billing_charge bc
group by bc.account_number ) x
--where x.Charge_Amt + x.payment_amt > 0.01
) deadbeat
on deadbeat.account_number = ea.account_number)x
GROUP BY x.ACCOUNT_NUMBER,x.status )agr) fff
)
/*Inserting all the data into a Table Dashboard*/
INSERT INTO dbo.dashboard (
[Effective_Date]
,[Invoice_gen]
,[unclaimed_payments]
,[Tot_Payments_received]
,[tdu_charge]
,[payments_received]
,[meter_cnt_active]
,[meter_cnt_cancelled]
,[meter_cnt_churn]
,[meter_cnt_intransit]
,[ag_tot_unstat]
,[ag_active_unstat]
,[ag_inactive_unstat]
,[ag_tot_notdue]
,[ag_active_notdue]
,[ag_inactive_notdue]
,[ag_tot_1_10]
,[ag_active_1_10]
,[ag_inactive_1_10]
,[ag_tot_11_30]
,[ag_active_11_30]
,[ag_inactive_11_30]
,[ag_tot_31_60]
,[ag_act_31_60]
,[ag_in_31_60]
,[ag_tot_61_90]
,[ag_act_61_90]
,[ag_in_61_90]
,[ag_tot_91_120]
,[ag_act_91_120]
,[ag_in_91_120]
,[ag_tot_over_121]
,[ag_act_over_121]
,[ag_in_over_121]
,[ecp_tot_unstat]
,[ecp_active_unstat]
,[ecp_inactive_unstat]
,[ecp_tot_notdue]
,[ecp_active_notdue]
,[ecp_inactive_notdue]
,[ecp_tot_1_10]
,[ecp_active_1_10]
,[ecp_inactive_1_10]
,[ecp_tot_11_30]
,[ecp_active_11_30]
,[ecp_inactive_11_30]
,[ecp_tot_31_60]
,[ecp_act_31_60]
,[ecp_in_31_60]
,[ecp_tot_61_90]
,[ecp_act_61_90]
,[ecp_in_61_90]
,[ecp_tot_91_120]
,[ecp_act_91_120]
,[ecp_in_91_120]
,[ecp_tot_over_121]
,[ecp_act_over_121]
,[ecp_in_over_121]
)
select
@cut_date
,@invoice_gen
,@unclaimed_payment
,@payment_received
,@tdu_charge
,@coll_payment_received
,@meter_active
,@meter_Cancelled
,@meter_churn
,@meter_intransit
,tot_cte.tot_unstat
,tot_cte.active_unstat
,tot_cte.inactive_unstat
,tot_cte.tot_notdue
,tot_cte.active_notdue
,tot_cte.inactive_notdue
,tot_cte.tot_1_10
,tot_cte.active_1_10
,tot_cte.inactive_1_10
,tot_cte.tot_11_30
,tot_cte.active_11_30
,tot_cte.inactive_11_30
,tot_cte.tot_31_60
,tot_cte.act_31_60
,tot_cte.in_31_60
,tot_cte.tot_61_90
,tot_cte.act_61_90
,tot_cte.in_61_90
,tot_cte.tot_91_120
,tot_cte.act_91_120
,tot_cte.in_91_120
,tot_cte.tot_over_
,tot_cte.act_over_121
,tot_cte.in_over_121
, ecp_tot_cte.tot_unstat
,ecp_tot_cte.active_unstat
,ecp_tot_cte.inactive_unstat
,ecp_tot_cte.tot_notdue
,ecp_tot_cte.active_notdue
,ecp_tot_cte.inactive_notdue
,ecp_tot_cte.tot_1_10
,ecp_tot_cte.active_1_10
,ecp_tot_cte.inactive_1_10
,ecp_tot_cte.tot_11_30
,ecp_tot_cte.active_11_30
,ecp_tot_cte.inactive_11_30
,ecp_tot_cte.tot_31_60
,ecp_tot_cte.act_31_60
,ecp_tot_cte.in_31_60
,ecp_tot_cte.tot_61_90
,ecp_tot_cte.act_61_90
,ecp_tot_cte.in_61_90
,ecp_tot_cte.tot_91_120
,ecp_tot_cte.act_91_120
,ecp_tot_cte.in_91_120
,ecp_tot_cte.tot_over_
,ecp_tot_cte.act_over_121
,ecp_tot_cte.in_over_121
FROM ecp_tot_cte
CROSS JOIN tot_cte
Here is my entire stored procedure. Here I am trying to do is insert the record for a particular cut date into a table called dashboard. Now I need to do is if a specific cut date already exists in the table dashboard this stored procedure should update the records and if the cut date doesnt exist in the table insert the records. Please assist me what I need to add to this stored procedure to accomplish this.
Thanks
January 6, 2012 at 8:40 pm
Read how a merge is supposed to be used here.
Your update statement is wrong for starters. The updates and inserts are inherently performed against the target .... whereas you're just sticking a stand-alone update statement complete with joins in that section.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply