SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can This Be Made Set-Based?
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

X002548
Not Just a Number

15586 Posts

Posted - 01/04/2012 :  13:31:52  Show Profile  Reply with Quote
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




BWAHAHAHAHAHAHAHAHAHA

U R Fired



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

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 01/05/2012 :  04:30:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is quite easy. The trick is to calculate at the lowest level.
Example: Give me 40 questions back!

TR: 40%
   T1: 33% = 0.33 * 0.40 * 40 =  5.28 questions.   5?
   T2: 34% = 0.34 * 0.40 * 40 =  5.44 questions.   6?
   T3: 33% = 0.33 * 0.40 * 40 =  5.28 questions.   5?
SC: 30%
   T1: 70% = 0.70 * 0.30 * 40 =  8.40 questions.   8?
   T2: 30% = 0.30 * 0.30 * 40 =  3.60 questions.   4?
PR: 30%    =        0.30 * 40 = 12.00 questions.  12
                             --------------------------
                                40.00 questions.  40


Now apply your business logic how to "round" up and down the integer part of number of questions.
Then use row_number with order by newid() to get random questions and partition by lowest level.

pesudocode

select *
from (select *, row_number() over (partition by lowestlevel order by newid()) as seqid from sourcetable) as x
inner join (select from above calcuation) as t ON t.lowestlevel = x.lowestlevel and t.maxrow > x.seqid


Done.



N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 01/05/2012 04:41:31
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/05/2012 :  10:07:00  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
quote:
Originally posted by X002548

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


BWAHAHAHAHAHAHAHAHAHA
U R Fired
.....


Dude. That's pretty harsh on someone with only 3 posts. At least the contribution was pertinent. Or are you aiming for Celko's vacant post on this forum?

rainmanblack - there is nothing inherently slow about outer joins or even sub-queries (as long as they are nice sub-queries)

A double cursor, however, is not 'the way' of the database. It may be easy to understand from an imperative programming perspective but to get good performance out of a relation database you should be able to adjust your thinking and start being DECLARATIVE. Say:

"This is what I want."

and Not "This is how I want you to do it."

If you are still around have a look at Peso's post:
SwePeso Posted - 01/05/2012 : 04:30:54

Good luck

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edited by - Transact Charlie on 01/05/2012 10:07:57
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/05/2012 :  10:34:03  Show Profile  Reply with Quote
Mia Culpa...So Don is the only other person to take issue? I'm surprised. BTW...IF You have an Oracle or DB2 Background...that is what you know...a double cursor...I saw guys in Peoplesoft write 7 levels of bested cursors in DB2 and wonder why it ran for 13 hours

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

USA
59 Posts

Posted - 01/05/2012 :  10:42:30  Show Profile  Reply with Quote
quote:
Originally posted by X002548

Mia Culpa...So Don is the only other person to take issue? I'm surprised. BTW...IF You have an Oracle or DB2 Background...that is what you know...a double cursor...I saw guys in Peoplesoft write 7 levels of bested cursors in DB2 and wonder why it ran for 13 hours

Brett

8-)




I also took issue, but tried to be politic about the matter. After all, he did try to help, and his solution is easy to read, and should work (albeit slowly). If I didn't think the set-based query would run so much faster, I wouldn't still be trying to solve it.

Thank you to SwePeso - I will see what I can do with your pseudo-code. I've written somwhere between 1,000 and 2,000 lines of SQL code, and have discarded most of it (with comments, it's about 300 lines now). If your four or so lines work, I just may have to cry.

FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/05/2012 :  10:51:30  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

This is quite easy. The trick is to calculate at the lowest level.

...

Done.




Thanks for the tip! I think I am missing something here, though. What is "lowest level?" This is what I have done:


select *
from (select *, row_number() over (partition by lowestlevel order by newid()) as seqid from vewQuizTemplates) as x
inner join (
SELECT DISTINCT
       TopicAreaID,
       ROUND ( (NumTotal * (TopicAreaWeight / 100)), 0) AS NumTopicQuestions,
       SubTopicAreaID,
       ROUND (
          ( (NumTotal * (TopicAreaWeight / 100)) * (SubTopicAreaWeight / 100)),
          0)
          AS lowestlevel -- NumSubTopicQuestions
  FROM vewQuizTemplates
 WHERE TemplateID = 1000
 ) as t ON t.lowestlevel = x.lowestlevel and t.maxrow > x.seqid


It gives me the following error:
Lookup Error - SQL Server Database Error: Invalid column name 'lowestlevel'.


So, what do you mean by "lowest level?" I am not familiar with it, and doing a search turns up nothing useful.

FischMan
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/05/2012 :  11:24:23  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
I think:

(select *, row_number() over (partition by lowestlevel order by newid()) as seqid from vewQuizTemplates) as x

There is no column called lowestLevel in the view vewQuizTemplates?

Partition by is splitting the ranking function into groups of distinct values from [lowestLevel] column. Whichever column you wanted to group on and rank (I am a bit lost in this thread) is what you want in there. (It must be in vewQuizTemplates

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/05/2012 :  11:38:13  Show Profile  Reply with Quote
quote:
Originally posted by Transact Charlie

I think:

(select *, row_number() over (partition by lowestlevel order by newid()) as seqid from vewQuizTemplates) as x

There is no column called lowestLevel in the view vewQuizTemplates?

Partition by is splitting the ranking function into groups of distinct values from [lowestLevel] column. Whichever column you wanted to group on and rank (I am a bit lost in this thread) is what you want in there. (It must be in vewQuizTemplates

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION




Thank you. Your comments were helpful. I know what partition does, but wasn't grasping the whole "lowestlevel" thing.

Assuming I understand this all correctly, I rewrote the query taking into account your comments. It's essentially doing one small part of what my query I am writing does. It's close, and does a good deal of what I need, but doesn't quite get it all. This is what I wrote:

select *
from (select *, row_number() over (partition by SubTopicAreaID order by newid()) as seqid from vewQuestionTopicsSubTopics) as x
inner join (
SELECT DISTINCT
       TopicAreaID,
       ROUND ( (NumTotal * (TopicAreaWeight / 100)), 0) AS NumTopicQuestions,
       SubTopicAreaID,
       ROUND (
          ( (NumTotal * (TopicAreaWeight / 100)) * (SubTopicAreaWeight / 100)),
          0)
          AS NumSubTopicQuestions
  FROM vewQuizTemplates
 WHERE TemplateID = 1000
 ) as t ON t.SubTopicAreaID = x.SubTopicAreaID and t.TopicAreaID = x.TopicAreaID and t.NumSubTopicQuestions > x.seqid

I might still be missing something, though...

FischMan
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/05/2012 :  11:49:27  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Can we get some sample data and DML for the tables?

Or did you provide this already? Thread is getting messy

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/05/2012 :  12:36:40  Show Profile  Reply with Quote
quote:
Originally posted by Transact Charlie

Can we get some sample data and DML for the tables?

Or did you provide this already? Thread is getting messy

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION



Yes - it is getting quite long.

On page 1, I provide the create table and create view statements, as well as some sample datasets. What I have not provided is an actual insert statement. However, all of the data are redily available, and an insert statement should be pretty easy to generate. Especially if you create the view as a static table, for testing and development purposes.

FischMan
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 01/05/2012 :  13:29:12  Show Profile  Reply with Quote
Has anyone suggested to use a cursor yet?j/k My hunch is if you would have posted the complete DML, some sample data, and desired results, this would've been answered 10 posts ago.

I can appreciate how you want to provide everything you have done and where you are at, but that has drastically overcomplicated this and seeing everything in one relatively short post (tables, inserts, desired results, etc ) will get this answered quickly. Unfortunately it's a lot of unnecessary work to go through each comment even if you are trying to be helpful by posting them, in order to assist you and extract what we need.

Good luck, but post it all in one comment (creates, inserts, sample data, desired results), and my hunch is someone will provide you an adequate query.


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Edited by - Vinnie881 on 01/05/2012 13:31:37
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 01/05/2012 :  17:25:18  Show Profile  Reply with Quote
I had some time so I went through the original post, and hope this is what you are looking for. It is a working version of based on picking a template as illustrated with the @template temp table. it will retrieve that percentage of questions for that topic & subtopics. I didn't read all the posts though so I am a little confused how you want the sub-topic weight to effect the results of each individual question (for simplicity I treated each topic/subtopic weight on a individual question as irrellivent so if a question was marked as a MATH,SCIENCE & history topic it was equally weighted for that individual question. This is easy to change I just didn't see how you wanted the percent weight to effect the actual question so please explain more ). If you explain a little clearer I'll take a look.

The template table I had return different percentages based on what topics where assigned to the template as you illustrated and I set the total amount of questions returned to 10, but you can change that to whatever.

Please explain more if this is not what you want and also why/how you are weighting the sub-topics of the questions and how is that factored in the final results.

declare @questions table(questid int identity(1,1) primary key,question varchar(200))
insert into @questions
select 'This is my question ' + cast(number as varchar(100))
from master..spt_values
where number > 0
group by number

declare @topics table(topicid int identity(1,1) primary key,topic varchar(200))
insert into @topics
select 'math' Union all
select 'Science' Union all
select 'History' Union all
select 'Computers' Union all
select 'Art' Union all
select 'Widgets' 



declare @QUESTIONTOPICS table(questiontopicid int identity(1,1) primary key,questid int,topicid int,weight decimal(3,2))
--select @offset

insert into @QUESTIONTOPICS(questid,topicid)
select questid,b.topicid from 
@questions a
cross apply
(select top 3 topicid 
from @topics
order by newid()
) b

declare @offset decimal(3,2),@anchor int
set @offset = .45
update a
set @offset = weight = case when @offset = .45 then .2 when @offset = .2 then .35 else .45 end 
,@anchor = Questiontopicid
from
@questiontopics a




--if I want [edit]]10[/edit] questions where the ratio was
--15% math related
--40% widget related
--10% art
--35% history this would return it.
declare @template table( templateid int ,topicid int,weight decimal(3,2),questionscount int)
insert into @TEMPLATE(Templateid,topicid,weight,questionscount)
select '1000',1,.15,10 union all
select '1000',6,.40,10 union all
select '1000',5,.10,10 union all
select '1000',3,.35,10 


declare @working table(workingid int identity(1,1) primary key,questid int,topicid int ,questionsfortopic decimal(3,2),keep int)
insert into @working(questid,topicid,questionsfortopic,keep)
select a.questid,b.topicid,CEILING(b.weight * b.questionscount),0
from
@questiontopics a
inner join
@template b
on a.topicid = b.topicid
order by topicid,newid()

declare @mycount int,@topicid int
set @mycount = 0

update a
set keep = case when topicid = @topicid and @mycount <= questionsfortopic then 1 end
,@mycount = case when topicid <> @topicid then 1 else @mycount + 1 end					   
,@topicid = topicid
from @working a

declare @totalquestions int
set @totalquestions = (select top 1 questionscount from @TEMPLATE)

set rowcount @totalquestions
select * from @working a inner join @questions b on a.questid = b.questid where keep = 1
set rowcount 0

/*RESULTS
workingid	questid	topicid	questionsfortopic	keep	questid	question
1	1666	1	2.00	1	1666	This is my question 1666
2	1704	1	2.00	1	1704	This is my question 1704
1034	1969	3	4.00	1	1969	This is my question 1969
1035	1661	3	4.00	1	1661	This is my question 1661
1036	963	3	4.00	1	963	This is my question 963
1037	1820	3	4.00	1	1820	This is my question 1820
2054	1250	5	1.00	1	1250	This is my question 1250
3111	1046	6	4.00	1	1046	This is my question 1046
3112	1692	6	4.00	1	1692	This is my question 1692
3113	846	6	4.00	1	846	This is my question 846
*/


Notice how 10 results still returned even though the weight came out uneven. I did this to ensure when the weighted results are odd #'s you can definitively still set the questions and not worry about the rounding. This query just is to illustrate a method, but once I can see more actual results you are looking for, it should be fairly easy to tweak.


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

edit: to fix long lines.

Edited by - Vinnie881 on 01/05/2012 19:02:08
Go to Top of Page

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/05/2012 :  18:37:26  Show Profile  Reply with Quote
First off, Thank You! This is doing things I'm not real famliar with, and is far more creative than my brute force method...

quote:

for simplicity I treated each topic/subtopic weight on a individual question as irrellivent so if a question was marked as a MATH,SCIENCE & history topic it was equally weighted for that individual question.



That should be correct. Topics aren't weighted on an individual question, only on a template. The "weight" column in your questiontopics table isn't necessary. It is necessary in the template table, though. Also, subtopics are necessary in the template table, along with weights.

I apologize - I'm not real good at writing those create and populate scripts that you wrote. That would have taken me a long time to write that little bit.

I'll look at this more tomorrow. I'd love to try it out tonight, but I have a meeting I have to go to.



FischMan
Go to Top of Page

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/05/2012 :  18:48:48  Show Profile  Reply with Quote
OK. Here goes nothing... *cringe*

Here's my solution. I have not done ANY optimizing, so please keep that in mind. It works, and it produces the desired results. But, it's really ugly. Try not to flame me too much...

[edit]deleted unoptimized code to make thread more readable.[/edit]

Results:

RowNum	SortCol	QuestionID	TopicAreaID	SubTopicAreaID	NumTopicQuestions	NumSubTopicQuestions
9	2	1002	AAT-TR	T2	1	2
1	-1	1005	SC		2	
2	-1	1006	SC		2	
8	1	1008	SC		2
10	3	1013	SC		2
3	-1	1019	AAT-TR	T2	2	1
4	-1	1022	AAT-TR	T1	2	1
5	-1	1063	AAT-AC		3	
6	-1	1082	QR		2	
7	-1	1148	RC		1	


Note that the original result set didn't have enough questions, and had to be padded. If you look closely at the data, you will notice that AAT-AC should have had 3 questions, but only one qualifying question was found. Similarly, QR should have had 2 questions, but only one qualifying question was found. So, that left me short 2+1=3 questions. The total number of questions that should have been generated is 10, but we only got 7, so I had to pad the result set with 3 additional questions.

On my developer PC, this takes about 0.4 seconds to run. To date, I have been unsuccessful in generating an execution plan.

I am now late for my meeting, so I will get more information/data out tomorrow.

FischMan

Edited by - FischMan2 on 01/09/2012 16:26:00
Go to Top of Page

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/06/2012 :  14:18:52  Show Profile  Reply with Quote
I changed the temp tables in the above query to table variables. It cut my execution time in half (from ~0.4 seconds to ~0.2 seconds).

FischMan
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 01/09/2012 :  16:26:38  Show Profile  Reply with Quote
here is a revised version of the query I posted that evaluates subtopics by weight as well. See how it performs compared to your existing.



--THIS IS CREATING SAMPLE QUESTIONS
declare @questions table(questid int identity(1,1) primary key,question varchar(200))
insert into @questions
select 'This is my question ' + cast(number as varchar(100))
from master..spt_values
where number between 0 and 2000
group by number


--THIS IS CREATING SAMPLE TOPICS
declare @topics table(topicid int identity(1,1) primary key,topic varchar(200))
insert into @topics
select 'math' Union all
select 'Science' Union all
select 'History' Union all
select 'Computers' Union all
select 'Art' Union all
select 'Widgets' 


--THIS IS ASSIGNING A MAIN TOPIC TO QUESTIONS
declare @QUESTIONTOPICS table(questiontopicid int identity(1,1) primary key,questid int,topicid int)

--select @offset

insert into @QUESTIONTOPICS(questid,topicid)
select questid,b.topicid from 
@questions a
cross apply
(select top 3 topicid 
from @topics
order by newid()
) b



--BELOW SETS UP THE MAIN TEMPLATE.
--if I want 100 questions where the ratio was
--15% math related
--40% widget related
--10% art
--35% history this would return it.
declare @template table( templateid int ,topicid int,weight decimal(3,2),questionscount int)
insert into @TEMPLATE(Templateid,topicid,weight,questionscount)
select '1000',1,.15,100 union all
select '1000',6,.40,100 union all
select '1000',5,.10,100 union all
select '1000',3,.35,100 


--BELOW SETS THE SUB-TOPICS SAMPLE SET
declare @subtemplate table( subtemplateid int primary key identity(1,1) ,Childtopicid int,topicid int, weight decimal(3,2)
)
insert into @subtemplate(childtopicid,topicid)
select b.topicid,a.topicid 
from 
@template a
cross apply
(select top 3 topicid 
from @topics aa
where aa.topicid <> a.topicid
order by newid()
) b



--RANDOMIZES THE WEIGHTS FOR SAMPLE TESTING
declare @offset decimal(3,2), @anchor int
set @offset = .45
update a
set @offset = weight = case when @offset = .45 then .2 when @offset = .2 then .35 else .45 end 
,@anchor = subtemplateid
from
@subtemplate a



--CREATES A TABLE TO WORK WITH THE DATA
/*BELOW IT IS ALSO DOING THE CALCULATION FOR # of questions for each TOPIC/SUBTOPIC
*/

declare @working table(workingid int identity(1,1) primary key,questid int,topicid varchar(20) ,questionsfortopic decimal(6,2),keep int)
insert into @working(questid,topicid,questionsfortopic)
select
questid,
case when c.subtemplateid is null then cast(b.topicid as varchar(20))
       else cast(b.topicid as varchar(10)) + '-' + cast(c.childtopicid as varchar(10))end as NewTopicID
,case when c.subtemplateid is null then round(a.weight * a.questionscount,0) 
else round(a.weight * a.questionscount * c.weight,0) end
from 
@template a
inner join
@questiontopics b
on a.topicid = b.topicid
left join
@subtemplate c
on a.topicid = c.topicid
--and 
order by newtopicid,newid()



declare @mycount int,@topicid varchar(20),@counter int

set @mycount = 0
set @counter = 0

declare @totalquestions int
set @totalquestions = (select top 1 questionscount from @TEMPLATE)

/*Updates based on variable and only selects a random sample of questions designated by the "KEEP" flag
*/

update a
set keep = case when topicid = @topicid and (@mycount <= questionsfortopic or @counter <= @totalquestions)  then 1 end
, @counter = case when topicid = @topicid and @mycount <= questionsfortopic then @counter + 1 else @counter end
,@mycount = case when topicid <> @topicid then 1 else @mycount + 1 end					   
,@topicid = topicid
from @working a

--THIS ENSURES PROPER QUESTION amounts ARE RETURNED
/*NOTE IF NOT ENOUGH QUESTIONS EXIST as designated by the weights, IT WILL PULL QUESTIONS FROM THE LAST AVAILABLE CATEGORY*/

set rowcount @totalquestions
select * from @working a inner join @questions b on a.questid = b.questid where keep = 1
set rowcount 0




Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Edited by - Vinnie881 on 01/09/2012 16:40:38
Go to Top of Page

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/09/2012 :  17:44:20  Show Profile  Reply with Quote
quote:
Originally posted by Vinnie881

here is a revised version of the query I posted that evaluates subtopics by weight as well. See how it performs compared to your existing.


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881



Vinnie881:
I altered your query slightly to take into account how my DB is structured. Hopefully I converted it correctly. The execution time is the same (~0.2 seconds). However, the result set is inaccurate. Here is the information:

Query:

DECLARE @working TABLE
                 (
                    workingid           INT IDENTITY (1, 1) PRIMARY KEY,
                    questid             INT,
                    topicid             VARCHAR (20),
                    questionsfortopic   DECIMAL (10, 2),
                    keep                INT
                 )

INSERT INTO @working (questid, topicid, questionsfortopic)
   SELECT c.questionid,
          CASE
             WHEN a.subtopicareaid IS NULL
             THEN
                cast (b.topicareaid AS VARCHAR (20))
             ELSE
                  cast (b.topicareaid AS VARCHAR (10))
                + '-'
                + cast (a.subtopicareaid AS VARCHAR (10))
          END
             AS NewTopicID,
          CASE
             WHEN a.subtopicareaid IS NULL
             THEN
                round (a.topicareaweight * a.NumTotal, 0)
             ELSE
                round (a.topicareaweight * a.NumTotal * a.subtopicareaweight, 0)
          END AS questionsfortopic
     FROM vewQuizTemplates a
          INNER JOIN tblQuestionTopicAreas b
             ON a.topicareaid = b.topicareaid
          INNER JOIN tblQuestions c
             ON b.QuestionID = c.QuestionID
--          LEFT JOIN @subtemplate c
--             ON a.topicid = c.topicid
  WHERE
   a.TemplateID = 1000
   AND c.IsActiveQuestion = 'True'
   ORDER BY newtopicid, newid ()

select * from @working

DECLARE
   @mycount   INT,
   @topicid   VARCHAR (20),
   @counter   INT

SET @mycount = 0
SET @counter = 0

DECLARE @totalquestions   INT
SET @totalquestions = (SELECT TOP 1 NumTotal  --Select no more than the total number of questions requested
                      FROM vewQuizTemplates
                     WHERE TemplateID = 1000)

/*Updates based on variable and only selects a random sample of questions designated by the "KEEP" flag
*/

UPDATE a
   SET keep =
          CASE
             WHEN     topicid = @topicid
                  AND (   @mycount <= questionsfortopic
                       OR @counter <= @totalquestions)
             THEN
                1
          END,
       @counter =
          CASE
             WHEN topicid = @topicid AND @mycount <= questionsfortopic
             THEN
                @counter + 1
             ELSE
                @counter
          END,
       @mycount = CASE WHEN topicid <> @topicid THEN 1 ELSE @mycount + 1 END,
       @topicid = topicid
  FROM @working a

--THIS ENSURES PROPER QUESTION amounts ARE RETURNED
/*NOTE IF NOT ENOUGH QUESTIONS EXIST as designated by the weights, IT WILL PULL QUESTIONS FROM THE LAST AVAILABLE CATEGORY*/

SET  ROWCOUNT @totalquestions

SELECT *
  FROM @working a --INNER JOIN tblQuestions b ON a.questid = b.questionid
 WHERE keep = 1

SET  ROWCOUNT 0


@working table:

workingid	questid	topicid	questionsfortopic	keep
1	1063	AAT-AC-T1	15000.00000	
2	1208	AAT-AC-T1	15000.00000	
3	1148	AAT-AC-T1	15000.00000	
4	1187	AAT-AC-T1	15000.00000	
5	1002	AAT-AC-T1	15000.00000	
6	1082	AAT-AC-T1	15000.00000	
7	1002	AAT-AC-T3	15000.00000	
8	1187	AAT-AC-T3	15000.00000	
9	1148	AAT-AC-T3	15000.00000	
10	1082	AAT-AC-T3	15000.00000	
11	1208	AAT-AC-T3	15000.00000	
12	1063	AAT-AC-T3	15000.00000	
13	1208	AAT-TR-T1	6800.00000	
14	1002	AAT-TR-T1	6800.00000	
15	1187	AAT-TR-T1	6800.00000	
16	1043	AAT-TR-T1	6800.00000	
17	1014	AAT-TR-T1	6800.00000	
18	1022	AAT-TR-T1	6800.00000	
19	1063	AAT-TR-T1	6800.00000	
20	1009	AAT-TR-T1	6800.00000	
21	1019	AAT-TR-T1	6800.00000	
22	1187	AAT-TR-T2	6600.00000	
23	1009	AAT-TR-T2	6600.00000	
24	1043	AAT-TR-T2	6600.00000	
25	1063	AAT-TR-T2	6600.00000	
26	1002	AAT-TR-T2	6600.00000	
27	1022	AAT-TR-T2	6600.00000	
28	1019	AAT-TR-T2	6600.00000	
29	1208	AAT-TR-T2	6600.00000	
30	1014	AAT-TR-T2	6600.00000	
31	1019	AAT-TR-T3	6600.00000	
32	1208	AAT-TR-T3	6600.00000	
33	1022	AAT-TR-T3	6600.00000	
34	1063	AAT-TR-T3	6600.00000	
35	1043	AAT-TR-T3	6600.00000	
36	1014	AAT-TR-T3	6600.00000	
37	1009	AAT-TR-T3	6600.00000	
38	1187	AAT-TR-T3	6600.00000	
39	1002	AAT-TR-T3	6600.00000	
40	1020	PR	200.00000	
41	1017	PR	200.00000	
42	1009	PR	200.00000	
43	1006	PR	200.00000	
44	1007	PR	200.00000	
45	1008	PR	200.00000	
46	1016	PR	200.00000	
47	1013	PR	200.00000	
48	1002	PR	200.00000	
49	1018	PR	200.00000	
50	1003	PR	200.00000	
51	1010	PR	200.00000	
52	1005	PR	200.00000	
53	1019	PR	200.00000	
54	1011	PR	200.00000	
55	1001	PR	200.00000	
56	1004	PR	200.00000	
57	1014	PR	200.00000	
58	1015	PR	200.00000	
59	1012	PR	200.00000	
60	1187	QR	200.00000	
61	1082	QR	200.00000	
62	1148	RC	100.00000	
63	1002	RC	100.00000	


Final result set:

workingid	questid	topicid	questionsfortopic	keep
1	1063	AAT-AC-T1	15000.00000	1
2	1208	AAT-AC-T1	15000.00000	1
3	1148	AAT-AC-T1	15000.00000	1
4	1187	AAT-AC-T1	15000.00000	1
5	1002	AAT-AC-T1	15000.00000	1
6	1082	AAT-AC-T1	15000.00000	1
7	1002	AAT-AC-T3	15000.00000	1
8	1187	AAT-AC-T3	15000.00000	1
9	1148	AAT-AC-T3	15000.00000	1
10	1082	AAT-AC-T3	15000.00000	1


Perhaps I set something up incorrectly?

FischMan
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 01/09/2012 :  17:51:41  Show Profile  Reply with Quote
The test data I provided gives correct results, so my assumption is that there is a conversion issue to make it work for your data. I am gone for the day though, so I'll check in the morning.

My guess is I am using decimal percentages though and you are using whole numbers (i.e. in my table 33% is .33, in yours it's 33), Double check that.


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/09/2012 :  18:08:16  Show Profile  Reply with Quote
quote:
Originally posted by Vinnie881

The test data I provided gives correct results, so my assumption is that there is a conversion issue to make it work for your data. I am gone for the day though, so I'll check in the morning.

My guess is I am using decimal percentages though and you are using whole numbers (i.e. in my table 33% is .33, in yours it's 33), Double check that.


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881



That's definitely part of it. Dividing the weight by 100 made a big difference...

That said, it still produces incorrect results:

workingid	questid	topicid	questionsfortopic	keep
1	1063	AAT-AC-T1	2.00	1
2	1208	AAT-AC-T1	2.00	1
3	1148	AAT-AC-T1	2.00	1
4	1187	AAT-AC-T1	2.00	1
5	1002	AAT-AC-T1	2.00	1
6	1082	AAT-AC-T1	2.00	1
7	1208	AAT-AC-T3	2.00	1
8	1082	AAT-AC-T3	2.00	1
9	1148	AAT-AC-T3	2.00	1
10	1002	AAT-AC-T3	2.00	1


For some reason, the "set keep =" code is only eliminating one question from the result set (63 down to 62). I don't yet fully understand what that function is doing, so I am not sure where the issue is.

I'm going home now, too...

FischMan
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 01/10/2012 :  10:02:31  Show Profile  Reply with Quote
see if this query makes more sense for you:


DECLARE @working TABLE
                 (
                    workingid           INT IDENTITY (1, 1) PRIMARY KEY,
                    questid             INT,
                    topicid             VARCHAR (20),
                    questionsfortopic   DECIMAL (10, 2),
                    keep                INT
                 )

INSERT INTO @working (questid, topicid, questionsfortopic)
   SELECT c.questionid,
          CASE
             WHEN a.subtopicareaid IS NULL
             THEN
                cast (b.topicareaid AS VARCHAR (20))
             ELSE
                  cast (b.topicareaid AS VARCHAR (10))
                + '-'
                + cast (a.subtopicareaid AS VARCHAR (10))
          END
             AS NewTopicID,
          CASE
             WHEN a.subtopicareaid IS NULL
             THEN
                round (a.topicareaweight * a.NumTotal, 0)
             ELSE
                round (a.topicareaweight * a.NumTotal * a.subtopicareaweight, 0)
          END AS questionsfortopic
     FROM vewQuizTemplates a
          INNER JOIN tblQuestionTopicAreas b
             ON a.topicareaid = b.topicareaid
          INNER JOIN tblQuestions c
             ON b.QuestionID = c.QuestionID
--          LEFT JOIN @subtemplate c
--             ON a.topicid = c.topicid
  WHERE
   a.TemplateID = 1000
   AND c.IsActiveQuestion = 'True'
   ORDER BY newtopicid, newid ()

Select * 
from
(
select row_number() over (partition by topicid order by  (select 1)) as Rowid,*
from 
@working
) aa
where  aa.rowid <= aa.questionsfortopic




Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Edited by - Vinnie881 on 01/10/2012 15:45:54
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.26 seconds. Powered By: Snitz Forums 2000