Have to replace the data when there is a change in the calculation

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  sql server   » Have to replace the data when there is a change in the calculation

Have to replace the data when there is a change in the calculation

Topic RSS Feed

Posts under the topic: Have to replace the data when there is a change in the calculation

Posted: 12/15/2011

Jedi Youngling 74  points  Jedi Youngling
  • Joined on: 11/28/2011
  • Posts: 27

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Dashboard_test]

AS



/*Cut_Date*/

declare @cut_date date
set @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_tot
from billing_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 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) * -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_payment
where TYPE_desc = 'Regular Bill')

Insert into dbo.test

select @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

2011-12-14     500  600  700   800  900

2011-12-14    1200   600  700  800  900

How to achieve this.

Thanks for any help.

 


Posted: 1/6/2012

Padawan 492  points  Padawan
  • Joined on: 6/7/2011
  • Posts: 41

update dbo.test

set 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;

 


Page 1 of 1 (2 items)