|
|
Posted by Roy Harvey (SQL Server MVP) on 11/05/07 16:56
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@yahoo.com> 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
Navigation:
[Reply to this message]
|