Filtering data based on sum
-
Hi,
I have a table questions which have question id, chapter name, question type, question complexity and marks.
Now i want to filter questions on based on total marks randomly. Example: i what to filter questions for total 20 marks where question type is short note and question complexity is easy.
How can i achieve this sql server 2008?
Thanks
Moovi Rastogi
-
Please provide sample data and expected results as outlined in first link in my signature. The sample data should be provided as a script that creates (temp) tables or declares table variables and then inserts the data/results into their respective tables.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
most likely you are looking for the HAVING function.
so something like this:
SELECT cust_id,sum(orderAmount) As TheSum
From SomeTable
Group by cust_id
HAVING sum(orderAmount) > 1000Lowell
--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! -
This sounds like a running totals issue.
Something along the lines of .....
SELECT as many random questions as needed
WHERE QuestionType = 'Short Note'
AND QuestionComplexity = 'Eeasy'
SUCH THAT SUM(Marks) = 20.The biggest problem will be if the questions have different Marks values.
You may need to have a second set of questions with specific Mark values to fill up the difference when the selected questions do not exactly match the required value. - DesNorton - Wednesday, April 5, 2017 12:52 PM
This might be helpful
-- Define a table to hold the questions
CREATE TABLE #QUESTIONS (
QuestionID INT PRIMARY KEY CLUSTERED
, ChapterName VARCHAR(30)
, QuestionType VARCHAR(20)
, QuestionComplexity VARCHAR(20)
, Marks INT
);
GO-- Create some sample questions
DECLARE @SAMPLE_SIZE BIGINT = 100;;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO #QUESTIONS ( QuestionID, ChapterName, QuestionType, QuestionComplexity, Marks )
SELECT
QuestionID = NM.N
, ChapterName = 'Chapter ' + CONVERT(VARCHAR(10), NM.N %5 +1)
, QuestionType = CASE ABS(CHECKSUM(NEWID())%(3))+1
WHEN 1 THEN 'Multi Choice'
WHEN 2 THEN 'Calculation'
ELSE 'Short Note'
END
, QuestionComplexity = CASE ABS(CHECKSUM(NEWID())%(3))+1
WHEN 1 THEN 'Medium'
WHEN 2 THEN 'Hard'
ELSE 'Easy'
END
, Marks = ABS(CHECKSUM(NEWID())%(3))+1
FROM NUMS AS NM
OPTION (RECOMPILE);
GO-- Query the data, with the required filters
SET NOCOUNT ON;DECLARE @NumMarks INT = 20;
DECLARE @QuestionType VARCHAR(20) = 'Short Note';
DECLARE @QuestionComplexity VARCHAR(20) = 'Easy';WITH cteBulkQuestions AS (
SELECT *
FROM (
SELECT
QuestionType
, QuestionComplexity
, ChapterName
, QuestionID
, Marks
, RunTotal = SUM(Marks) OVER (PARTITION BY QuestionType, QuestionComplexity
ORDER BY ChapterName, QuestionID
)
FROM #QUESTIONS
WHERE QuestionType = @QuestionType
AND QuestionComplexity = @QuestionComplexity
AND Marks > 1
) AS src
WHERE RunTotal <= @NumMarks
)
, cteMakeupQuestions AS (
SELECT *
FROM (
SELECT
QuestionType
, QuestionComplexity
, ChapterName
, QuestionID
, Marks
, RunTotal = SUM(Marks) OVER (PARTITION BY QuestionType, QuestionComplexity
ORDER BY ChapterName, QuestionID
)
FROM #QUESTIONS
WHERE QuestionType = @QuestionType
AND QuestionComplexity = @QuestionComplexity
AND Marks = 1
) AS src
WHERE RunTotal <= @NumMarks - (SELECT MAX(RunTotal) FROM cteBulkQuestions)
)
SELECT
blk.QuestionType
, blk.QuestionComplexity
, blk.ChapterName
, blk.QuestionID
, blk.Marks
FROM cteBulkQuestions AS blk
UNION ALL
SELECT
mkp.QuestionType
, mkp.QuestionComplexity
, mkp.ChapterName
, mkp.QuestionID
, mkp.Marks
FROM cteMakeupQuestions AS mkp
ORDER BY ChapterName, QuestionID;EDIT: I tried to randomise the questions by replacing ORDER BY ChapterName, QuestionID with ORDER BY NEWID(). However, this made the second CTE return inconsistent results.
-
Thanks for reply.
Please use the following script for sample data...
CREATE TABLE [dbo].[test](
[QesID] [varchar](50) primary key,
[ChpID] [varchar](50) NULL,
[QesComplexity] [varchar](50) NULL,
[QesType] [varchar](50) NULL,
[QesQuestion] [varchar](1000) NULL,
[Marks] [int] NULL)INSERT [dbo].[test] ([QesID], [ChpID], [QesComplexity], [QesType], [QesQuestion], [Marks]) VALUES (N'QUES-0000010', N'CHPT-0000004', N'Easy', N'Fill-in-the-Blanks', N'Equilateral triangle ka area_______ hota hai.', 1)
INSERT [dbo].[test] ([QesID], [ChpID], [QesComplexity], [QesType], [QesQuestion], [Marks]) VALUES (N'QUES-0000011', N'CHPT-0000004', N'Touch', N'Sketch', N'Hexagon ka perimeters ______ hota hai.', 5)
INSERT [dbo].[test] ([QesID], [ChpID], [QesComplexity], [QesType], [QesQuestion], [Marks]) VALUES (N'QUES-0000012', N'CHPT-0000004', N'Easy', N'Numerical', N'Octagon area _______ hota hai.', 1)
INSERT [dbo].[test] ([QesID], [ChpID], [QesComplexity], [QesType], [QesQuestion], [Marks]) VALUES (N'QUES-0000013', N'CHPT-0000004', N'Touch', N'Long Answer', N'Trapezium ka perimeters _______ hota hai.', 2)
INSERT [dbo].[test] ([QesID], [ChpID], [QesComplexity], [QesType], [QesQuestion], [Marks]) VALUES (N'QUES-0000014', N'CHPT-0000004', N'Easy', N'Fill-in-the-Blanks', N'Trapezium ka area _______ hota hai.', 3)
INSERT [dbo].[test] ([QesID], [ChpID], [QesComplexity], [QesType], [QesQuestion], [Marks]) VALUES (N'QUES-0000015', N'CHPT-0000004', N'Moderate', N'Fill-in-the-Blanks', N'Circle ka perimeters _______ hota hai.', 1)
INSERT [dbo].[test] ([QesID], [ChpID], [QesComplexity], [QesType], [QesQuestion], [Marks]) VALUES (N'QUES-0000016', N'CHPT-0000004', N'Easy', N'Multiple Choice', N'FS level se ek samay mein ______ mtr gap nap sakte hai.', 6)
INSERT [dbo].[test] ([QesID], [ChpID], [QesComplexity], [QesType], [QesQuestion], [Marks]) VALUES (N'QUES-0000021', N'CHPT-0000004', N'Moderate', N'Fill-in-the-Blanks', N'Gap measuring set No 4 se hum ____ se _____ fit nape sakte hai.', 1)
INSERT [dbo].[test] ([QesID], [ChpID], [QesComplexity], [QesType], [QesQuestion], [Marks]) VALUES (N'QUES-0000022', N'CHPT-0000004', N'Easy', N'Short Notes', N'Abney level se ____ se ____ slope padh sakte hai.', 2)Each question have its specific question type, complexity and marks and i want to filter questions for a sum of given marks (i.e. 20 or 30 marks) on a specific condition.
Example: Show me questions for a sum of 4 marks where question type is 'Fill-in-the-Blanks' and question complexity is easy.
Result will be like:
Thanks
-
I can see where this is going, but there's really no good way to ask the database that question. The problem is that the Marks value you are looking to filter on is a SUM, that depends on creating the correct set of questions that has that particular SUM. Any given value might NOT exist in any given set of questions, or, it may exist for several different combinations of questions. Unless your Marks values for EVERY individual question are very selective; e.g. a question can only have a Marks value of 1, 2, or 3; just being able to generate all possible sets of questions in order to then filter out to only those sets with a specific sum, could be quite challenging, and is also quite clearly WELL BEYOND THE SCOPE of the kind of assistance offered in a forum like this. As there are existing commercial products for which this kind of solution does exist, and clearly would represent intellectual property, I don't think this is something anyone is going to solve here. You're going to need your own company resources to develop such a project to fruition.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply