Posted: 12/15/2011
Hello All,
I have stored procedure like this
USE [rlk_test]GO/****** Object: StoredProcedure [dbo].[usp_Dashboard] Script Date: 12/15/2011 09:21:00 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[usp_Dashboard_test]AS/*Cut_Date*/declare @cut_date dateset @cut_date = DATEADD(d,-1,getdate())/*Invoice Generated*/declare @invoice_gen decimal(12,2)set @invoice_gen = (select SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) line_totfrom billing_paymentwhere 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 round(SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT),2) 'Payments Recieved'from billing_paymentwhere 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_paymentwhere (customer_tkn = 42372942and CUST_STATEMENT_TKN is nulland 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) * -1 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_paymentwhere TYPE_desc = 'Regular Bill')Insert into dbo.testselect @cut_date,@invoice_gen,@payments_received,@unclaimed_payments,@coll_payments_received,@tdu_charges
If I execute this procedure and query the table I will get the output as
2011-12-14 500 600 700 800 900
Suppose if I have some change in one of the calculations(500 ---> 1200) for the same date and If i execute the store procedure again it should give me the result as
2011-12-14 1200 600 700 800 900
So it should overwrite the existing the data.It should not add an other row like this
How to achieve this.
Thanks for any help.
Posted: 1/6/2012
update dbo.testset invoice_gen = @invoice_gen
,payments_received = @payments_received
,unclaimed_payments = @unclaimed_payments
,col_payments_received = @coll_payments_received
,tdu_charges = @tdu_charges
where cut_date = @cut_date;