Converting table-value function to set based query

Question

Post reply

Converting table-value function to set based query

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ross.heironimus@aspireindiana.org Ten Centuries Points: 1241 More actions · Answer 3

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ross.heironimus@aspireindiana.org Ten Centuries Points: 1241 More actions · Answer 5

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

)