ISNULL and Count using SQL SERVER 2005

Question

Post reply

ISNULL and Count using SQL SERVER 2005

Mahathi

Old Hand

Points: 320
More actions
September 15, 2008 at 10:53 pm

#197857

Hi
I have a requirement such that
I should insert a new record if there is no entry for a particular column for the day and update the record if already there exists a record for the same.
For this I used a count column in the table. If the count is null i am inserting the record into the database. If not null I update the count value for the record. For this I am using a stored procedur e to get the count value and the stored procedure is
ALTER proc [dbo].[getMaxval](@AllocId bigint,@Cnt int output)
as
select @Cnt=max(Cnt)+1,@Cnt=COUNT(ISNULL(Cnt,0)) from AllocationVisit where AllocId=@AllocId
Here AllocId is the column for which I need to check the entry into the database.
The problem is that if the value of the count is null i am able to insert value '0' for the count. After that the value is updated to 1 also.
But after the count is incremented to 1 for the rest of the entried I am getting only the value '1' for the same AllocId.
Am I clear in my doubt. If not please let me know. I'll explain it in a more clearer way.
Thank you in advance for any help provided.
Regards
Mahathi.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 1

arjun.tewari

Ten Centuries

Points: 1013

September 16, 2008 at 12:13 am

#872139

please provide the table schema

Mahathi Old Hand Points: 320 More actions · Answer 2

the table schema is as follows:

AllocId bigint

VisitTime datetime

Cnt int

LastUpdate datetime

we'll get the AllocId using the session object.

Regards

Mahathi.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

ALTER proc dbo.getMaxval

(

@AllocId bigint,

@Cnt int output

)

as

set nocount on

select@cnt = max(cnt)

fromAllocationVisit

whereAllocId = @AllocId

set @cnt = coalesce(@cnt, 0) + 1

N 56°04'39.16"
E 12°55'05.25"

Mahathi Old Hand Points: 320 More actions · Answer 4

Thanks alot. This works correctly.

Regards,

Mahathi.

Mahathi Old Hand Points: 320 More actions · Answer 5

how to get count only for today's date? That is if there is no record for the present day, even if there is for previous days, then i should get the count for AllocId as '0'. I can use the VisitTime in the where condition. But how to get today's date.

Regards

Mahathi.