Calculating queries

  • When I run this I get a error

     

    Server: Msg 245, Level 16, State 1, Line 1

    Syntax error converting the nvarchar value '5.209' to a column of data type int.

     

    I am trying to take output from temp table where # of records and information will vary and lock in a density reading for every .5 inches through a block

    If there is a better approach I am all ears.

     

    Create table [test thickness].dbo.#dengrad

            (Block_id nvarchar (50)

            ,density float

            ,AVG_THICK real

            ,total float)

    go

     

    DECLARE @blkid AS int

    SET @blkid = 216178  -- will be user defined

     

     

    insert into #dengrad

    select  BLOCK_ID

                ,DENSITY

                ,AVG_THICK

                ,(select sum(AVG_THICK)

                             from dbo.THICKNESS_TABLE y

                            where y.[TIME]  <= x.[TIME]

                            and y.BLOCK_ID = x.BLOCK_ID

                            and BLOCK_SIDE like 'bottom')

                            as total

    from dbo.THICKNESS_TABLE x

    where BLOCK_ID like @blkid and BLOCK_SIDE like 'bottom'

     

    union

     

     

    select  BLOCK_ID

                ,DENSITY

                ,AVG_THICK

                ,(select 23-sum(AVG_THICK)

                             from dbo.THICKNESS_TABLE y

                            where y.[TIME]  <= x.[TIME]

                            and y.BLOCK_ID = x.BLOCK_ID

                            and BLOCK_SIDE like 'top')

                            as total

    from dbo.THICKNESS_TABLE x

    where BLOCK_ID like @blkid and BLOCK_SIDE like 'top'

     

    go

     

    select

            (select avg (density)

            from #dengrad

            where total between 0 and .75

            group by block_id ) as '0.5'

            ,(select avg (density)

            from #dengrad

            where total between .75 and .25

            group by block_id ) as '1'

    from #dengrad

     

  • This was removed by the editor as SPAM

  • You cannot use like with an integer value is one problem I see.  You are comparing BLOCK Like @BlkId which is int so that is not going to work.  Then in you final select you eed to change the between 0 and .75 to between 0.0 and 0.75.  Because the 0 without the decimal is an int not a decimal/Float/Real

  • Thank you for the help both on this post and my previous one the sql part of the project is done for now

     

    Cory Lee McRae

Viewing 4 posts - 1 through 3 (of 3 total)

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