[thelist] case statement catch-all? MSSQL

[Brian Cummiskey]

Ken Schaefer wrote:

>Whilst this can be done using T-SQL, as you can see from the various SQL
>statements being proposed, it's not the easiest thing to do. It's also not
>very efficient.
>
>Your presentation layer code (PHP, .NET, CF) is probably *very* efficient at
>adding three numeric values together, and that's all that the final value
>(the combined % is). Is it possible to have the presentation layer code add
>the values together?
>  
>
Sure, it can be done on the scripting side, but IMO, it would be much 
more time consuming, especially since i'm stuck with classic asp.  The 
example I provided is a far cry from the actual query to make the post 
easier to work with..

I'm SURE the code below is HIGHLY in-efficient.  But, with the time 
frame they give us for these things, 95% of our code and projects are 
hack jobs just 'good enough' to work and get by.  :(

At this point, i'm going with a temp table, population, and then 
updates, and finally a select * from the temp

here's the FIRST quadrant (questions 1 through 6 out of 28)  and a sub 
total row query...

the code is horible, but, it works...  and we can always buy more ram :D

@startdate and @enddate are defined further up...
fn_dateonly is a global function on our server that strips the time out 
of a datetime field


-------------------------------------------------------------
--        Report 3


    create table #temp
    (
        Quadrant varchar(25),
        Question varchar(25),
        Parameters varchar(200),
        [CT] varchar(50),
        [PA] varchar(50),
        [TX] varchar(50),
        [Overall Compliance] varchar(50)
    )

    insert into #temp
        (Quadrant, Question, Parameters, [CT],[PA],[TX],[Overall 
Compliance])
        values ('1', 'q1', 'Started Call Promptly', NULL, NULL, NULL, NULL )
            UPDATE #temp
                set
                    [CT] = (select cast(round((sum(cast(q1_points as 
float)) / sum(cast(q1_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'B' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [PA] = (select cast(round((sum(cast(q1_points as 
float)) / sum(cast(q1_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'L' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [TX] = (select cast(round((sum(cast(q1_points as 
float)) / sum(cast(q1_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'T' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [Overall Compliance] = (select 
cast(round((sum(cast(q1_points as float)) / sum(cast(q1_possible as 
float))) * 100,2) AS varchar) + '%' from the_table where site 
IN('B','L','T') AND fn_dateonly(calldate) between @startdate and @enddate)
            WHERE Question = 'q1'

    insert into #temp
        (Quadrant, Question, Parameters, [CT],[PA],[TX],[Overall 
Compliance])
        values ('1', 'q2', 'Proper Introduction', NULL, NULL, NULL, NULL)
            UPDATE #temp
                set
                    [CT] = (select cast(round((sum(cast(q2_points as 
float)) / sum(cast(q2_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'B' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [PA] = (select cast(round((sum(cast(q2_points as 
float)) / sum(cast(q2_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'L' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [TX] = (select cast(round((sum(cast(q2_points as 
float)) / sum(cast(q2_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'T' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [Overall Compliance] = (select 
cast(round((sum(cast(q2_points as float)) / sum(cast(q2_possible as 
float))) * 100,2) AS varchar) + '%' from the_table where site 
IN('B','L','T') AND fn_dateonly(calldate) between @startdate and @enddate)
            WHERE Question = 'q2'

    insert into #temp
        (Quadrant, Question, Parameters, [CT],[PA],[TX],[Overall 
Compliance])
        values ('1', 'q3', 'Manner/Tone', NULL, NULL, NULL, NULL)
            UPDATE #temp
                set
                    [CT] = (select cast(round((sum(cast(q3_points as 
float)) / sum(cast(q3_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'B' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [PA] = (select cast(round((sum(cast(q3_points as 
float)) / sum(cast(q3_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'L' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [TX] = (select cast(round((sum(cast(q3_points as 
float)) / sum(cast(q3_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'T' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [Overall Compliance] = (select 
cast(round((sum(cast(q3_points as float)) / sum(cast(q3_possible as 
float))) * 100,2) AS varchar) + '%' from the_table where site 
IN('B','L','T') AND fn_dateonly(calldate) between @startdate and @enddate)
            WHERE Question = 'q3'

    insert into #temp
        (Quadrant, Question, Parameters, [CT],[PA],[TX],[Overall 
Compliance])
        values ('1', 'q4', 'Conversation Control', NULL, NULL, NULL, NULL)
            UPDATE #temp
                set
                    [CT] = (select cast(round((sum(cast(q4_points as 
float)) / sum(cast(q4_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'B' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [PA] = (select cast(round((sum(cast(q4_points as 
float)) / sum(cast(q4_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'L' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [TX] = (select cast(round((sum(cast(q4_points as 
float)) / sum(cast(q4_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'T' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [Overall Compliance] = (select 
cast(round((sum(cast(q4_points as float)) / sum(cast(q4_possible as 
float))) * 100,2) AS varchar) + '%' from the_table where site 
IN('B','L','T') AND fn_dateonly(calldate) between @startdate and @enddate)
            WHERE Question = 'q4'

    insert into #temp
        (Quadrant, Question, Parameters, [CT],[PA],[TX],[Overall 
Compliance])
        values ('1', 'q5', 'Listening Skills', NULL, NULL, NULL, NULL)
            UPDATE #temp
                set
                    [CT] = (select cast(round((sum(cast(q5_points as 
float)) / sum(cast(q5_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'B' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [PA] = (select cast(round((sum(cast(q5_points as 
float)) / sum(cast(q5_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'L' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [TX] = (select cast(round((sum(cast(q5_points as 
float)) / sum(cast(q5_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'T' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [Overall Compliance] = (select 
cast(round((sum(cast(q5_points as float)) / sum(cast(q5_possible as 
float))) * 100,2) AS varchar) + '%' from the_table where site 
IN('B','L','T') AND fn_dateonly(calldate) between @startdate and @enddate)
            WHERE Question = 'q5'

    insert into #temp
        (Quadrant, Question, Parameters, [CT],[PA],[TX],[Overall 
Compliance])
        values ('1', 'q6', 'Grammer/Vocabulary', NULL, NULL, NULL, NULL)
            UPDATE #temp
                set
                    [CT] = (select cast(round((sum(cast(q6_points as 
float)) / sum(cast(q6_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'B' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [PA] = (select cast(round((sum(cast(q6_points as 
float)) / sum(cast(q6_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'L' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [TX] = (select cast(round((sum(cast(q6_points as 
float)) / sum(cast(q6_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'T' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [Overall Compliance] = (select 
cast(round((sum(cast(q6_points as float)) / sum(cast(q6_possible as 
float))) * 100,2) AS varchar) + '%' from the_table where site 
IN('B','L','T') AND fn_dateonly(calldate) between @startdate and @enddate)
            WHERE Question = 'q6'

    insert into #temp
        (Quadrant, Question, Parameters, [CT],[PA],[TX],[Overall 
Compliance])
        values ('1 Totals', 'q1 to q6', 'Quadrant Totals', NULL, NULL, 
NULL, NULL)
            UPDATE #temp
                set
                    [CT] = (select cast(round((sum(cast(q1_q6_points as 
float)) / sum(cast(q1_q6_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'B' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [PA] = (select cast(round((sum(cast(q1_q6_points as 
float)) / sum(cast(q1_q6_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'L' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [TX] = (select cast(round((sum(cast(q1_q6_points as 
float)) / sum(cast(q1_q6_possible as float))) * 100,2) AS varchar) + '%' 
from the_table where site = 'T' AND fn_dateonly(calldate) between 
@startdate and @enddate),
                    [Overall Compliance] = (select 
cast(round((sum(cast(q1_q6_points as float)) / sum(cast(q1_q6_possible 
as float))) * 100,2) AS varchar) + '%' from the_table where site 
IN('B','L','T') AND fn_dateonly(calldate) between @startdate and @enddate)
            WHERE Question = 'q1 to q6'