November 19, 2008 at 12:54 pm
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
November 19, 2008 at 1:22 pm
Please refer to the link in my signature for how to post table definition / sample data here.
November 20, 2008 at 9:22 am
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
November 20, 2008 at 9:32 am
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]
November 20, 2008 at 9:34 am
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]
November 20, 2008 at 9:41 am
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]
November 20, 2008 at 9:46 am
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://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
November 20, 2008 at 10:18 am
/*------------------------
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'.
November 20, 2008 at 10:21 am
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]
November 20, 2008 at 10:23 am
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
November 20, 2008 at 10:29 am
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]
November 20, 2008 at 10:33 am
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.
November 20, 2008 at 10:37 am
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.
November 20, 2008 at 10:47 am
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?
November 20, 2008 at 8:29 pm
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply