Reply to Re: Suggestions

Your name:

Reply:


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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация