Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Arunavally
Yak Posting Veteran
58 Posts |
 Posted - 2013-11-20 : 02:58:44
|
Hai,
I used some calculation for all id. After this calculation all value insert into table.
But this is each day inserted once and remaining times its update.
I knw how to insert.But i dont knw how to check for insert or update.
This is my procedure
ALTER procedure [dbo].[insert_cutoff]
@memberid varchar(11),
@LeftAmt money,
@RightAmt money,
@Cutoff money,
@TDS money,
@SC money,
@Total money,
@Date varchar(20),
@camt money,
@pleg varchar(10)
as
begin
declare @datechk varchar(20)
select @datechk=Date from Cutoff where MemberId=@memberid
if @datechk!=@Date or @datechk='' or @datechk is null
--if @datechk != @Date
insert into cutoff(MemberId,LeftAmt,RightAmt,Cutoffpay,TDS,SC,Total,Date,CarryAmt,PowerLeg) values(@memberid,@LeftAmt,@RightAmt,@Cutoff,@TDS,@SC,@total,@Date,@camt,@pleg)
--else
update Cutoff set LeftAmt=@LeftAmt,RightAmt=@RightAmt,CutoffPay=@Cutoff,TDS=@TDS,SC=@SC,Total=@Total,CarryAmt=@camt,PowerLeg=@pleg where MemberId=@memberid and Date=@Date
end
....
This procedure not work correct format. This procedure executed where top up is done. Each day allowed lot of topup to id.
Kindly help me.
Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-20 : 04:02:22
|
I dont understand your requirement. Please post some sample data and explain what you want
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
 |
|
|
Arunavally
Yak Posting Veteran
58 Posts |
Posted - 2013-11-20 : 20:44:15
|
Thank you. I resolved this issue.
I have other issue. I want to select information from table between from 20th date to 30th date. using date only not month or year.
I used this querey
select MemberId, PlanAmt,Total,Date,ClosedDate, DAY(date) as du from Plan_Payment where du between '20' and '30'
I got error in 'du' is invalid column.
Can i use this format. or not. Kindly tel me how to reframe it.
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-11-20 : 21:03:49
|
you can't use the column alias in the WHERE clause
where du between '20' and '30'
where DAY(date) between 20 and 30
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
Arunavally
Yak Posting Veteran
58 Posts |
Posted - 2013-11-20 : 22:42:20
|
| Thank you so much. It work well. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-21 : 01:15:40
|
otherwise you need to do like this
select *
from
(
select MemberId, PlanAmt,Total,Date,ClosedDate, DAY(date) as du
from Plan_Payment
)t
where du between '20' and '30'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
Arunavally
Yak Posting Veteran
58 Posts |
Posted - 2013-11-21 : 05:51:08
|
| Thank you |
|
|
|
|
|
|
|
|
How to use this procedure