- Mailing list pgsql-sql

<p><font face="Arial Narrow">Could someone please tell me why this is scoring all BUT my first row of data?</font><br
/><fontface="Arial Narrow">Thank you.</font><br /><p><font face="Arial Narrow">CREATE PROCEDURE scoreBDI_sp
AS</font><p><fontface="Arial Narrow">DECLARE X CURSOR</font><br /><font face="Arial Narrow">for SELECT * FROM
BDI</font><br/><p><font face="Arial Narrow">DECLARE @STUDYID int</font><br /><font face="Arial Narrow">DECLARE @ADATE
datetime</font><br/><font face="Arial Narrow">DECLARE @TPOINT smallint</font><br /><font face="Arial Narrow">DECLARE
@PORCnvarchar</font><br /><font face="Arial Narrow">DECLARE @FNAME nvarchar</font><br /><font face="Arial
Narrow">DECLARE@LNAME nvarchar</font><br /><font face="Arial Narrow">DECLARE @BDI1 smallint</font><br /><font
face="ArialNarrow">DECLARE @BDI2 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI3 smallint</font><br
/><fontface="Arial Narrow">DECLARE @BDI4 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI5
smallint</font><br/><font face="Arial Narrow">DECLARE @BDI6 smallint</font><br /><font face="Arial Narrow">DECLARE
@BDI7smallint</font><br /><font face="Arial Narrow">DECLARE @BDI8 smallint</font><br /><font face="Arial
Narrow">DECLARE@BDI9 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI10 smallint</font><br /><font
face="ArialNarrow">DECLARE @BDI11 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI12 smallint</font><br
/><fontface="Arial Narrow">DECLARE @BDI13 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI14
smallint</font><br/><font face="Arial Narrow">DECLARE @BDI15 smallint</font><br /><font face="Arial Narrow">DECLARE
@BDI16smallint</font><br /><font face="Arial Narrow">DECLARE @BDI17 smallint</font><br /><font face="Arial
Narrow">DECLARE@BDI18 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI19 smallint</font><br /><font
face="ArialNarrow">DECLARE @BDI20 smallint</font><br /><font face="Arial Narrow">DECLARE @BDI21 smallint</font><br
/><fontface="Arial Narrow">DECLARE @TOTAL float</font><br /><font face="Arial Narrow">DECLARE @NUMANS float</font><br
/><fontface="Arial Narrow">DECLARE @BDITOT float</font><br /><font face="Arial Narrow">DECLARE @CLEANED char</font><br
/><fontface="Arial Narrow">DECLARE @NEW char</font><p><font face="Arial Narrow">SET @TOTAL=0</font><br /><font
face="ArialNarrow">SET @BDITOT=0</font><br /><font face="Arial Narrow">SET @NUMANS=0</font><p><font face="Arial
Narrow">OPENX</font><p><font face="Arial Narrow">FETCH NEXT FROM X INTO @STUDYID, @ADATE, @TPOINT, @PORC, @FNAME,
@LNAME,@BDI1, @BDI2, @BDI3, @BDI4, @BDI5, @BDI6, @BDI7, @BDI8, @BDI9, @BDI10, @BDI11, @BDI12, @BDI13, @BDI14, @BDI15,
@BDI16,@BDI17, @BDI18, @BDI19, @BDI20, @BDI21, @TOTAL, @NUMANS, @BDITOT, @CLEANED, @NEW</font><p><font face="Arial
Narrow">WHILE(@@FETCH_status=0) BEGIN</font><p>        <font face="Arial Narrow">IF @BDI1 <> 999 </font><br
/>       <font face="Arial Narrow">       BEGIN</font><br />        <font face="Arial Narrow">        SET
@TOTAL=@TOTAL+@BDI1</font><br/>        <font face="Arial Narrow">        SET @NUMANS=@NUMANS+1</font><br />       
<fontface="Arial Narrow">       END   </font><p>        <font face="Arial Narrow">IF @BDI2 <> 999</font><br
/>       <font face="Arial Narrow">       BEGIN</font><br />        <font face="Arial Narrow">        SET
@TOTAL=@TOTAL+@BDI2</font><br/>        <font face="Arial Narrow">        SET @NUMANS=@NUMANS+1</font><br />       
<fontface="Arial Narrow">       END</font><br /><font face="Arial Narrow">     </font><br />        <font face="Arial
Narrow">IF @BDI3 <> 999</font><br />        <font face="Arial Narrow">       BEGIN</font><br />        <font
face="ArialNarrow">        SET @TOTAL=@TOTAL+@BDI3</font><br />        <font face="Arial Narrow">        SET
@NUMANS=@NUMANS+1</font><br/>        <font face="Arial Narrow">       END</font><br /><font face="Arial Narrow">   
</font><br/>        <font face="Arial Narrow">    IF @BDI4 <> 999</font><br />        <font face="Arial
Narrow">      BEGIN</font><br />        <font face="Arial Narrow">        SET @TOTAL=@TOTAL+@BDI4</font><br />       
<fontface="Arial Narrow">        SET @NUMANS=@NUMANS+1</font><br />        <font face="Arial Narrow">      
END</font><br/>        <font face="Arial Narrow">     </font><br />        <font face="Arial Narrow">    IF @BDI5
<>999</font><br />        <font face="Arial Narrow">        BEGIN</font><br />        <font face="Arial
Narrow">        SET @TOTAL=@TOTAL+@BDI5</font><br />        <font face="Arial Narrow">         SET
@NUMANS=@NUMANS+1</font><br/>        <font face="Arial Narrow">        END</font><br />        <font face="Arial
Narrow">   </font><br />        <font face="Arial Narrow">    IF @BDI6 <> 999</font><br />        <font
face="ArialNarrow">       BEGIN</font><br />        <font face="Arial Narrow">        SET @TOTAL=@TOTAL+@BDI6</font><br
/>       <font face="Arial Narrow">        SET @NUMANS=@NUMANS+1</font><br />        <font face="Arial Narrow">      
END</font><br/>        <font face="Arial Narrow">    </font><br />        <font face="Arial Narrow">    IF @BDI7
<>999</font><br />        <font face="Arial Narrow">        BEGIN</font><br />        <font face="Arial
Narrow">        SET @TOTAL=@TOTAL+@BDI7</font><br />        <font face="Arial Narrow">         SET
@NUMANS=@NUMANS+1</font><br/>        <font face="Arial Narrow">        END</font><br />        <font face="Arial
Narrow">   </font><br />        <font face="Arial Narrow">    IF @BDI8 <> 999</font><br />        <font
face="ArialNarrow">       BEGIN</font><br />        <font face="Arial Narrow">        SET @TOTAL=@TOTAL+@BDI8</font><br
/>       <font face="Arial Narrow">        SET @NUMANS=@NUMANS+1</font><br />        <font face="Arial Narrow">      
END</font><br/>        <font face="Arial Narrow">     </font><br />        <font face="Arial Narrow">    IF @BDI9
<>999</font><br />        <font face="Arial Narrow">        BEGIN</font><br />        <font face="Arial
Narrow">        SET @TOTAL=@TOTAL+@BDI9</font><br />        <font face="Arial Narrow">         SET
@NUMANS=@NUMANS+1</font><br/>        <font face="Arial Narrow">        END</font><br />        <font face="Arial
Narrow">   </font><br />        <font face="Arial Narrow">    IF @BDI10 <> 999</font><br />        <font
face="ArialNarrow">       BEGIN</font><br />        <font face="Arial Narrow">        SET
@TOTAL=@TOTAL+@BDI10</font><br/>        <font face="Arial Narrow">        SET @NUMANS=@NUMANS+1</font><br />       
<fontface="Arial Narrow">       END</font><br />        <font face="Arial Narrow">    </font><br />        <font
face="ArialNarrow">    IF @BDI11 <> 999</font><br />        <font face="Arial Narrow">       BEGIN</font><br
/>       <font face="Arial Narrow">        SET @TOTAL=@TOTAL+@BDI11</font><br />        <font face="Arial
Narrow">       SET @NUMANS=@NUMANS+1</font><br />        <font face="Arial Narrow">       END</font><br />        <font
face="ArialNarrow">   </font><br />        <font face="Arial Narrow">    IF @BDI12 <> 999</font><br />       
<fontface="Arial Narrow">        BEGIN</font><br />        <font face="Arial Narrow">         SET
@TOTAL=@TOTAL+@BDI12</font><br/>        <font face="Arial Narrow">         SET @NUMANS=@NUMANS+1</font><br />       
<fontface="Arial Narrow">        END</font><br />        <font face="Arial Narrow">      </font><br />        <font
face="ArialNarrow">    IF @BDI13 <> 999</font><br />        <font face="Arial Narrow">         BEGIN</font><br
/>       <font face="Arial Narrow">          SET @TOTAL=@TOTAL+@BDI13</font><br />        <font face="Arial
Narrow">         SET @NUMANS=@NUMANS+1</font><br />        <font face="Arial Narrow">         END</font><br />       
<fontface="Arial Narrow">      </font><br />        <font face="Arial Narrow">    IF @BDI14 <> 999</font><br
/>       <font face="Arial Narrow">         BEGIN</font><br />        <font face="Arial Narrow">          SET
@TOTAL=@TOTAL+@BDI14</font><br/>        <font face="Arial Narrow">          SET @NUMANS=@NUMANS+1</font><br />       
<fontface="Arial Narrow">         END</font><br />        <font face="Arial Narrow">   </font><br />        <font
face="ArialNarrow">    IF @BDI15 <> 999 </font><br />        <font face="Arial Narrow">       BEGIN</font><br
/>       <font face="Arial Narrow">        SET @TOTAL=@TOTAL+@BDI15</font><br />        <font face="Arial
Narrow">       SET @NUMANS=@NUMANS+1</font><br />        <font face="Arial Narrow">       END</font><br />        <font
face="ArialNarrow">    </font><br />        <font face="Arial Narrow">    IF @BDI16 <> 999</font><br />       
<fontface="Arial Narrow">       BEGIN</font><br />        <font face="Arial Narrow">        SET
@TOTAL=@TOTAL+@BDI16</font><br/>        <font face="Arial Narrow">        SET @NUMANS=@NUMANS+1</font><br />       
<fontface="Arial Narrow">       END</font><br />        <font face="Arial Narrow">    </font><br />        <font
face="ArialNarrow">    IF @BDI17 <> 999</font><br />        <font face="Arial Narrow">       BEGIN</font><br
/>       <font face="Arial Narrow">        SET @TOTAL=@TOTAL+@BDI17</font><br />        <font face="Arial
Narrow">       SET @NUMANS=@NUMANS+1</font><br />        <font face="Arial Narrow">       END</font><br />        <font
face="ArialNarrow">    </font><br />        <font face="Arial Narrow">    IF @BDI18 <> 999</font><br />       
<fontface="Arial Narrow">       BEGIN</font><br />        <font face="Arial Narrow">        SET
@TOTAL=@TOTAL+@BDI18</font><br/>        <font face="Arial Narrow">        SET @NUMANS=@NUMANS+1</font><br />       
<fontface="Arial Narrow">       END</font><br />        <font face="Arial Narrow">    </font><br />        <font
face="ArialNarrow">    IF @BDI19 <> 999</font><br />        <font face="Arial Narrow">       BEGIN</font><br
/>       <font face="Arial Narrow">        SET @TOTAL=@TOTAL+@BDI19</font><br />        <font face="Arial
Narrow">       SET @NUMANS=@NUMANS+1</font><br />        <font face="Arial Narrow">       END</font><br />        <font
face="ArialNarrow">    </font><br />        <font face="Arial Narrow">    IF @BDI20 <> 999</font><br />       
<fontface="Arial Narrow">       BEGIN</font><br />        <font face="Arial Narrow">        SET
@TOTAL=@TOTAL+@BDI20</font><br/>        <font face="Arial Narrow">        SET @NUMANS=@NUMANS+1</font><br />       
<fontface="Arial Narrow">       END</font><br />        <font face="Arial Narrow">    </font><br />        <font
face="ArialNarrow">    IF @BDI21 <> 999</font><br />        <font face="Arial Narrow">       BEGIN</font><br
/>       <font face="Arial Narrow">        SET @TOTAL=@TOTAL+@BDI21</font><br />        <font face="Arial
Narrow">       SET @NUMANS=@NUMANS+1</font><br />        <font face="Arial Narrow">       END</font><br />        <font
face="ArialNarrow">      </font><br />        <font face="Arial Narrow">      IF @NUMANS > 16 </font><br />       
<fontface="Arial Narrow">        BEGIN</font><br />        <font face="Arial Narrow">                        SET
@BDITOT=round(@TOTAL * (21.0 /@NUMANS),0)</font><br />                        <br />                <font face="Arial
Narrow">END</font><br />        <font face="Arial Narrow">      ELSE</font><br />                <font face="Arial
Narrow">BEGIN</font><br/>                        <font face="Arial Narrow">SET @BDITOT=999</font><br />               
       <br />                <font face="Arial Narrow">END </font><br />        <br /><font face="Arial Narrow">update
BDIset BDITOT=@BDITOT, TOTAL=@BDITOT, NUMANS=@NUMANS, NEW='N' WHERE [ID]=@studyid and ADATE=@adate and TPOINT=@TPOINT
ANDPORC=@PORC</font><p>        <br />        <br /><font face="Arial Narrow">fetch next from X into @STUDYID, @ADATE,
@TPOINT,@PORC, @FNAME, @LNAME, @BDI1, @BDI2, @BDI3, @BDI4, @BDI5, @BDI6, @BDI7, @BDI8, @BDI9, @BDI10, @BDI11, @BDI12,
@BDI13,@BDI14, @BDI15, @BDI16, @BDI17, @BDI18, @BDI19, @BDI20, @BDI21, @TOTAL, @NUMANS, @BDITOT, @CLEANED,
@NEW</font><p><fontface="Arial Narrow">SET @TOTAL=0</font><br /><font face="Arial Narrow">SET @NUMANS=0</font><br /><br
/><p><fontface="Arial Narrow">END</font><br /><p><font face="Arial Narrow">CLOSE X</font><br /><font face="Arial
Narrow">DEALLOCATEX</font>