February 3, 2011 at 10:18 am
Hi,
I just want some ideas and suggestions on the best techniques to use to implement some logic in SQL ie regular expressions, functions, CTEs, stored procs etc...
I need to build a scoring engine in SQL. My client has a list of projects on their books that all submit questionnaires that have the same questions and a set of possible responses.
Questions on the quesitonnaire are grouped into categories eg Technology categories, Legal categories..
For each project, each response provided in the questionnaire is scored based on a response- score lookup.
Once the scores are generated for each project question, the scores are then put through a first set of formula for each category of question where each question in that category is weighted and then summed
eg Score for technology questions =
(tech Q1 score * tech Q1 weight) +
(tech Q2 score * tech Q2 weight)
Score for legal questions
(legal Q1 score * legal Q1 weight)
Once the overall scores for each of the categories are generated, the categories scores are then reweighted and totalled to provide the score grand total for each project.
eg tech total score * tech total weight + legal total score * legal total weight.
I am very interested in any suggestions on how anyone might approach this issue ie essentially repeating a process
for each project record that involves inputting a set of response scores into a set of formulae to get a total.
My first thought is to have a set of lookup tables and use functions to apply the actual formula. I have also thought of may be using dynamic sql / regular expressions etc as another possibility?
🙂
February 3, 2011 at 1:38 pm
I would try something like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QScores](
[Id] [int] NOT NULL, --to identify who took the test
-- individuals name, etc., in a persons
-- table (if desired)
[Qtype] [varchar](50) NOT NULL CONSTRAINT [DF_QScores_Qtype] DEFAULT (' '),
[Qscore] [decimal](8, 0) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QWeights](
[Qtype] [varchar](50) NULL,
[QWeight] [decimal](4, 1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
To test:
Some sample data:
INSERT INTO dbo.QScores(Id,Qtype,Qscore)
SELECT 10,'TQ1',55 UNION ALL
SELECT 11, 'LQ2',75
INSERT INTO dbo.QWeights(Qtype,QWeight)
SELECT 'TQ1',10.4 UNION ALL
SELECT 'LQ2',50.0
Then to calculate some values:
SELECT Qscores.Qscore * QWeights.QWeight AS 'Weighted Score' FROM Qscores
JOIN QWeights ON
QWeights.Qtype = Qscores.Qtype
Results from sample data:
Weighted Score
572.0
3750.0
Now this might not be the best approach, but is posted here to get your thought process into high gear. Hope it helps.
Edited to add:
Now a question:
Will the weight given to a question be altered at some future time? If so then the Weighted scores for past test takers will also reflect the change. If this is NOT desired then I would change the QScores table and add a column lets call it "WScore" and calculate its value as data was inserted into the QScores table. This in turn would then give you the ability to compare past weighted values with the newly calculated values for whatever that information is worth, and if my experience is typical as sure as the sun rises in the east someone will want that information.
February 4, 2011 at 2:45 am
Thanks for the detailed input. I will take a good look at the code and have a bit of a play around with it. The good thing about the scoring requirement it that I can put together a test model easily with just a couple of projects with questionnaires and responses and if the solution works well simply extend it for the full set of projects, questions etc. This is because the questionnaire structure, possible responses and process for each project would be identical in each case.
According to my client the questions, responses, scorings and weights are very static (ie may be chance once a year?). 🙂
February 4, 2011 at 10:51 am
Nasreenm (2/4/2011)
Thanks for the detailed input. I will take a good look at the code and have a bit of a play around with it. The good thing about the scoring requirement it that I can put together a test model easily with just a couple of projects with questionnaires and responses and if the solution works well simply extend it for the full set of projects, questions etc. This is because the questionnaire structure, possible responses and process for each project would be identical in each case.According to my client the questions, responses, scorings and weights are very static (ie may be chance once a year?). 🙂
According to my client the questions, responses, scorings and weights are very static (ie may be chance once a year?).
For the above, ask the client a detailed specific question about what they will accept or care?. Pardon me for being a skeptic, but to protect by A$$ I would ask for a written answer to my written question. Too often I have encountered the "I thought I heard, what you though you said" and was only able to gain a clear understanding of the requirement by both the client and myself by placing same in writing and both signing off on that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply