If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Multi-row calculations

I have the following code snippet that returns two datetimes (each originally
on their own row, the difference between the two, and the difference between
two values (each associated with the original datetimes).

Re: Multi-row calculations

Thanks for the response. I was hoping to avoid using a cursor.
I think I've come up with a solution that makes use of a second temporary
table. I create another table with an additional identity column, insert
the records from the original, then change the where clause to exclude all
but the next row. More specifically...

Re: Multi-row calculations

A self join does look like the best solution. The only problem I foresee is
your insert statments. If you will be using the data in an application, your
recordset won't be populated correctly unless you use SET NOCOUNT ON before
the inserts, and SET NOCOUNT OFF after the inserts.

Re: Multi-row calculations

I'm a little confused. Why would turning NOCOUNT on or off affect any inserts
into the table? I've seen it have affect on MS Access where Access thinks
a stored procedure is done before it's really done but I don't see how it
would have affects in this case.

"Daniel Reber" <daniel@domain-group.com> wrote:
>
>A self join does look like the best solution. The only problem I foresee
is
>your insert statments. If you will be using the data in an application,
your
>recordset won't be populated correctly unless you use SET NOCOUNT ON before
>the inserts, and SET NOCOUNT OFF after the inserts.
>
>Daniel Reber, MCP
>

Re: Multi-row calculations

It all depends on what the data is used for. If it is just being used in SQL
Server, then it probably won't be needed. But if the data is being sent to
a VB applicaion to a ADO recordset, or MS Access like you said, then you
will get an empty recordset because control will be returned to the application
before the data has been retrieved. Again, if the data will be used only
in SQL, then this is a mute point.

Daniel Reber, MCP

"Bob Hines" <bobhi@pectech.com> wrote:
>
>I'm a little confused. Why would turning NOCOUNT on or off affect any inserts
>into the table? I've seen it have affect on MS Access where Access thinks
>a stored procedure is done before it's really done but I don't see how it
>would have affects in this case.
>
>"Daniel Reber" <daniel@domain-group.com> wrote:
>>
>>A self join does look like the best solution. The only problem I foresee
>is
>>your insert statments. If you will be using the data in an application,
>your
>>recordset won't be populated correctly unless you use SET NOCOUNT ON before
>>the inserts, and SET NOCOUNT OFF after the inserts.
>>
>>Daniel Reber, MCP
>>
>