Hi,

The following SELECT statement works fine and returns what I need as
one of the columns in my query. I have to create more than 10 of the
selection statements like this and then UNION them into a final query.
What I want to do is to have an example to show me how create a
function to pass the column name and the table name to create the
query.

SELECT LN_NR, ROUND(CAST(amountTotal AS DECIMAL(7, 2)) /
positionTotal, 0) AS AverageYearsinPresentPosition
FROM (SELECT SURV_YY_DT, LN_NR, SUM(TOT_PSTN_OFFCR_NR) AS
positionTotal, SUM(TOT_YY_PSTN_NR) AS amountTotal
FROM (SELECT TOP 100 PERCENT
SURV_YY_DT, LN_NR, TOT_PSTN_OFFCR_NR, TOT_YY_PSTN_NR
FROM
dbo.FLT_OFFCR_SLRY_SURV_DTL AS FLT_OFFCR_SLRY_SURV_DTL_1
WHERE
(TOT_YY_PSTN_NR > 0)
ORDER BY LN_NR) AS
presentYears_1
GROUP BY SURV_YY_DT, LN_NR) AS presentYears
WHERE (SURV_YY_DT = '2007')

Thank you.

Faye Larson

I'm not a big fan of dynamic SQL, which is what you appear to be
asking about, so I'm not a good source of advice on that.

I am a fan of simplified SQL though. I believe the following will
provide the same results as the query posted without any derived
tables or meaningless TOP 100 PERCENT.

SELECT LN_NR,
ROUND(CAST(SUM(TOT_YY_PSTN_NR) AS DECIMAL(7, 2)) /
SUM(TOT_PSTN_OFFCR_NR), 0) AS AverageYearsinPresentPosition
FROM dbo.FLT_OFFCR_SLRY_SURV_DTL AS presentYears_1
WHERE TOT_YY_PSTN_NR > 0
AND SURV_YY_DT = '2007'
GROUP BY LN_NR

Roy Harvey
Beacon Falls, CT

On Mon, 05 Nov 2007 15:49:52 -0000, Faye <fzl20 (AT) yahoo (DOT) com> wrote: