Help to Write one select CURSOR who sum one field in the last recno and put it one actual recno on selected field.

Question

Post reply

Help to Write one select CURSOR who sum one field in the last recno and put it one actual recno on selected field.

maiquelrinco

SSC Enthusiast

Points: 149
More actions
November 19, 2008 at 12:54 pm

#70952

Hi all,
I´m newbie in T-SQL and i´m learing about create cursor and it´s very dificult to me create one cursor for one function I need.
Here is what I need.
I have one table
Field1 Field2 Field3
5 90 Result is the subtract of 90 - 0
2 34 Result is the subtract of 90 - 34
3 75 Result is the subtract of 34 - 75
Some one has any idea of one solution in T-SQL?
I need to create one select with 3 fields, the field3 is the result of subtract of the last recno field2 as
I try to show in my table.
Regards
Maiquel Rinco
Easter Island
Chile

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Please refer to the link in my signature for how to post table definition / sample data here.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

maiquelrinco SSC Enthusiast Points: 149 More actions · Answer 2

Hi all,

I´m newbie in T-SQL and i´m learing about create cursor and it´s very dificult to me create one cursor for one function I need.

Here is what I need.

I have one table

CREATE TABLE [dbo].[controlcloro](

[idcontrol] [int] IDENTITY(1,1) NOT NULL,

[fechahora] [datetime] NOT NULL,

[nropozo] [numeric](2, 0) NOT NULL,

[capacidad] [numeric](5, 2) NOT NULL,

[nivel] [numeric](5, 2) NOT NULL,

[salida] [varchar](10) COLLATE Modern_Spanish_CI_AI NOT NULL,

[horometro] [numeric](18, 2) NOT NULL,

[kwh] [numeric](18, 2) NOT NULL,

[m3salida] [numeric](18, 2) NOT NULL,

[llenado] [varchar](10) COLLATE Modern_Spanish_CI_AI NOT NULL,

[litros] [numeric](5, 2) NULL,

[obs] [varchar](max) COLLATE Modern_Spanish_CI_AI NULL

) ON [PRIMARY]

I need to create one select with 3 fields(fechahora,m3salida,one new column), the supposed field3(new column) is the result of subtract of the last recno field2(m3salida) as

i try to show in one sample data result under.

fechahora m3salida (no name column)

2008-11-17 00:00:00.000569512.00this field result is the subtract of 569512 - 0

2008-11-17 00:00:00.000966786.00this field result is the subtract of 966786 - 569512

2008-11-17 00:00:00.000465119.00this field result is the subtract of 465119 - 966786

Some one has any idea of one solution in T-SQL?

Regards

Maiquel Rinco

Easter Island

Chile

Max Yasnytsky SSCrazy Points: 2462 More actions · Answer 3

below script should work for you

select

a.fechahora

,a.m3salida

,a.m3salida- coalesce(b.m3salida,0)

(

select row_number () over (order by idcontrol) [rownumber]

fechahora

,m3salida

from [dbo].[controlcloro]

) a

left join

(

select row_number () over (order by idcontrol) [rownumber]

fechahora

,m3salida

from [dbo].[controlcloro]

) b

on a.[rownumber]-1=b.[rownumber]

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

Max this op is using SQL 2000 ROW_NUMBER() won't work

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Try this:

DECLARE @Tmp TABLE

(id INT IDENTITY(1,1)

,fechahora DATETIME

,m3salida DECIMAL(15,8))

INSERT INTO @Tmp

(fechahora,m3salida)

SELECT '2008-11-17',569512.00 UNION all

SELECT '2008-11-17',966786.00 UNION all

SELECT '2008-11-17',465119.00

SELECT

[1].ID

,[1].fechahora

,[1].m3salida

,[2].m3salida

,[1].m3salida- isnull([2].m3salida,0)

FROM @Tmp [1]

LEFT JOIN @Tmp [2]

ON [1].id = [2].id +1

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

No cursor is needed. This is a variation on the running total problem.

Read this article, it may help you figure out this problem, and if not ask for more help.

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

maiquelrinco SSC Enthusiast Points: 149 More actions · Answer 7

/*------------------------

select

a.fechahora

,a.m3salida

,a.m3salida- coalesce(b.m3salida,0)

(

select row_number () over (order by idcontrol) [rownumber]

fechahora

,m3salida

from [dbo].[controlcloro]

) a

left join

(

select row_number () over (order by idcontrol) [rownumber]

fechahora

,m3salida

from [dbo].[controlcloro]

) b

on a.[rownumber]-1=b.[rownumber]

------------------------*/

Msg 102, Level 15, State 1, Line 7

Sintaxis incorrecta cerca de 'fechahora'.

Msg 102, Level 15, State 1, Line 14

Sintaxis incorrecta cerca de 'fechahora'.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

You can't use ROW_NUMBER() in SQL 2000 or SQL 7 you need to use another approach.

Try my solution or try reading lynn's post about running totals!

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

maiquelrinco SSC Enthusiast Points: 149 More actions · Answer 9

Christopher Stobbs (11/20/2008)
Try this:
DECLARE @Tmp TABLE
(id INT IDENTITY(1,1)
,fechahora DATETIME
,m3salida DECIMAL(15,8))
INSERT INTO @Tmp
(fechahora,m3salida)
SELECT '2008-11-17',569512.00 UNION all
SELECT '2008-11-17',966786.00 UNION all
SELECT '2008-11-17',465119.00
SELECT
[1].ID
,[1].fechahora
,[1].m3salida
,[2].m3salida
,[1].m3salida- isnull([2].m3salida,0)
FROM @Tmp [1]
LEFT JOIN @Tmp [2]
ON [1].id = [2].id +1

Hi.. I don´t need exactly the value as you sample in your query

SELECT '2008-11-17',569512.00 UNION all

SELECT '2008-11-17',966786.00 UNION all

SELECT '2008-11-17',465119.00

I need the value of this field subtract from the value on the same field, BUT in the before record. did you understand ?

Thx

Regards

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 10

I'm not sure what you mean...

What should the actual results be?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Garadin One Orange Chip Points: 29613 More actions · Answer 11

Hi.. I don´t need exactly the value as you sample in your query
SELECT '2008-11-17',569512.00 UNION all
SELECT '2008-11-17',966786.00 UNION all
SELECT '2008-11-17',465119.00
I need the value of this field subtract from the value on the same field, BUT in the before record. did you understand ?

To clarify... that's not part of his solution. That is him providing sample data for your problem, which you didn't do as per the article I sent you to. The query is below it.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

maiquelrinco SSC Enthusiast Points: 149 More actions · Answer 12

Christopher Stobbs (11/20/2008)
You can't use ROW_NUMBER() in SQL 2000 or SQL 7 you need to use another approach.
Try my solution or try reading lynn's post about running totals!
Thanks
Chris

Sorry but i´m newbie in T-SQL and I don´t understand much about this method in this article.

I´m not sure if all people understand what I need...I think it´s simple, using one comand who I don´t known to capture the field of after recno() -1

and use it one calc of one field.

i´m not expert in sql but seems like this: select fechahora, m3salida, (m3salida - (recno(m3salida) -1) from controlcloro

I assume the recno is a function to return the value of one field and -1 is to put the pointer in the after record.

Regards and thx a lot for try to help me.

Maiquel Rinco

Easter Island

Chile.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

This is the table you provided:

CREATE TABLE [dbo].[controlcloro](

[idcontrol] [int] IDENTITY(1,1) NOT NULL,

[fechahora] [datetime] NOT NULL,

[nropozo] [numeric](2, 0) NOT NULL,

[capacidad] [numeric](5, 2) NOT NULL,

[nivel] [numeric](5, 2) NOT NULL,

[salida] [varchar](10) COLLATE Modern_Spanish_CI_AI NOT NULL,

[horometro] [numeric](18, 2) NOT NULL,

[kwh] [numeric](18, 2) NOT NULL,

[m3salida] [numeric](18, 2) NOT NULL,

[llenado] [varchar](10) COLLATE Modern_Spanish_CI_AI NOT NULL,

[litros] [numeric](5, 2) NULL,

[obs] [varchar](max) COLLATE Modern_Spanish_CI_AI NULL

) ON [PRIMARY]

In your original post, you indicated a third field was to be computed from the value of a column from the current record - the value from the same column in the previous record (or 0 if the current record is the first), which column is supposed to be updated in your table above?

Sergiy SSC Guru Points: 110208 More actions · Answer 14

Christopher Stobbs (11/20/2008)
You can't use ROW_NUMBER() in SQL 2000 or SQL 7

VARCHAR(MAX) either.

But it's used in the table definition.

_____________
Code for TallyGenerator