April 19, 2012 at 5:05 am
Dear All,
I am in great trouble. I am having 2 Tables: QuestionPaper & Employee.
The QuestionPaper is having following details:
--------------------------------
QuestionID | ExamID | SectionID
--------------------------------
232 | 18 | 20
233 | 18 | 20
234 | 18 | 20
235 | 18 | 20
236 | 18 | 20
237 | 18 | 20
238 | 18 | 20
239 | 18 | 20
240 | 18 | 20
241 | 18 | 20
262 | 18 | 21
263 | 18 | 21
264 | 18 | 21
265 | 18 | 21
266 | 18 | 21
267 | 18 | 21
268 | 18 | 21
269 | 18 | 21
270 | 18 | 21
271 | 18 | 21
292 | 18 | 22
293 | 18 | 22
294 | 18 | 22
295 | 18 | 22
296 | 18 | 22
297 | 18 | 22
298 | 18 | 22
299 | 18 | 22
300 | 18 | 22
301 | 18 | 22
--------------------------------
and the Employee table contains:
---------------
ExamID | EmpID
---------------
18 | 119
18 | 219
18 | 359
18 | 297
18 | 384
18 | 245
18 | 117
18 | 269
---------------
Now from the Above 2 tables, I have to create a T-SQL, which will assign 5 Unique QuestionID RANDOMLY as per SectionID and ExamID to each EmpID, e.g.
----------------------------------------
ExamID | EmpID | SectionID | QuestionID
----------------------------------------
18 | 119 | 20 | 233
18 | 119 | 20 | 237
18 | 119 | 20 | 232
18 | 119 | 20 | 241
18 | 119 | 20 | 239
18 | 119 | 21 | 262
18 | 119 | 21 | 269
18 | 119 | 21 | 265
18 | 119 | 21 | 268
18 | 119 | 21 | 263
18 | 119 | 22 | 301
18 | 119 | 22 | 299
18 | 119 | 22 | 297
18 | 119 | 22 | 295
18 | 119 | 22 | 293
18 | 219 | 20 | 236
18 | 219 | 20 | 233
18 | 219 | 20 | 241
18 | 219 | 20 | 232
18 | 219 | 20 | 239
18 | 219 | 21 | 265
18 | 219 | 21 | 266
18 | 219 | 21 | 267
18 | 219 | 21 | 268
18 | 219 | 21 | 269
18 | 219 | 22 | 292
18 | 219 | 22 | 294
18 | 219 | 22 | 296
18 | 219 | 22 | 298
18 | 219 | 22 | 300
----------------------------------------
Hope I can make you understand my query, please help.
April 19, 2012 at 6:05 am
This should do the trick:
-- SETUP
DECLARE @Employee TABLE (
ExamID int,
EmpID int
)
DECLARE @QuestionPaper TABLE (
QuestionID int,
ExamID int,
SectionID int
)
-- SAMPLE DATA
INSERT INTO @QuestionPaper (QuestionID, ExamID, SectionID)
VALUES
(232, 18, 20),
(233, 18, 20),
(234, 18, 20),
(235, 18, 20),
(236, 18, 20),
(237, 18, 20),
(238, 18, 20),
(239, 18, 20),
(240, 18, 20),
(241, 18, 20),
(262, 18, 21),
(263, 18, 21),
(264, 18, 21),
(265, 18, 21),
(266, 18, 21),
(267, 18, 21),
(268, 18, 21),
(269, 18, 21),
(270, 18, 21),
(271, 18, 21),
(292, 18, 22),
(293, 18, 22),
(294, 18, 22),
(295, 18, 22),
(296, 18, 22),
(297, 18, 22),
(298, 18, 22),
(299, 18, 22),
(300, 18, 22),
(301, 18, 22)
INSERT INTO @Employee (ExamID, EmpID)
VALUES
(18, 119),
(18, 219),
(18, 359),
(18, 297),
(18, 384),
(18, 245),
(18, 117),
(18, 269)
-- SOLUTION:
SELECT E.ExamID
,E.EmpID
,Questions.QuestionId
,Questions.SectionId
FROM @Employee AS E
CROSS APPLY (
SELECT *
FROM (
-- Rank the questions partitioned by section
-- and sorted by a random value
SELECT RN = ROW_NUMBER() OVER (
PARTITION BY SectionId
ORDER BY CHECKSUM(NEWID())
)
,QuestionId
,SectionId
FROM @QuestionPaper
WHERE ExamId = E.ExamId
) AS RankedQuestions
WHERE RN <= 5
) AS Questions
Hope this helps
Gianluca
-- Gianluca Sartori
April 19, 2012 at 6:12 am
That's a pretty slick solution. Using this snippet is pretty neat to get random values per section. ROW_NUMBER() OVER( PARTITION BY [field] ORDER BY CHECKSUM(NEWID()) )
April 19, 2012 at 6:26 am
Thank You Sir. Will check and confirm for any error
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply