data diff between rows

Hi All,
in my database i have counter values and datestamp:
2008-01-17 16:15:10.293 516,9989
2008-01-17 16:30:09.577 517,0241
2008-01-17 16:45:10.420 517,0357
2008-01-17 17:00:10.060 517,0602
2008-01-17 17:15:10.700 517,0728
etc.
i my report i need data differece at every hour. I have no idea how to get
it (from this hour summarised value substract last hours summarised value)
2008-01-17 16 0,0368 (=517,0357-517,0357)
2008-01-17 17 0,0371 (=517,0728-517,0357)
thanks for advise
bala nematehmmm.. no answer.
it is to hard or to simple to you?;]
bn
"bala nemate" <bala.nemateDELETETHIS@.gmail.com> wrote in message
news:uqQCMHbWIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> in my database i have counter values and datestamp:
> 2008-01-17 16:15:10.293 516,9989
> 2008-01-17 16:30:09.577 517,0241
> 2008-01-17 16:45:10.420 517,0357
> 2008-01-17 17:00:10.060 517,0602
> 2008-01-17 17:15:10.700 517,0728
>
> etc.
> i my report i need data differece at every hour. I have no idea how to get
> it (from this hour summarised value substract last hours summarised value)
> 2008-01-17 16 0,0368 (=517,0357-517,0357)
> 2008-01-17 17 0,0371 (=517,0728-517,0357)
> thanks for advise
> bala nemate
>|||On Jan 20, 9:47=A0pm, "bala nemate" <bala.nemateDELETET...@.gmail.com>
wrote:
> hmmm.. no answer.
> it is to hard or to simple to you?;]
> bn
> "bala nemate" <bala.nemateDELETET...@.gmail.com> wrote in message
> news:uqQCMHbWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
> > Hi All,
> > in my database i have counter values and datestamp:
> > 2008-01-17 16:15:10.293 =A0 =A0 516,9989
> > 2008-01-17 16:30:09.577 =A0 =A0 517,0241
> > 2008-01-17 16:45:10.420 =A0 =A0 517,0357
> > 2008-01-17 17:00:10.060 =A0 =A0 517,0602
> > 2008-01-17 17:15:10.700 =A0 =A0 517,0728
> > etc.
> > i my report i need data differece at every hour. I have no idea how to g=et
> > it (from this hour summarised value substract last hours summarised valu=e)
> > 2008-01-17 16 =A0 =A00,0368 =A0 =A0 (=3D517,0357-517,0357)
> > 2008-01-17 17 =A0 =A00,0371 =A0 =A0 (=3D517,0728-517,0357)
> > thanks for advise
> > bala nemate- Hide quoted text -
> - Show quoted text -
First a subquery (or CTE) that shows the counter for each hour instead
of each quarter.
The join this CTE with itself to match each hour to the previous.
WITH Hours AS(
SELECT Hour =3D convert(varchar(13), TimestampColName, 120),
Counter =3D max(CounterColName),
RowNum =3D row_number() over (Order by
convert(varchar(13), TimestampColName, 120))
FROM TableName
WHERE ...
GROUP BY convert(varchar(13), TimestampColName, 120)
)
SELECT H1.Hour, Diff =3DH1.Counter-H2.Counter
FROM Hours H1
JOIN Hours H2 ON H1.RowNum =3D H2.RowNum+1|||Hi RBot,
well, i wanted to do this only on ssrs side.
on sql side i did exactly this idea you are suggesting, but in more
difficult way (well, i am not DB programer :) )
thanks man, you impoved my sql knowledge a lot ! :)
br
bala nemate
"RBot" <renato_buda@.iprimus.com.au> wrote in message
news:66c16db1-470f-4d1d-bba2-f06f9e1cc25d@.y5g2000hsf.googlegroups.com...
On Jan 20, 9:47 pm, "bala nemate" <bala.nemateDELETET...@.gmail.com>
wrote:
> hmmm.. no answer.
> it is to hard or to simple to you?;]
> bn
> "bala nemate" <bala.nemateDELETET...@.gmail.com> wrote in message
> news:uqQCMHbWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
> > Hi All,
> > in my database i have counter values and datestamp:
> > 2008-01-17 16:15:10.293 516,9989
> > 2008-01-17 16:30:09.577 517,0241
> > 2008-01-17 16:45:10.420 517,0357
> > 2008-01-17 17:00:10.060 517,0602
> > 2008-01-17 17:15:10.700 517,0728
> > etc.
> > i my report i need data differece at every hour. I have no idea how to
> > get
> > it (from this hour summarised value substract last hours summarised
> > value)
> > 2008-01-17 16 0,0368 (=517,0357-517,0357)
> > 2008-01-17 17 0,0371 (=517,0728-517,0357)
> > thanks for advise
> > bala nemate- Hide quoted text -
> - Show quoted text -
First a subquery (or CTE) that shows the counter for each hour instead
of each quarter.
The join this CTE with itself to match each hour to the previous.
WITH Hours AS(
SELECT Hour = convert(varchar(13), TimestampColName, 120),
Counter = max(CounterColName),
RowNum = row_number() over (Order by
convert(varchar(13), TimestampColName, 120))
FROM TableName
WHERE ...
GROUP BY convert(varchar(13), TimestampColName, 120)
)
SELECT H1.Hour, Diff =H1.Counter-H2.Counter
FROM Hours H1
JOIN Hours H2 ON H1.RowNum = H2.RowNum+1