Suggestions

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

Re: Suggestions

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 wrote:

>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