October 17, 2013 at 1:58 pm
I have an application that generates questionnaires with multiple choice questions in several categories. I created a table-valued function that takes a set of answers and compares it to the previous set of answers and aggregates the scores. The TVF works perfectly but takes forever to process large amounts of scores since I have to use the CROSS APPLY method to generate the scores. I'm trying to convert it into something set based but I can't seem to come up with a method that generates the same values. The main problem revolves around the fact that the number of questions in each category may change from one time to the next. The TVF uses a left join with parameters inside the subqueries since I can't reference the identifiers in the subqueries. I originally tried to join this to another query as set based but couldn't get the LEFT JOIN to function properly. It always ended up with an inner join that altered the scores. The identifiers are determined by a date field in another part of the query to display the results.
It seems like a simple problem to solve but I can't seem to come up with a solution. My goal to to be able to put this inline with another query and join it based on the identifier column.
Something like.
some code that displays client details...
JOIN ( Code that calculates 2 scores based on most recent questionnaires)
ON IDENTIFIER = Node1
I'm sure I'm leaving some info out but here's what I have currently.
Example data:
Set #1: Note the 7 questions for category 62
CATEGORY Question_ID ANSWER_VALUE IDENT1
----------- ----------- ------------ -----------
61 533 2 45350
61 534 0 45350
61 535 1 45350
61 536 2 45350
61 537 1 45350
61 538 0 45350
61 539 0 45350
61 540 0 45350
61 541 0 45350
61 542 0 45350
62 543 0 45350
62 544 0 45350
62 545 0 45350
62 546 1 45350
62 547 0 45350
62 548 0 45350
62 549 0 45350
(17 row(s) affected)
Set #2: Note the 6 questions for category 62
CATEGORY Question_ID ANSWER_VALUE IDENT1
----------- ----------- ------------ -----------
61 533 2 52583
61 534 0 52583
61 535 1 52583
61 536 2 52583
61 537 1 52583
61 538 0 52583
61 539 0 52583
61 540 0 52583
61 541 0 52583
61 542 0 52583
62 544 0 52583
62 545 0 52583
62 546 1 52583
62 547 0 52583
62 548 0 52583
62 549 0 52583
(16 row(s) affected)
Output from TVF:
CATEGORY Score1 Score2
----------- --------------------------------------- ---------------------------------------
61 6.000000 6.000000
62 1.428570 1.666660
Warning: Null value is eliminated by an aggregate or other SET operation.
(4 row(s) affected)
TVF:
DECLARE
@NODE1 VARCHAR(8) = '45350',
@NODE2 VARCHAR(8) = '52583'
SELECT
SCORES.CATEGORY,
AVG(CASE WHEN SCORES.ANSWER_VALUE IN (0,1,2,3) THEN SCORES.ANSWER_VALUE*1.0 ELSE NULL END)*10.0 AS Score1,
AVG(CASE WHEN SCORES2.ANSWER_VALUE IN (0,1,2,3) THEN SCORES2.ANSWER_VALUE*1.0 ELSE NULL END)*10.0 AS Score2
FROM
(SELECT ANS.CATEGORY,
ANS.Question_ID,
ANS.ANSWER_VALUE,
ANS.IDENT1
FROM ANSWERS AS ANS
WHERE ANS.IDENT1 = @NODE1
) AS SCORES
LEFT JOIN
(SELECT ANS.CATEGORY,
ANS.Question_ID,
ANS.ANSWER_VALUE,
ANS.IDENT1
FROM ANSWERS AS ANS
WHERE ANS.IDENT1 = @NODE2
) AS SCORES2 ON SCORES.CATEGORY = SCORES2.CATEGORY
AND SCORES.Question_ID = SCORES2.Question_ID
GROUP BY SCORES.CATEGORY
October 17, 2013 at 3:10 pm
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2013 at 3:23 pm
I get to the same result, but I might be missing information from you.
Will you always have 2 IDENT1 values? How would you show that?
For additional values, you might need to use some dynamic sql, but I'm uncertain without further information.
WITH SampleData(CATEGORY, Question_ID, ANSWER_VALUE, IDENT1) AS(
SELECT 61, 533, 2, 45350 UNION ALL
SELECT 61, 534, 0, 45350 UNION ALL
SELECT 61, 535, 1, 45350 UNION ALL
SELECT 61, 536, 2, 45350 UNION ALL
SELECT 61, 537, 1, 45350 UNION ALL
SELECT 61, 538, 0, 45350 UNION ALL
SELECT 61, 539, 0, 45350 UNION ALL
SELECT 61, 540, 0, 45350 UNION ALL
SELECT 61, 541, 0, 45350 UNION ALL
SELECT 61, 542, 0, 45350 UNION ALL
SELECT 62, 543, 0, 45350 UNION ALL
SELECT 62, 544, 0, 45350 UNION ALL
SELECT 62, 545, 0, 45350 UNION ALL
SELECT 62, 546, 1, 45350 UNION ALL
SELECT 62, 547, 0, 45350 UNION ALL
SELECT 62, 548, 0, 45350 UNION ALL
SELECT 62, 549, 0, 45350 UNION ALL
SELECT 61, 533, 2, 52583 UNION ALL
SELECT 61, 534, 0, 52583 UNION ALL
SELECT 61, 535, 1, 52583 UNION ALL
SELECT 61, 536, 2, 52583 UNION ALL
SELECT 61, 537, 1, 52583 UNION ALL
SELECT 61, 538, 0, 52583 UNION ALL
SELECT 61, 539, 0, 52583 UNION ALL
SELECT 61, 540, 0, 52583 UNION ALL
SELECT 61, 541, 0, 52583 UNION ALL
SELECT 61, 542, 0, 52583 UNION ALL
SELECT 62, 544, 0, 52583 UNION ALL
SELECT 62, 545, 0, 52583 UNION ALL
SELECT 62, 546, 1, 52583 UNION ALL
SELECT 62, 547, 0, 52583 UNION ALL
SELECT 62, 548, 0, 52583 UNION ALL
SELECT 62, 549, 0, 52583
),
Scores AS(
SELECT *, DENSE_RANK() OVER(ORDER BY IDENT1) ranked
FROM SampleData
)
SELECT CATEGORY,
AVG(CASE WHEN ANSWER_VALUE IN (0,1,2,3) AND ranked = 1
THEN ANSWER_VALUE*1.0 END)* 10.0 AS Score1,
AVG(CASE WHEN ANSWER_VALUE IN (0,1,2,3) AND ranked = 2
THEN ANSWER_VALUE*1.0 END)* 10.0 AS Score2
FROM Scores
GROUP BY CATEGORY
October 18, 2013 at 10:19 am
Thanks for the help. I think I over-simplified a couple things. The datetime value is key to determining the evaluation and the identifiers that represent the answers. I'm calculating the score for one evaluation and comparing it to the previous evaluation. I created the table-valued function since that was the only way I could get the left join to function properly. When I join the scores to the set of evaluations it only does the aggregation with inner joins.
Here is some sample data to illustrate what I'm trying to accomplish.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#evalanswers','U') IS NOT NULL
DROP TABLE #evalanswers
IF OBJECT_ID('TempDB..#evaluations','U') IS NOT NULL
DROP TABLE #evaluations
IF OBJECT_ID('TempDB..#filter','U') IS NOT NULL
DROP TABLE #filter
--===== Create the test tables with
CREATE TABLE #evalanswers
(Ident1 INT NOT NULL,
Question_ID INT NOT NULL,
Answer_ID INT NULL,
Answer_Value INT NULL
CONSTRAINT pk_evalanswers PRIMARY KEY CLUSTERED
(Ident1 ASC,
Question_ID ASC
)
)
CREATE TABLE #evaluations
(Complete BIT NULL,
Ident1 INT NOT NULL,
Account VARCHAR(15) NULL,
EvalDate DATETIME NULL
)
CREATE TABLE #filter -- Purpose of this table is to select specific questions for analysis
(Module_ID INT NOT NULL,
Question_ID INT NOT NULL,
Answer_ID INT NOT NULL
)
--===== Populate test tables
INSERT INTO #evaluations (Complete, Ident1, Account, EvalDate)
SELECT '1','52484','182173','Jun 9 2013 12:00AM' UNION ALL
SELECT '1','32692','182173','Dec 4 2011 12:00AM' UNION ALL
SELECT '1','38885','182173','Jun 2 2012 12:00AM' UNION ALL
SELECT '1','45251','182173','Dec 1 2012 12:00AM'
INSERT INTO #evalanswers (Ident1, Question_ID, Answer_ID, Answer_Value)
SELECT '32692','517','2074','2' UNION ALL
SELECT '32692','518','2078','2' UNION ALL
SELECT '32692','519','2082','2' UNION ALL
SELECT '32692','520','2085','1' UNION ALL
SELECT '32692','521','2092','-1' UNION ALL
SELECT '32692','522','2096','3' UNION ALL
SELECT '32692','523','2098','1' UNION ALL
SELECT '32692','524','2102','1' UNION ALL
SELECT '32692','525','2107','2' UNION ALL
SELECT '32692','526','2110','1' UNION ALL
SELECT '32692','527','2114','1' UNION ALL
SELECT '32692','528','2119','2' UNION ALL
SELECT '32692','533','2139','2' UNION ALL
SELECT '32692','534','2141','0' UNION ALL
SELECT '32692','535','2146','1' UNION ALL
SELECT '32692','536','2151','2' UNION ALL
SELECT '32692','537','2154','1' UNION ALL
SELECT '32692','538','2157','0' UNION ALL
SELECT '32692','539','2161','0' UNION ALL
SELECT '32692','540','2165','0' UNION ALL
SELECT '32692','541','2169','0' UNION ALL
SELECT '32692','542','2173','0' UNION ALL
SELECT '32692','543','2177','0' UNION ALL
SELECT '32692','544','2181','0' UNION ALL
SELECT '32692','545','2185','0' UNION ALL
SELECT '32692','546','2190','1' UNION ALL
SELECT '32692','547','2193','0' UNION ALL
SELECT '32692','548','2197','0' UNION ALL
SELECT '32692','549','2201','0' UNION ALL
SELECT '38885','517','2074','2' UNION ALL
SELECT '38885','518','2078','2' UNION ALL
SELECT '38885','519','2082','2' UNION ALL
SELECT '38885','520','2085','1' UNION ALL
SELECT '38885','521','2091','3' UNION ALL
SELECT '38885','522','2096','3' UNION ALL
SELECT '38885','523','2098','1' UNION ALL
SELECT '38885','524','2102','1' UNION ALL
SELECT '38885','525','2107','2' UNION ALL
SELECT '38885','526','2110','1' UNION ALL
SELECT '38885','527','2114','1' UNION ALL
SELECT '38885','528','2119','2' UNION ALL
SELECT '38885','533','2140','3' UNION ALL
SELECT '38885','534','2141','0' UNION ALL
SELECT '38885','535','2146','1' UNION ALL
SELECT '38885','536','2151','2' UNION ALL
SELECT '38885','537','2154','1' UNION ALL
SELECT '38885','538','2157','0' UNION ALL
SELECT '38885','539','2161','0' UNION ALL
SELECT '38885','540','2165','0' UNION ALL
SELECT '38885','541','2169','0' UNION ALL
SELECT '38885','542','2173','0' UNION ALL
SELECT '38885','543','2177','0' UNION ALL
SELECT '38885','544','2181','0' UNION ALL
SELECT '38885','545','2185','0' UNION ALL
SELECT '38885','546','2190','1' UNION ALL
SELECT '38885','547','2193','0' UNION ALL
SELECT '38885','548','2197','0' UNION ALL
SELECT '38885','549','2201','0' UNION ALL
SELECT '45251','517','2073','1' UNION ALL
SELECT '45251','518','2078','2' UNION ALL
SELECT '45251','519','2081','1' UNION ALL
SELECT '45251','520','2085','1' UNION ALL
SELECT '45251','521','2091','3' UNION ALL
SELECT '45251','522','2096','3' UNION ALL
SELECT '45251','523','2098','1' UNION ALL
SELECT '45251','524','2102','1' UNION ALL
SELECT '45251','525','2107','2' UNION ALL
SELECT '45251','526','2110','1' UNION ALL
SELECT '45251','527','2114','1' UNION ALL
SELECT '45251','528','2119','2' UNION ALL
SELECT '45251','533','2139','2' UNION ALL
SELECT '45251','534','2141','0' UNION ALL
SELECT '45251','535','2146','1' UNION ALL
SELECT '45251','536','2151','2' UNION ALL
SELECT '45251','537','2154','1' UNION ALL
SELECT '45251','538','2157','0' UNION ALL
SELECT '45251','539','2161','0' UNION ALL
SELECT '45251','540','2165','0' UNION ALL
SELECT '45251','541','2169','0' UNION ALL
SELECT '45251','542','2173','0' UNION ALL
SELECT '45251','543','2177','0' UNION ALL
SELECT '45251','544','2181','0' UNION ALL
SELECT '45251','545','2185','0' UNION ALL
SELECT '45251','546','2190','1' UNION ALL
SELECT '45251','547','2193','0' UNION ALL
SELECT '45251','548','2197','0' UNION ALL
SELECT '45251','549','2201','0' UNION ALL
SELECT '52484','517','2073','1' UNION ALL
SELECT '52484','518','2078','2' UNION ALL
SELECT '52484','519','2082','2' UNION ALL
SELECT '52484','520','2086','2' UNION ALL
SELECT '52484','521','2091','3' UNION ALL
SELECT '52484','522','2096','3' UNION ALL
SELECT '52484','523','2098','1' UNION ALL
SELECT '52484','524','2103','2' UNION ALL
SELECT '52484','525','2108','3' UNION ALL
SELECT '52484','526','2110','1' UNION ALL
SELECT '52484','527','2115','2' UNION ALL
SELECT '52484','528','2119','2' UNION ALL
SELECT '52484','533','2139','2' UNION ALL
SELECT '52484','534','2141','0' UNION ALL
SELECT '52484','535','2146','1' UNION ALL
SELECT '52484','536','2151','2' UNION ALL
SELECT '52484','537','2154','1' UNION ALL
SELECT '52484','538','2157','0' UNION ALL
SELECT '52484','539','2161','0' UNION ALL
SELECT '52484','540','2165','0' UNION ALL
SELECT '52484','541','2169','0' UNION ALL
SELECT '52484','542','2173','0' UNION ALL
SELECT '52484','544','2181','0' UNION ALL
SELECT '52484','545','2185','0' UNION ALL
SELECT '52484','546','2190','1' UNION ALL
SELECT '52484','547','2193','0' UNION ALL
SELECT '52484','548','2197','0' UNION ALL
SELECT '52484','549','2201','0'
INSERT INTO #filter (Module_ID, Question_ID, Answer_ID)
SELECT '61','533','2137' UNION ALL
SELECT '61','533','2138' UNION ALL
SELECT '61','533','2139' UNION ALL
SELECT '61','533','2140' UNION ALL
SELECT '61','534','2141' UNION ALL
SELECT '61','534','2142' UNION ALL
SELECT '61','534','2143' UNION ALL
SELECT '61','534','2144' UNION ALL
SELECT '61','535','2145' UNION ALL
SELECT '61','535','2146' UNION ALL
SELECT '61','535','2147' UNION ALL
SELECT '61','535','2148' UNION ALL
SELECT '61','536','2149' UNION ALL
SELECT '61','536','2150' UNION ALL
SELECT '61','536','2151' UNION ALL
SELECT '61','536','2152' UNION ALL
SELECT '61','537','2153' UNION ALL
SELECT '61','537','2154' UNION ALL
SELECT '61','537','2155' UNION ALL
SELECT '61','537','2156' UNION ALL
SELECT '61','538','2157' UNION ALL
SELECT '61','538','2158' UNION ALL
SELECT '61','538','2159' UNION ALL
SELECT '61','538','2160' UNION ALL
SELECT '61','539','2161' UNION ALL
SELECT '61','539','2162' UNION ALL
SELECT '61','539','2163' UNION ALL
SELECT '61','539','2164' UNION ALL
SELECT '61','540','2165' UNION ALL
SELECT '61','540','2166' UNION ALL
SELECT '61','540','2167' UNION ALL
SELECT '61','540','2168' UNION ALL
SELECT '61','541','2169' UNION ALL
SELECT '61','541','2170' UNION ALL
SELECT '61','541','2171' UNION ALL
SELECT '61','541','2172' UNION ALL
SELECT '61','542','2173' UNION ALL
SELECT '61','542','2174' UNION ALL
SELECT '61','542','2175' UNION ALL
SELECT '61','542','2176' UNION ALL
SELECT '62','543','2177' UNION ALL
SELECT '62','543','2178' UNION ALL
SELECT '62','543','2179' UNION ALL
SELECT '62','543','2180' UNION ALL
SELECT '62','544','2181' UNION ALL
SELECT '62','544','2182' UNION ALL
SELECT '62','544','2183' UNION ALL
SELECT '62','544','2184' UNION ALL
SELECT '62','545','2185' UNION ALL
SELECT '62','545','2186' UNION ALL
SELECT '62','545','2187' UNION ALL
SELECT '62','545','2188' UNION ALL
SELECT '62','546','2189' UNION ALL
SELECT '62','546','2190' UNION ALL
SELECT '62','546','2191' UNION ALL
SELECT '62','546','2192' UNION ALL
SELECT '62','547','2193' UNION ALL
SELECT '62','547','2194' UNION ALL
SELECT '62','547','2195' UNION ALL
SELECT '62','547','2196' UNION ALL
SELECT '62','548','2197' UNION ALL
SELECT '62','548','2198' UNION ALL
SELECT '62','548','2199' UNION ALL
SELECT '62','548','2200' UNION ALL
SELECT '62','549','2201' UNION ALL
SELECT '62','549','2202' UNION ALL
SELECT '62','549','2203' UNION ALL
SELECT '62','549','2204'
--===== TVF that generates correct scores but only evaluates one set at a time
DECLARE
@NODE1 VARCHAR(8) = '45251',
@NODE2 VARCHAR(8) = '52484'
SELECT
SCORES.Ident1,
SCORES.Module_ID,
AVG(CASE WHEN SCORES.ANSWER_VALUE IN (0,1,2,3) THEN SCORES.ANSWER_VALUE*1.0 ELSE NULL END)*10.0 AS Score1,
AVG(CASE WHEN SCORES2.ANSWER_VALUE IN (0,1,2,3) THEN SCORES2.ANSWER_VALUE*1.0 ELSE NULL END)*10.0 AS Score2
FROM
(SELECT FIL.Module_ID,
FIL.Question_ID,
ANS.ANSWER_VALUE,
ANS.Ident1
FROM #evalanswers AS ANS
JOIN #filter AS FIL ON ANS.Answer_ID = FIL.Answer_ID
JOIN #evaluations EVL ON EVL.Ident1 = ANS.Ident1
WHERE ANS.Answer_Value IN (-1,0,1,2,3)
AND EVL.COMPLETE = 1
AND ANS.Ident1 = @NODE1
) AS SCORES
LEFT JOIN
(SELECT FIL.Module_ID,
FIL.Question_ID,
ANS.ANSWER_VALUE,
ANS.Ident1
FROM #evalanswers AS ANS
JOIN #filter AS FIL ON ANS.Answer_ID = FIL.Answer_ID
JOIN #evaluations EVL ON EVL.Ident1 = ANS.Ident1
WHERE ANS.Answer_Value IN (-1,0,1,2,3)
AND EVL.COMPLETE = 1
AND ANS.Ident1 = @NODE2
) AS SCORES2 ON SCORES.Module_ID = SCORES2.Module_ID
AND SCORES.Question_ID = SCORES2.Question_ID
GROUP BY SCORES.Ident1,
SCORES.Module_ID
--===== Query that could be joined to another query on the Ident1 column.
SELECT EVAL.Account,
EVAL.Ident1 AS IdTime2,-- This identifier represents the most recent evaluation
TWOSCORES.Ident1 AS IdTime1,-- This identifier is the previous evaluation
EVAL.EvalDate,-- Date of evaluation
TWOSCORES.Score1,
TWOSCORES.Score2
FROM #evaluations AS EVAL
JOIN (
SELECT
SCORES.Ident1,
SCORES2.Ident1 AS Ident2,
SCORES.Module_ID,
AVG(CASE WHEN SCORES.ANSWER_VALUE IN (0,1,2,3) THEN SCORES.ANSWER_VALUE*1.0 ELSE NULL END)*10.0 AS Score1,
AVG(CASE WHEN SCORES2.ANSWER_VALUE IN (0,1,2,3) THEN SCORES2.ANSWER_VALUE*1.0 ELSE NULL END)*10.0 AS Score2
FROM
(SELECT FIL.Module_ID,
FIL.Question_ID,
ANS.ANSWER_VALUE,
ANS.Ident1
FROM #evalanswers AS ANS
JOIN #filter AS FIL ON ANS.Answer_ID = FIL.Answer_ID
JOIN #evaluations EVL ON EVL.Ident1 = ANS.Ident1
WHERE ANS.Answer_Value IN (-1,0,1,2,3)
AND EVL.COMPLETE = 1
) AS SCORES
LEFT JOIN -- Previous evaluations may contain questions not in current evals.
(SELECT FIL.Module_ID,
FIL.Question_ID,
ANS.ANSWER_VALUE,
ANS.Ident1
FROM #evalanswers AS ANS
JOIN #filter AS FIL ON ANS.Answer_ID = FIL.Answer_ID
JOIN #evaluations EVL ON EVL.Ident1 = ANS.Ident1
WHERE ANS.Answer_Value IN (-1,0,1,2,3)
AND EVL.COMPLETE = 1
) AS SCORES2 ON SCORES.Module_ID = SCORES2.Module_ID
AND SCORES.Question_ID = SCORES2.Question_ID
GROUP BY SCORES.Ident1,
SCORES2.Ident1,
SCORES.Module_ID
) AS TWOSCORES ON TWOSCORES.Ident1 = (SELECT TOP 1 Ident1 -- Identifiers are not guaranteed to be sequential
FROM #evaluations
WHERE Account = EVAL.Account
AND EvalDate < EVAL.EvalDate
ORDER BY EvalDate DESC)
AND TWOSCORES.Ident2 = EVAL.Ident1
ORDER BY EVAL.EvalDate DESC
My results for the second query look like this:
Account IdTime2 IdTime1 EvalDate Score1 Score2
--------------- ----------- ----------- ----------------------- --------------------------------------- ---------------------------------------
182173 52484 45251 2013-06-09 00:00:00.000 6.000000 6.000000
182173 52484 45251 2013-06-09 00:00:00.000 1.666660 1.666660
182173 45251 38885 2012-12-01 00:00:00.000 7.000000 6.000000
182173 45251 38885 2012-12-01 00:00:00.000 1.428570 1.428570
182173 38885 32692 2012-06-02 00:00:00.000 6.000000 7.000000
182173 38885 32692 2012-06-02 00:00:00.000 1.428570 1.428570
(6 row(s) affected)
The second row Score1 should be 1.428570 instead of 1.666660. I hope this makes more sense.
October 18, 2013 at 1:21 pm
This seems to work as desired.
WITH Scores AS(
SELECT EVL.Account,
EVL.Ident1,
EVL.EvalDate,
FIL.Module_ID,
AVG(CASE WHEN ANSWER_VALUE IN (0,1,2,3) THEN ANSWER_VALUE*1.0 ELSE NULL END)*10.0 AS Score,
DENSE_RANK() OVER(ORDER BY EVL.Ident1 DESC) ranking
FROM #evalanswers AS ANS
JOIN #filter AS FIL ON ANS.Answer_ID = FIL.Answer_ID
JOIN #evaluations EVL ON EVL.Ident1 = ANS.Ident1
WHERE ANS.Answer_Value IN (-1,0,1,2,3)
AND EVL.COMPLETE = 1
GROUP BY EVL.Account,
EVL.Ident1,
EVL.EvalDate,
FIL.Module_ID
)
SELECT s2.Account,
s2.Module_ID,
MAX( s2.Ident1) AS IdTime2,
MAX( s1.Ident1) AS IdTime1,
s2.EvalDate,
MAX( s1.Score) AS Score1,
MAX( s2.Score) AS Score2
FROM Scores s1
JOIN Scores s2 ON s1.ranking = s2.ranking + 1
AND s1.Module_ID = s2.Module_ID
AND s1.Account = s2.Account
GROUP BY s2.Account, s2.EvalDate, s2.Module_ID
ORDER BY IdTime2 DESC
To clarify, I'm getting the scores (with the formula) before the JOIN. That way, you won't loose rows in your calculation. Using a self-join of the calculated recordset, you can have 2 scores compared at a time with infinite possible rows. The DENSE_RANK() function is to have a value to join with the previous evaluation.
October 22, 2013 at 12:08 pm
Thanks for the help. I had to make a minor edit but it works great. I need to sort the groups by the date instead of the identifier since there are times when those numbers get out of order. I tested the change on a much larger data set and the query times went from minutes to seconds. 😀
Here is the version of the CTE I used.
WITH Scores AS(
SELECT EVL.Account,
EVL.Ident1,
EVL.EvalDate,
FIL.Module_ID,
AVG(CASE WHEN ANSWER_VALUE IN (0,1,2,3) THEN ANSWER_VALUE*1.0 ELSE NULL END)*10.0 AS Score,
DENSE_RANK() OVER(PARTITION BY EVL.Account ORDER BY EVL.EvalDate DESC) ranking
FROM #evalanswers AS ANS
JOIN #filter AS FIL ON ANS.Answer_ID = FIL.Answer_ID
JOIN #evaluations EVL ON EVL.Ident1 = ANS.Ident1
WHERE ANS.Answer_Value IN (-1,0,1,2,3)
AND EVL.COMPLETE = 1
GROUP BY EVL.Account,
EVL.Ident1,
EVL.EvalDate,
FIL.Module_ID
)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply