I have to insert all the data as it is in the same table(MYTABLE) except changing some fields on basis of some conditions:
1. ifPaidAmount>0 && CurrentInstallment<TotalInstallment then
CurrentInstallment=CurrentInstallment+1
2. In the MonthYear field I am having data in formate(month/year)ex. 01/2012, 11/2012 ....
So I have to insert data by incrementing month and year. for example:
if currentdata is 11/2012 then next data will be 12/2012
But next will be 01/2013
I have to select all the records which belongs to previous month(through MonthYear field ) and put checking & changes on each record of the selected data and then insert them into same table(MYTABLE).
Here is function code you can use to add one month later to given date/year combination
create function addMonth(@s varchar(10)) returns varchar(10)
begin
declare @d as date
set @d = substring(@s,4,4)+ substring(@s,1,2) + '01'
return substring(replace(convert(nvarchar(10),dateadd(mm,1,@d),105),'-','/'),4,7)
end
you will use this sql function in following insert statement
insert into mytable (
col1, col2, col3,
TotalInstallments, CurrentInstallment, PaidAmount, MonthYear
)
select
col1, col2, col3,
TotalInstallments,
case when ( PaidAmount > 0 and CurrentInstallment < TotalInstallment )
then CurrentInstallment+1
else CurrentInstallment
end,
CurrentInstallment,
PaidAmount,
dbo.addMonth(MonthYear)
from mytable
Please test above function and insert statement before you use on a productive environment
CREATE OR REPLACE
PROCEDURE TRANSFERDATATONEXTMONTH(
CUSTOMERID_var IN VARCHAR2,
MONTHYEAR_var IN VARCHAR2,
MESSAGE_var OUT VARCHAR2 )
AS
BEGIN
insert into your_table
(col1, col2, col3...current_installment, MonthYear)
select col1, col2, col3..,
CASE
when paidamount > 0 and current_installment < total_installment then
current_installment +1
else
current_installment
END,
GETMonthYear(MonthYear) where CUSTOMERID=CUSTOMERID_var and MONTHYEAR=MONTHYEAR_var ;
COMMIT;
MESSAGE_VAR:='Success';
END TRANSFERDATATONEXTMONTH;
GETMONTH() is a function which returns the next monthyear body is as following:
create or replace
FUNCTION GETMonthYear(
PAYMONTHYEAR_VAR IN VARCHAR2 )
RETURN VARCHAR2
AS
pmonth VARCHAR(2);
pyear VARCHAR(4);
BEGIN
pmonth :=SUBSTR(PAYMONTHYEAR_VAR,1,2);
pyear :=SUBSTR(PAYMONTHYEAR_VAR,4,4);
IF(pmonth='12') THEN
pmonth:='01';
pyear :=(TO_NUMBER(pyear) +1);
ELSE
pmonth:=TO_NUMBER(pmonth)+1;
if(TO_NUMBER(PMONTH)<10) then
pmonth:='0'||PMONTH;
End if;
END IF;
RETURN pmonth||'/'||pyear;
END GETMonthYear;
Every thing is working fine but some strange result as: when i run my Procedure TRANSFERDATATONEXTMONTH
1. by Passing Value as : CUSTOMERID_var ='ABX101' and MONTHYEAR_var='12/2012' it insurts 5 rows which is correct , since I have 5 records where CUSTOMERID='ABX101' and MONTHYEAR='12/2012' and new 5 rows has CUSTOMERID='ABX101' and MONTHYEAR='01/2013' (all other values are as expected) 2. now when i again run by passing values: CUSTOMERID='ABX101' and MONTHYEAR='01/2013' it inserts 10 records(just double ) and new records has value CUSTOMERID='ABX101' and MONTHYEAR='02/2013' (while on the basis of condition CUSTOMERID='ABX101' and MONTHYEAR='01/2013' i have in my table only 5 records)
and all records are duplicate. Some times it inserts three times , while on condition basis it should no. What is happening?
Why it is inserting double of records while i have only 5 records on given condition? Am I missing some thing? Please.
mishra.bhupe...
Participant
1598 Points
378 Posts
Insert Data in Same table
Dec 21, 2012 06:03 AM|LINK
I have to write a stored procedure to Insert Data into a table say MYTABLE ,having structure as:
Col1 Col2 Col3 ................ TotalInstallments CurrentInstallment PaidAmount MonthYear
I have to insert all the data as it is in the same table(MYTABLE) except changing some fields on basis of some conditions:
1. if PaidAmount>0 && CurrentInstallment<TotalInstallment then
CurrentInstallment=CurrentInstallment+1
2. In the MonthYear field I am having data in formate(month/year)ex. 01/2012, 11/2012 ....
So I have to insert data by incrementing month and year. for example:
if currentdata is 11/2012 then next data will be 12/2012
But next will be 01/2013
I have to select all the records which belongs to previous month(through MonthYear field ) and put checking & changes on each record of the selected data and then insert them into same table(MYTABLE).
How to achive that?
Thanks.
eralper
Contributor
6048 Points
971 Posts
Re: Insert Data in Same table
Dec 21, 2012 07:00 AM|LINK
Hi mishra,
Here is function code you can use to add one month later to given date/year combination
you will use this sql function in following insert statement
Please test above function and insert statement before you use on a productive environment
SQL Server 2012
mishra.bhupe...
Participant
1598 Points
378 Posts
Re: Insert Data in Same table
Dec 26, 2012 06:27 AM|LINK
I did it in this way:
GETMONTH() is a function which returns the next monthyear body is as following:
Every thing is working fine but some strange result as:
when i run my Procedure TRANSFERDATATONEXTMONTH
1. by Passing Value as : CUSTOMERID_var ='ABX101' and MONTHYEAR_var='12/2012' it insurts 5 rows
which is correct , since I have 5 records where CUSTOMERID='ABX101' and MONTHYEAR='12/2012' and
new 5 rows has CUSTOMERID='ABX101' and MONTHYEAR='01/2013' (all other values are as expected)
2. now when i again run by passing values: CUSTOMERID='ABX101' and MONTHYEAR='01/2013' it inserts 10 records(just double )
and new records has value CUSTOMERID='ABX101' and MONTHYEAR='02/2013' (while on the basis of condition CUSTOMERID='ABX101' and MONTHYEAR='01/2013' i have in my table only 5 records)
and all records are duplicate. Some times it inserts three times , while on condition basis it should no. What is happening?
Why it is inserting double of records while i have only 5 records on given condition? Am I missing some thing?
Please.
eralper
Contributor
6048 Points
971 Posts
Re: Insert Data in Same table
Dec 26, 2012 02:11 PM|LINK
Hi mishra,
Just remove the INSER comand in the SP and useit only as as SELECT statement
The dublicated values will again display, so you can make a better guess why it is 10 times this time instead of 5
It must be related with 12th month or adding 0 to months less than 10
SQL Server 2012
NadeemZee
Participant
942 Points
178 Posts
Re: Insert Data in Same table
Dec 26, 2012 02:19 PM|LINK
Hi when ever you increament date i.e(formatted mm/yyyy) your date increamented not month
so you have to get monthpart and increment that part then it working fine 12/1012 will be next 01/2013
Do FEAR (Face Everything And Rise)
Please mark as Answer if my post helps you..!