Post reply

ISNULL and Count using SQL SERVER 2005

  • 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.

  • September 16, 2008 at 12:13 am

    #872139

    please provide the table schema

  • September 16, 2008 at 12:20 am

    #872142

    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.

  • September 16, 2008 at 12:23 am

    #872144

    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"

  • September 16, 2008 at 12:44 am

    #872151

    Thanks alot. This works correctly.

    Regards,

    Mahathi.

  • September 16, 2008 at 1:04 am

    #872160

    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.

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

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