January 29, 2008 at 10:42 am
I've never done anything quite like this and I'm having a major mental block, so any help appreciated. I have a table with a bunch of columns. Each column either has a 1 for a yes, 2 for a no, or 3 for NA. I need to count all the ones in the row where iAsmtID=xx and I need to count all the twos where iAsmtID=xx. Then I'll have to divide these later but for now I just need to be able to find out how many of each there are in a certain row. Thanks.
January 29, 2008 at 11:40 am
Well - you could either "long-hand" the formula, or - you might consider using the UNPIVOT clause to normalize you data, and then use something like SUM(case answer when 1 then 1 else 0 end)
In other words - use UNPIVOT to turn your one row, many repeating columns into many shorter rows with no repeating columns.
Without some specific as to your table structure - I can't give you any specifics, but Books Online has some decent examples.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 31, 2008 at 8:01 am
Sorry for the long code but its shorter than me trying to describe it. I am getting an error saying incorrect syntax near ",". The only comma in the string is highlighted below. What am I doing wrong?:crazy:
'Open connection
set conn=Server.CreateObject("ADODB.Connection")
conn.open My_Conn
set rs = Server.CreateObject("ADODB.Recordset")
'Get asmt information
str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS CountOfOnes, SUM((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE ASMT.iAsmtID="&iAsmtID
response.Write("DEBUG: This is the string:" + str)
rs.open str, conn
if rs.eof = true then
response.Write(" ")
response.End()
else
'Declare variables
CountOfOnes = rs("CountOfOnes")
CountOfTwos = rs("CountOfTwos")
end if
rs.close
set rs = nothing
conn.close
set conn = nothing
January 31, 2008 at 8:30 am
Here's an UNPIVT example:
select iasmtID, sum(case when answer=1 then 1 else 0 end) as countOfOnes,
sum(case when answer=2 then 1 else 0 end) as countOfTwos
from
(
select iasmtid,question,answer
from (select
iasmtID,q1,q2,q3,q4,q5,
q6,q7,q8,q9,q10,q11,q12,
q13,q14,q15,q16,q17,q18,q19,q20
from itcc_test) t
UNPIVOT (ANSWER for Question in
([q1],[q2],[q3],[q4],[q5],
[q6],[q7],[q8],[q9],[q10],
[q11],[q12],[q13],[q14],[q15],
[q16],[q17],[q18],[q19],[q20])
) unpvt
) as renormalizedTable
WHERE iasmtID=3
group by iasmtID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply