simplied the sp

Question

Post reply

simplied the sp

Frances L

SSCarpal Tunnel

Points: 4727
More actions
May 25, 2011 at 1:00 pm

#238086

simplied the sp . the number should extend to 10. Thanks.
I will like to simple the below statement in sp.
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='1' and answer='a'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='A'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='1' and answer='b'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='B'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='1' and answer='c'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='C'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='1' and answer='d'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='D'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='1' and answer='E'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='E'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='1' and answer='F'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='F'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='2' and answer='A'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='A'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='2' and answer='B'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='B'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='2' and answer='C'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='C'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='2' and answer='D'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='D'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='2' and answer='E'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='E'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='2' and answer='F'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='F'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='3' and answer='A'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='A'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='3' and answer='B'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='B'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='3' and answer='C'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='C'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='3' and answer='D'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='D'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='3' and answer='E'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='E'
update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=
(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat
FROM BackInjuryPreventionQuizAnswer where len(rtrim(ltrim( [Answer] )))<>0 and questions='3' and answer='F'
GROUP BY Questions, Answer order by Questions ) where [dbo].[tblQuizAnwserSelectionStatistics].Answer='F'

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

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

huh?

maybe a copy/paste error?

EVERY statement you posted is making the Question1 field equal to a count of the persons name?

it looks like the sub query gets a total, and has NO relationship whatsoever to the table you are updating?

update [dbo].[tblQuizAnwserSelectionStatistics] set [Question1]=

(SELECT top 1 cOUNT(DISTINCT EmpName) AS Stat

if you are trying to get a total of the right answers, why not just roll up the counts in the first place? why are you updating some other table?

SELECT COUNT(DISTINCT EmpName) AS Stat,Questions,Answer

FROM BackInjuryPreventionQuizAnswer where len([Answer]) <> 0 --LEN already does a right trim...

GROUP BY Questions, Answer

order by Questions

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

the only different answer is A, B, C, D, E, F . Question from 1 to 15. Thanks/.