SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 How to use this procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 11/20/2013 :  02:58:44  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 11/20/2013 :  04:02:22  Show Profile  Reply with Quote
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
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 11/20/2013 :  20:44:15  Show Profile  Reply with Quote
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.

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 11/20/2013 :  21:03:49  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 11/20/2013 :  22:42:20  Show Profile  Reply with Quote
Thank you so much. It work well.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/21/2013 :  01:15:40  Show Profile  Reply with Quote
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
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 11/21/2013 :  05:51:08  Show Profile  Reply with Quote
Thank you
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000