Calculation by Row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculation by Row

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2005-06-22 : 21:32:25

Please someone - help the boob - er . . noob

This gives me the correct results in Query Analyzer, but in multiple recordsets. And, of course, my datagrid only returns the first recordset.

Basically, I need to get the @ROW which will change per row as the ParaID increments.

It's basically getting the results from a survey (yes, no) per paragraph.

ALTER PROCEDURE TAM_PROFILE_RESULTS
(@PAGEID INT, @ROW FLOAT OUTPUT)
AS
DECLARE @X INT

SET @X = 1

WHILE @X < 7
BEGIN

SELECT @ROW=COUNT(PAGEID) FROM TAM_P_Data where PageID=@PAGEID AND ParaID=@X --right here

PRINT @ROW

SELECT TOP 100 PERCENT dbo.TAM_Profiles.Profile, dbo.TAM_P_Data.PageID,dbo.TAM_P_Data.ParaID,
((COUNT(dbo.TAM_P_Data.Choose)/@ROW)) AS TOTAL,
dbo.TAM_P_Data.Choose, dbo.TAM_Profiles.ID

FROM dbo.TAM_P_Data RIGHT OUTER JOIN
dbo.TAM_Profiles ON dbo.TAM_P_Data.ParaID = dbo.TAM_Profiles.ParaID AND dbo.TAM_P_Data.PageID = dbo.TAM_Profiles.PageID

GROUP BY dbo.TAM_Profiles.Profile, dbo.TAM_P_Data.PageID, dbo.TAM_P_Data.ParaID, dbo.TAM_P_Data.Choose, dbo.TAM_Profiles.ID
HAVING (dbo.TAM_P_Data.Choose = 0) AND (dbo.TAM_P_Data.PageID = @PAGEID) and (dbo.TAM_P_Data.ParaID = @X)
ORDER BY dbo.TAM_P_Data.PageID

SET @X = @X + 1
print @X

END

I know it's mangled crap, but I'm counting on the SQLTeam magic! I've been messing with this WAY too long! Is there a better WAY??

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2005-07-13 : 22:44:27

Well . . . I'm still struggling with this. Was wondering if anyone has a suggestion? Maybe a better way to accomplish? I have a survey which people vote on per paragraph. I'm trying to do a results page where I have to count the number of people that have voted on each paragraph (cause not every paragraph will get a vote) and how many agree with the statement. Then

totalperparagraph/choose_yes = %agree

Make sense? A challenge for any guru!

Thanks!

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 01:25:00

Put
SET NOCOUNT ON
after the "AS"

(That doesn't solve your problem! but will prevent some unnecessary info. going to the client)

Would this do any good?:

Create a temporary table at the top of your SProc (perhaps including the value of @X in a column), INSERT each of your 7 SELECT statements into the temporary table, and then
SELECT * FROM TemporaryTable
at the end

Kristen

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2005-07-14 : 08:34:17

Kristen - you are AWESOME! Thanks so much for replying. I think your suggestion just might work. I'll give it a shot and let you know. The temp table seems like a logical choice.

BTW: My thoughts are with your country - please accept my sympathy for the loved ones lost.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources