Returning Records

Question

Post reply

Returning Records

Ry-SQL

SSChasing Mays

Points: 601
More actions
May 9, 2007 at 11:14 am

#153574

Hi,
I am trying to get a result set back which gives me values which subtract one row from the previous row.
Existing Table Structure:
id dtDate
1 5/1/2007
2 5/3/2007
3 5/15/2007
4 5/17/2007
Query should return this:
dtDate AccumaltiveTime DailyTime
5/1/2007 0 0
5/3/2007 2 2
5/15/2007 14 12
5/17/2007 16 2
I am having trouble getting the "daily time", my query so far is as follows: this returns 14 as the DailyTime for every record, which is wrong.
select b.id,dtdate,
datediff(dd,(select dtdate from tbl_test z where z.id = (select min(id) from tbl_test) ),dtdate) AccumaltiveTime,
(datediff(dd,(select dtdate from tbl_test z where z.id = (select min(id) from tbl_test) ),dtdate)) - ((datediff(
dd,(select dtdate from tbl_test z where z.id = (select max(id) from tbl_test where id < (select max(id) from tbl_test ) ) ),
dtdate))) DailyTime
from tbl_test b
thanks for all who can help!

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

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

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 1

you'll need a cursor and a temp table (or table variable)

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

David McFarland SSChampion Points: 11815 More actions · Answer 2

This should do the trick for you, if you want things in id order, which it appears you do:

DECLARE

@FirstDate datetime

SET

@FirstDate = (SELECT Min(dtDate) FROM DateTest)

SELECT

dtDate = curr.dtDate

,AccumulativeTime = Datediff(day, @FirstDate, curr.dtDate)

,DailyTime = Datediff(day, IsNull(prev.dtDate, curr.dtDate), curr.dtDate)

FROM

DateTest AS curr

LEFT OUTER JOIN DateTest AS prev ON

curr.[id] = prev.[id] + 1

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Yup, no need for a cursor nor a temp table. However this is one rare case when the cursors and temp tables beat the set based (in case of triangular join) query.

Ry-SQL SSChasing Mays Points: 601 More actions · Answer 4

Ry-SQL

SSChasing Mays

Points: 601

May 9, 2007 at 11:48 am

#705332

awesome

this worked..

thanks!

Ry-SQL SSChasing Mays Points: 601 More actions · Answer 5

actually this doesnt work!

i had tried a similar solution, however i worry about the ID's not being in succession. In my example, the ID's are 1,2,3,5.

David McFarland SSChampion Points: 11815 More actions · Answer 6

Yes, it only works when they are incremental and with no gaps. If we need to avoid looping for this type of thing, and if the existing identity column has gaps, we move the necessary data to a new table, in the proper order, using the Identity attribute during the move. Then this will work.

Ry-SQL SSChasing Mays Points: 601 More actions · Answer 7

i failed to make my ID's 1,2,3,5 in my example...but this query would fail if the ID's are not in order

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Or another way to do this :

- Insert into temp table in the correct order

- Run a single update statement calculating the running total

- Select from temp table

Option c is to do this calculation client side with the reporting tools or whatever is available, but this is not always a possibility.

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

Or you can try this:

select

a.dtDate,

datediff(dd,(select min(b.dtDate) from dbo.testtbl b where b.dtDate <= a.dtDate), a.dtDate) as AccumTime,

datediff(dd,isnull((select max(c.dtDate) from dbo.testtbl c where c.dtDate < a.dtDate), a.dtDate), a.dtDate) as DailyTime

from

dbo.testtbl a

No cursors, no temp tables!

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

Lynn Pettis

SSC Guru

Points: 442467

May 9, 2007 at 12:05 pm

#705346

Got beat, oh well!

David McFarland SSChampion Points: 11815 More actions · Answer 11

Which method you should use depends on your needs, table sizes, reusability, etc. In our environment, SELECT INTOs don't present any problems, and are extremely fast, which considering the sizes of the tables we work with, is the most important piece. That may or may not match your environment, but if it does, then the following should work (untested):

SELECT

[id] = Identity(int, 1, 1)

,dtDate = a.dtDate

INTO

dbo.DateTest

FROM

<your table> AS a

ORDER BY

a.[id]

CREATE INDEX idx#id ON dbo.DateTest([id])

DECLARE

@FirstDate datetime

SET

@FirstDate = (SELECT Min(dtDate) FROM DateTest)

SELECT

dtDate = curr.dtDate

,AccumulativeTime = Datediff(day, @FirstDate, curr.dtDate)

,DailyTime = Datediff(day, IsNull(prev.dtDate, curr.dtDate), curr.dtDate)

FROM

DateTest AS curr

LEFT OUTER JOIN DateTest AS prev ON

curr.[id] = prev.[id] + 1

Ry-SQL SSChasing Mays Points: 601 More actions · Answer 12

Lynn Pettis, you get a gold star.

this worked perfectly.

thanks for everyones input...it was an interesting topic.

noeld SSC Guru Points: 96590 More actions · Answer 13

Hopefully your tables are *really small* that implemetation could not get off the ground on one of my tables

Cheers,

* Noel

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

Also depends on indexes as well. If the datefield is indexed it should still run well, but I guess we'd have to find someone willing to test it on a VLDBT.