Need help with calculation

  • Hello,

    I have a table called 'tblRCStudentGrades' in which there are four VarChar(2)fields that can contain these values: 1-5, NULL, 'N'.

    I need to arive at a single average of the values in these combined four fields where the number used in the 'AVG' calculation will change based on the number of fields that contain a value of 1-5. If none of the fields contain a number of 1-5, then I don't want to compute the average of the fields. The field values to be averaged will be selected where a field called permnum = a value.

    I've been able to get as far as handling the fields when they have certain values with the following code:

    *************************************

    Select

    CASEWhen ST1Q1 <> 'N' Then CAST(ST1Q1 as tinyint)Else Null End AS intST1Q1,

    CASEWhen ST1Q2 <> 'N' Then CAST(ST1Q2 as tinyint)Else Null End AS intST1Q2,

    CASEWhen ST1Q3 <> 'N' Then CAST(ST1Q3 as tinyint)Else Null End AS intST1Q3,

    CASEWhen ST1Q4 <> 'N' Then CAST(ST1Q4 as tinyint)Else Null End AS intST1Q4

    From tblRCStudentGrades

    where Permnum = '602'

    *************************************

    To clarify this a bit, I have some possible scenarios below that would be examples of situations I would need to account for:

    When:

    ST1Q1 = 2

    ST1Q2 = 'N'

    ST1Q3 = 2

    ST1Q4 = 5

    Then: (2+2+5)/3

    When:

    ST1Q1 = 2

    ST1Q2 = 'N'

    ST1Q3 = NULL

    ST1Q4 = 5

    Then: (2+5)/2

    When:

    ST1Q1 = 'N'

    ST1Q2 = 'N'

    ST1Q3 = NULL

    ST1Q4 = 'N'

    Then: (Do not compute an average)

    Please let me know if you need further clarification. If you have Ideas on this, please pass them along.

    Thanks!

    CSDunn

  • May not be the best solution but should work for you calculation try

    
    
    (
    ISNULL((CASE When ST1Q1 = 'N' Then Null ELSE ST1Q1 End),0) +
    ISNULL((CASE When ST1Q2 = 'N' Then Null ELSE ST1Q2 End),0) +
    ISNULL((CASE When ST1Q3 = 'N' Then Null ELSE ST1Q3 End),0) +
    ISNULL((CASE When ST1Q4 = 'N' Then Null ELSE ST1Q4 End),0)
    ) / (
    (CASE When ST1Q1 = 'N' Then 0 ELSE 1 End) +
    (CASE When ST1Q2 = 'N' Then 0 ELSE 1 End) +
    (CASE When ST1Q3 = 'N' Then 0 ELSE 1 End) +
    (CASE When ST1Q4 = 'N' Then 0 ELSE 1 End)
    )

    and to get rid of the rows that will be an issue like this one

    When:

    ST1Q1 = 'N'

    ST1Q2 = 'N'

    ST1Q3 = NULL

    ST1Q4 = 'N'

    Then: (Do not compute an average)

    use a where clause like so

    
    
    WHERE
    ISNULL(ST1Q1,'N') != 'N' AND
    ISNULL(ST1Q2,'N') != 'N' AND
    ISNULL(ST1Q3,'N') != 'N' AND
    ISNULL(ST1Q4,'N') != 'N'

    I am sure it is along those lines but there may be a better way someone else can see. But should give you a start.

  • I'll go with Antares686 suggestion,

    thus version II

    Select Calc.StudentNr,Calc.ColSum/Calc.FieldCnt From (

    Select StudentNr,

    (ISNULL((CASE When ST1Q1 = 'N' Then Null ELSE ST1Q1 End),0) +

    ISNULL((CASE When ST1Q2 = 'N' Then Null ELSE ST1Q2 End),0) +

    ISNULL((CASE When ST1Q3 = 'N' Then Null ELSE ST1Q3 End),0) +

    ISNULL((CASE When ST1Q4 = 'N' Then Null ELSE ST1Q4 End),0)),

    ((CASE When IsNull(ST1Q1,'N') = 'N' Then 0 ELSE 1 End) +

    (CASE When IsNull(ST1Q2,'N') = 'N' Then 0 ELSE 1 End) +

    (CASE When IsNull(ST1Q3,'N') = 'N' Then 0 ELSE 1 End) +

    (CASE When IsNull(ST1Q4,'N') = 'N' Then 0 ELSE 1 End))

    From tblRCStudentGrades) as Calc(StudentNr,ColSum,FieldCnt)

    Where Calc.FieldCnt>0

  • Version III - which slightly modifies 5409045121009's script to

    - account for the varchar values

    - add output for no average cases

    - provide alternative methods of checking columns (I can't say if this is a better method)

    Select Calc.StudentNr,Calc.ColSum,Calc.FieldCnt,

    CASE WHEN Calc.FieldCnt = 0 THEN 'No Average'

    ELSE Convert(Varchar(20),(Calc.ColSum/Calc.FieldCnt)) END AvgVal

    From (

    Select StudentNr,

    (

    (CASE When isNumeric(ST1Q1) = 0 Then 0 ELSE Convert(tinyint,ST1Q1) End) +

    (CASE When isNumeric(ST1Q2) = 0 Then 0 ELSE Convert(tinyint,ST1Q2) End) +

    (CASE When isNumeric(ST1Q3) = 0 Then 0 ELSE Convert(tinyint,ST1Q3) End) +

    (CASE When isNumeric(ST1Q4) = 0 Then 0 ELSE Convert(tinyint,ST1Q4) End)

    ),

    (

    (CASE When ST1Q1 like '[1-5]' Then 1 ELSE 0 End) +

    (CASE When ST1Q2 like '[1-5]' Then 1 ELSE 0 End) +

    (CASE When ST1Q3 like '[1-5]' Then 1 ELSE 0 End) +

    (CASE When ST1Q4 like '[1-5]' Then 1 ELSE 0 End)

    )

    From tblRCStudentGrades) as Calc(StudentNr,ColSum,FieldCnt)

  • Hello,

    The following is what I came up with, but the final average is still a little off:

    *****************************************************************

    Select

    Permnum,

    ROUND(

    CAST

    (

    (

    ISNULL((CASE When ST1Q1 <> 'N' Then CAST(ST1Q1 as tinyint) Else Null End),0) +

    ISNULL((CASE When ST1Q2 <> 'N' Then CAST(ST1Q2 as tinyint) Else Null End),0) +

    ISNULL((CASE When ST1Q3 <> 'N' Then CAST(ST1Q3 as tinyint) Else Null End),0) +

    ISNULL((CASE When ST1Q4 <> 'N' Then CAST(ST1Q4 as tinyint) Else Null End),0)

    )

    /

    (

    (CASE When IsNull(ST1Q1,'N') = 'N' Then 0 ELSE 1 End) +

    (CASE When IsNull(ST1Q2,'N') = 'N' Then 0 ELSE 1 End) +

    (CASE When IsNull(ST1Q3,'N') = 'N' Then 0 ELSE 1 End) +

    (CASE When IsNull(ST1Q4,'N') = 'N' Then 0 ELSE 1 End)

    )

    AS Decimal(3,2)

    ),1)As Results

    FROM tblRCStudentGrades

    WHERE

    Permnum = '602'

    and

    (ISNULL(ST1Q1,'N') <> 'N' or

    ISNULL(ST1Q2,'N') <> 'N' or

    ISNULL(ST1Q3,'N') <> 'N' or

    ISNULL(ST1Q4,'N') <> 'N')

    *******************************************************

    In the case where ST1Q1=2, ST1Q4=5, and the other fields are null (or 'N'), the average should come out to 3.50, but I get 3.00.

    Am I not using ROUND correctly to acheive the desired result?

    Thanks Again!

  • I think you need to shift the CAST down to the denominator...this is because it is already doing an integer division and rounding the result - and this rounded result is being converted to numeric

    ROUND(

    (

    ISNULL((CASE When ST1Q1 <> 'N' Then CAST(ST1Q1 as tinyint) Else Null End),0) +

    ISNULL((CASE When ST1Q2 <> 'N' Then CAST(ST1Q2 as tinyint) Else Null End),0) +

    ISNULL((CASE When ST1Q3 <> 'N' Then CAST(ST1Q3 as tinyint) Else Null End),0) +

    ISNULL((CASE When ST1Q4 <> 'N' Then CAST(ST1Q4 as tinyint) Else Null End),0)

    )

    /

    CAST(

    (

    (CASE When IsNull(ST1Q1,'N') = 'N' Then 0 ELSE 1 End) +

    (CASE When IsNull(ST1Q2,'N') = 'N' Then 0 ELSE 1 End) +

    (CASE When IsNull(ST1Q3,'N') = 'N' Then 0 ELSE 1 End) +

    (CASE When IsNull(ST1Q4,'N') = 'N' Then 0 ELSE 1 End)

    )As Decimal(3,2)

    ),1)As Results

  • The problem is interger values produce integer results, at least one has to be a decimal to get a decimal result. Try

    
    
    Select
    Permnum,
    ROUND(
    (
    ISNULL((CASE When ST1Q1 <> 'N' Then CAST(ST1Q1 as tinyint) Else Null End),0) +
    ISNULL((CASE When ST1Q2 <> 'N' Then CAST(ST1Q2 as tinyint) Else Null End),0) +
    ISNULL((CASE When ST1Q3 <> 'N' Then CAST(ST1Q3 as tinyint) Else Null End),0) +
    ISNULL((CASE When ST1Q4 <> 'N' Then CAST(ST1Q4 as tinyint) Else Null End),0)
    )
    /
    CAST((
    (CASE When IsNull(ST1Q1,'N') = 'N' Then 0 ELSE 1 End) +
    (CASE When IsNull(ST1Q2,'N') = 'N' Then 0 ELSE 1 End) +
    (CASE When IsNull(ST1Q3,'N') = 'N' Then 0 ELSE 1 End) +
    (CASE When IsNull(ST1Q4,'N') = 'N' Then 0 ELSE 1 End)
    ) AS Decimal(3,2)), 1) As Results

    FROM
    tblRCStudentGrades

    WHERE
    Permnum = '602' and
    (
    'N' NOT IN (
    ISNULL(ST1Q1,'N'),
    ISNULL(ST1Q2,'N'),
    ISNULL(ST1Q3,'N'),
    ISNULL(ST1Q4,'N')
    )
    )

    Try the NOT IN where option I show above for OR as well, just using the IN standard. This is reverse of what most people think of but is quite usefull.

  • Amended original post to add permnum limitation.

    declare @denom int

    declare @num int

    declare @aver float

    declare myfetch cursor for select permnum, cola, colb, colc, cold from rmx

    declare @myfetch nvarchar(10)

    declare @myfetcha varchar(2)

    declare @myfetchb varchar(2)

    declare @myfetchc varchar(2)

    declare @myfetchd varchar(2)

    create table #mytemp (permnum nvarchar(10), average float)

    open myfetch

    fetch next from myfetch into @myfetch, @myfetcha, @myfetchb, @myfetchc, @myfetchd

    while @@fetch_status = 0

    begin

    set @denom = casewhen isnumeric(@myfetcha)=1 then 1

    else @denom

    end

    set @denom = casewhen isnumeric(@myfetchb)=1 then @denom+1

    else @denom

    end

    set @denom = casewhen isnumeric(@myfetchc)=1 then @denom+1

    else @denom

    end

    set @denom = casewhen isnumeric(@myfetchd)=1 then @denom+1

    else @denom

    end

    set @num = casewhen isnumeric(@myfetcha)=1 then @myfetcha

    else @num

    end

    set @num = casewhen isnumeric(@myfetchb)=1 then @num+@myfetchb

    else @num

    end

    set @num = casewhen isnumeric(@myfetchc)=1 then @num+@myfetchc

    else @num

    end

    set @num = casewhen isnumeric(@myfetchd)=1 then @num+@myfetchd

    else @num

    end

    set @aver = casewhen @denom !=0 then cast(@num as float)/cast(@denom as float)

    else null

    end

    insert into #mytemp (permnum, average)

    values (@myfetch, @aver)

    set @num = 0

    set @denom = 0

    fetch next from myfetch into @myfetch, @myfetcha, @myfetchb, @myfetchc, @myfetchd

    end

    close myfetch

    deallocate myfetch

    select * from #mytemp where permnum = '602'

    drop table #mytemp

    Edited by - Aadomm on 06/18/2003 03:16:36 AM

  • May not be practical or possible, but you could have a table in with the numeric scores for the various possible answers. Then you could join the tables on the response to get the numeric score for each answer.

  • Thanks to all, your input has been very helpful!

    CSDunn

Viewing 10 posts - 1 through 9 (of 9 total)

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