Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can This Be Made Set-Based?

Author  Topic 

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-20 : 10:38:35
I hope this makes sense...
Create quiz questions:
We have a table that contains questions. These questions must have topics assigned to them. They can also have optional sub-topics assigned to them, but it is not required. The topics and sub-topics are linked. For instance, topic TR might have T1, T2, and T3 as sub-topics. Topic PR might have S1, S2 and S3 as sub-topics.

Example:
Question 1000 might have topics TR, SC and PR assigned to it. TR might have sub-topics T1 and T2 assigned to it, and SC might have sub-topics T2 and T3 assigned to it. PR might have no sub-topics assigned to it.

When a quiz template is created, the total number of questions to generate is typed in, and topics to select from are assigned to it. Each topic might also have an optional sub-topic assigned to it. Each topic has a percent “weight” of the total number of questions it should represent. If a topic has sub-topics, then each sub-topic will be assigned a percent of the topic questions.

Example:
Template 1000: Number of questions: 100
Topics and Sub-Topics, percent weight:
TR: 40%
T1: 33%
T2: 34%
T3: 33%
SC: 30%
T1: 70%
T2: 30%
PR: 30%

In this example, the quiz should generate 40 questions from the topic TR, 30 questions from SC and 30 questions from PR. Topic TR should be comprised of 13.2 questions from T1 (40 * .33), 13.6 questions from T2 (40 * .34), and 13.2 questions from T3. Obviously, we can’t have fractional questions. So, somehow that needs to end up being 40 (e.g.: 13, 14, 13). SC would be handled in the same way.

The questions need to be randomly selected and randomly ordered. I have handled this with SELECT TOP n and ORDER BY NEWID(). Also, and perhaps obviously, questions may only appear once in a quiz. A question may be in more than one topic area the quiz is pulling from, but that question may only appear one time. If a question is assigned a topic, but no sub-topic, it should be included in any sub-topic (with a bias towards those that have the desired sub-topic assigned). In question 1000 above, it should appear in any PR search, but it should not appear in a search for SC-T1 or TR-T3.

This seems like a great candidate for tally tables and coalesce, but is beyond my current capabilities. So I'm looking at while loops. Ugh.


FischMan

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-20 : 10:40:00
This is what I have done so far. The next step would be to start looping...



Edit: Removed some unnecessary debug code. Thanks Rob Volk!

FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-20 : 10:47:06
One more thing:
Some of you reviewing the code posted will have an extreme allergic reaction to my prefacing views with "vew" and tables with "tbl." This reaction may include foaming at the mouth and screaming. You will want to say things like "Everything in a relational DB is a table." and "Tables are just unordered sets of information." and "set theory this" and "Codd said that." Undertood, and valid points all, I'm sure. I do, however, have a very simple reply for you: Corporate coding standards require this naming convention, you don't work here, and I like getting paid.

Oh, one more thing: All humourous jibes aside, any help will be greatly appreciated. Thanks for taking the time from your busy schedule to help a poor developer!

FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-20 : 16:29:13
Another item:
It would be really nice if I could just call a stored procedure with a template ID, and have it spit out a list of question ID's.

I'm not sure if this can be done in a non-iterative, set-based manner. Some of the wizardry I've seen with tally tables leads me to think that it is possible, just outside my skillset.

Any of you Yak Masters/Generals/Colonels (or Kernels) have any brilliant ideas?

FischMan
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-12-20 : 17:46:27
A couple of things:

1. Can you post the definitions of the tables, and more importantly, the views?
2. Can you also post some sample data and results you're expecting? Doesn't have to be a lot, 5-10 source rows is fine.
3. Is there any reason you're selecting columns that are not used in JOIN or WHERE conditions? (i.e. TemplateName) Ultimately you're just returning a QuestionID.
4. Is DISTINCT really necessary? Would help to have the table structures including the primary key.
5. Mind if I post an answer using some abbreviated column aliases (tblQuestion -> Q, TemplateQuestions -> TQ)? I'm not saying short ones are a best practice, but having long table aliases really makes this hard to read.

Thanks.
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-20 : 18:35:13
quote:
Originally posted by robvolk

A couple of things:

1. Can you post the definitions of the tables, and more importantly, the views?
2. Can you also post some sample data and results you're expecting? Doesn't have to be a lot, 5-10 source rows is fine.
3. Is there any reason you're selecting columns that are not used in JOIN or WHERE conditions? (i.e. TemplateName) Ultimately you're just returning a QuestionID.
4. Is DISTINCT really necessary? Would help to have the table structures including the primary key.
5. Mind if I post an answer using some abbreviated column aliases (tblQuestion -> Q, TemplateQuestions -> TQ)? I'm not saying short ones are a best practice, but having long table aliases really makes this hard to read.

Thanks.



4. Yes - distinct is really necessary. Since topic->sub-topics is 1->many, there are (potentially) multiple rows returned for each topic. However, your comment #3 did cause me to realize that, in the course of building and debugging, my query had gathered a lot of unneeded detritus. I have altered the second query to clean it up some:

Running the query with the following line removed (which causes it to return all matching rows):
AND tblQuestionTopicAreas.QuestionID NOT IN ( SELECT n FROM @UsedQuestions ) )
gives:

Without distinct, the result set returned is:
QuestionID
1063
1009
1014
1009
1043
1063
1187
1063
1014
1043
1187
1014
1187
1009
1043

With distinct, the result set returned is:
QuestionID
1187
1043
1009
1063
1014

Thanks for your reply!

FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-20 : 18:58:15
Here is a sample result set from the above sample query (post 2):
n
1009
1014
1022
1187

FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-20 : 19:12:27
This is from tblQuestionTopicAreas:
QuestionTopicAreaID TopicAreaID QuestionID
12 AAT-SC 1000
346 AAT-SC 1187
349 AAT-SC 1002
352 AAT-SC 1148
354 AAT-SC 1082
357 AAT-SC 1063
360 AAT-SC 1208
17 AAT-TR 1000
347 AAT-TR 1187
359 AAT-TR 1063
361 AAT-TR 1208
362 AAT-TR 1009
365 AAT-TR 1043
368 AAT-TR 1022
369 AAT-TR 1014


FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-20 : 19:13:15
quote:
Originally posted by robvolk

A couple of things:

1. Can you post the definitions of the tables, and more importantly, the views?
2. Can you also post some sample data and results you're expecting? Doesn't have to be a lot, 5-10 source rows is fine.
3. Is there any reason you're selecting columns that are not used in JOIN or WHERE conditions? (i.e. TemplateName) Ultimately you're just returning a QuestionID.
4. Is DISTINCT really necessary? Would help to have the table structures including the primary key.
5. Mind if I post an answer using some abbreviated column aliases (tblQuestion -> Q, TemplateQuestions -> TQ)? I'm not saying short ones are a best practice, but having long table aliases really makes this hard to read.

Thanks.



Do you have enough information now, Rob?

FischMan
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-12-20 : 19:47:02
That should be OK, thanks. I'm not at a computer where I can work on this right now, so it might have to wait till tomorrow, or hopefully someone else can take a crack at it.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-12-21 : 11:21:16
Hate to be a pest but I can't duplicate the results without schema and sample data for the criteria tables referenced in the view (tblCriteriaTopicAreas, tblCriteria, tblCriteriaSubTopicAreas, tblQuizCriteria).

At the same time, I don't see that most of the tables in the view are needed for this query, except tblQuizTemplates (TemplateID). The other column in the query, TopicAreaID, is joined to the view column, and the tblQuestionTopicAreas table has the QuestionID as well. Is there another table that links templates to questions? If so it will probably be better to avoid the view entirely.

Just out of curiosity, are there foreign keys defined on these tables? Also, what was the decision for using varchar(25) in some primary keys but int for the rest?
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-21 : 11:29:47
quote:
Originally posted by robvolk

Just out of curiosity, are there foreign keys defined on these tables? Also, what was the decision for using varchar(25) in some primary keys but int for the rest?



1. Yes, there are foreign keys.
2. Heh - You're getting into company politics there... They WERE all int, but someone wanted natural keys, if possible, not generated keys. So, some of the keys ended up being varchar's, instead of int's.

FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-21 : 11:39:55
quote:
Originally posted by robvolk

Hate to be a pest but I can't duplicate the results without schema and sample data for the criteria tables referenced in the view (tblCriteriaTopicAreas, tblCriteria, tblCriteriaSubTopicAreas, tblQuizCriteria).

At the same time, I don't see that most of the tables in the view are needed for this query, except tblQuizTemplates (TemplateID). The other column in the query, TopicAreaID, is joined to the view column, and the tblQuestionTopicAreas table has the QuestionID as well. Is there another table that links templates to questions? If so it will probably be better to avoid the view entirely.



Let's see if I can answer your questions here:
The view is not specific to this query. It's meant to be useable in multiple situations. It concatenates all of the disparate tables that make up a quiz template into one simple table structure. So, yes, you are correct - it has a few extra columns that aren't absolutely necessary to this specific issue.

That said, the only way to get the topics and sub-topics associated with a template is via the criteria tables. That is where that information is stored (as it is 1->n). In theory, criteria can be created one time, and then reused among many templates (reality is a bit different, but we won't go there). Hence the 1->n relationship among templates and criteria (or, more accurately, n<->n relationship).

The connection between topics, questions and templates is, I think, as simple as I can make it. There aren't other ways to link questions <-> topics <-> templates together.

As far as the schema, since the view is, essentially, a table, can you pretend that it is a table? Or do you still need the schema for the criteria tables?

FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-21 : 17:50:26
Rob has said that he will try to take a look at this while he is on vacation! Do any of you other SQL guru's out there wanna take a crack at this? I'm running down blind alleys, myself (yeah - I'm working on it, I'm just getting nowhere).

Rob thinks, while tally tables might be an option, it might be solvable with a ranking function (Row_Number, Rank, Dense_Rank, NTile). I'm trying. I'm just not seeing the road out of the wilderness yet...

I'm normally pretty good at cracking things like this, but for some reason, this one has me stumped.

FischMan
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 18:07:06
I'm doing this AFTER Work

It's a disease we have

we try to control it with....stuff

HOWEVER

You have WAY overcomplicated what you are trying to do

Can we take a step and...get "Stupid"? (it's a technical term)

We often fins that people get stuck on a track that they don't let go of...when the answer and solution is usually 10 times easier than what is posted....


Rob is now at Bondi beach sucking down margaritas...you will have to excuse him if his eyes are not on the screen



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 18:09:10
why do you post a simple business requirement document (statement)

Like what it is that you need to do..not technology..just business terms

Let's go from there

with real examples

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-21 : 18:31:14
quote:
Originally posted by X002548

why do you post a simple business requirement document (statement)

Like what it is that you need to do..not technology..just business terms

Let's go from there

with real examples

Brett



Brett,
Thank you for your reply. Maybe I am missing something, but I thought that was what I did with my first post. No tech talk there, just the business requirements on what needs to go into generating questions for a quiz from a template.

I can maybe try to rewrite it?

I don't care how this gets accomplished. Like I said, I am working on this, and I am trying to figure out how to implement Rob's suggestions. The problem is, all I have to show for my work is a bloody head from beating it against a brick wall for a week. Which is no fun.



FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2011-12-21 : 18:35:33
quote:
Originally posted by X002548

I'm doing this AFTER Work

It's a disease we have

we try to control it with....stuff

HOWEVER

You have WAY overcomplicated what you are trying to do

Can we take a step and...get "Stupid"? (it's a technical term)

We often fins that people get stuck on a track that they don't let go of...when the answer and solution is usually 10 times easier than what is posted....

Rob is now at Bondi beach sucking down margaritas...you will have to excuse him if his eyes are not on the screen

Brett

8-)




On Rob: Yeah - his eyes are probably on something more lovely...

If you think I am over-complicating this, then by all means, let's get stupid. I've been programming for 15+ years now, I know what you say is true.

FischMan
Go to Top of Page

rainmanblack
Starting Member

3 Posts

Posted - 2011-12-28 : 19:03:42
Using a cursor makes it simple plus you get the added bonus of avoid time costs of outer joins and sub selects:

create table TemplateCounts
TemplateID int not null,
TopicAreaID int not null,
SubTopicAreaID int null,
CountOfQs int not null,
constraint PK_TemplateCounts primary key (TemplateID,TopicAreaID,SubTopicAreaID)
)

--Populate that table with the exact counts you want per grouping of questions

Create table QuestionsOutput
(FormOrder uniqueidentifier default Newid(),
TopicAreaID int,
SubTopicAreaID int,
QuestionID int
)

-- Then two cursors

DECLARE CreationCursor Cursor
FOR
     Select TopicAreaID,SupTopicAreaId,CountOfQs from TemplateCounts
     where TemplateID=1000
Open CreationCursor
Declare @TopicAreaID integer, @SupTopicAreaId integer, @CountOfQs integer, @count integer
Fetch next from CreationCursor Into @TopicAreaID , @SupTopicAreaId , @CountOfQs
while @@FETCH_STATUS=0
     begin
     set @count=0
     while @count<@CountOfQs
          begin
          insert into QuestionsOutput
               (formorder, TopicAreaID,SubTopicAreaID,QuestionID)
          values
               (NEWID(),@TopicAreaID,@SupTopicAreaId,NULL)
          set @count=@count+1
          end

     Fetch next from CreationCursor Into @TopicAreaID , @SupTopicAreaId , @CountOfQs
     end
close CreationCursor
deallocate CreationCursor


DECLARE UpdateCursor Cursor
FOR
     Select formorder,TopicAreaID,SubTopicAreaID from QuestionsOutput
     order by formorder
Open UpdateCursor
Declare @formorder uniqueidentifier, @TopicAreaID integer, @SupTopicAreaId integer
Fetch next from UpdateCursor Into @formorder, @TopicAreaID , @SubTopicAreaId
while @@FETCH_STATUS=0
     begin
     set @count=0
     if @SupTopicAreaId is null then
          begin
          update QuestionsOutput
               set QuestionID=
                    (select top 1 tblQuestions.QuestionID
                    from tblQuestions
                    join tblQuestionTopicArea
                         on (tblQuestions.QuestionID=tblQuestionTopicArea.QuestionID
                         and tblQuestionTopicArea.tblQuestionTopicAreaID=@TopicAreaID
                         and tblQuestions.QuestionID not IN select QuestionID from QuestionsOutput)
                    order by newid())
          where FormOrder=@formorder
          end
     else
          begin
          update QuestionsOutput
               set QuestionID=
                    (select top 1 tblQuestions.QuestionID
                    from tblQuestions
                    join tblQuestionTopicArea
                         on (tblQuestions.QuestionID=tblQuestionTopicArea.QuestionID
                         and tblQuestionTopicArea.tblQuestionTopicAreaID=@TopicAreaID)
                    join tblQuestionSubTopicAreas
                         on (tblQuestionTopicArea.QuestionTopicAreaID=tblQuestionSubTopicAreas.QuestionTopicAreaID
                         and SubTopicAreaID=@SupTopicAreaId
                         and tblQuestions.QuestionID not IN select QuestionID from QuestionsOutput)
                    order by newid())
          where FormOrder=@formorder
          end
     Fetch next from UpdateCursor Into @formorder, @TopicAreaID , @SubTopicAreaId
     end
close UpdateCursor
deallocate UpdateCursor

Lipaque
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-12-29 : 06:48:04
quote:
Originally posted by rainmanblack

Using a cursor makes it simple plus you get the added bonus of avoid time costs of outer joins and sub selects:

Lipaque


So, you think a cursor is LESS of a time cost than a set based solution

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-03 : 15:45:41
quote:
Originally posted by rainmanblack

Using a cursor makes it simple plus you get the added bonus of avoid time costs of outer joins and sub selects:


...


Lipaque



Thanks! I may yet end up going this route. I would love to get this to a set-based query, though. It should perform faster than an iterative loop through the data.


FischMan
Go to Top of Page
    Next Page

- Advertisement -