need help creating a timeline - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need help creating a timeline

Author

Topic

the_justin
Starting Member

19 Posts

Posted - 2009-03-08 : 20:49:09

Hi all,
I have a table similar to this:

timecode datestart dateend totalweight
A 07:00 07:02 2
A 07:20 07:21 1
A 07:30 07:32 3
B 07:05 07:06 2
B 07:07 07:09 2
B 07:40 07:41 1

now i'm aiming to create a table like this from the above table

A 07:00 07:02 2
B 07:05 07:09 4 -- this is from 07:05-07:06 and 07:07-7:09
A 07:20 07:32 4 -- this is from 07:20-07:21 and 07:30-07:32
B 07:40 07:41 1

anyone have an idea of how to do this ?

Thank you in advance

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-08 : 21:06:04

What is the criteria of startdate and enddate that defines SUM or not SUM ?

the_justin
Starting Member

19 Posts

Posted - 2009-03-08 : 21:30:19

hi sodeep, thanks for replying,
the aggregation is base on the timecode and timeline.
so in the mentioned example, first sort it by datestart to be like this:

A 07:00 07:02 2
B 07:05 07:06 2
B 07:07 07:09 2
A 07:20 07:21 3
A 07:30 07:32 1
B 07:40 07:41 1

then from here, to this:
A 07:00 07:02 2
B 07:05 07:09 4
A 07:20 07:32 4
B 07:40 07:41 1

hope this makes sense to you.

Thank you

the_justin
Starting Member

19 Posts

Posted - 2009-03-08 : 22:10:26

got the answer.
using
SELECT
CN = ROW_NUMBER() OVER (ORDER BY datestart)
- ROW_NUMBER() OVER (PARTITION BY timecode ORDER BY datestart),
timecode,
datestart,
dateend,
totalweight
FROM @data

this article will explain it:
http://www.sqlmag.com/Article/ArticleID/93606/sql_server_93606.html

thanks to everyone reading this and specially andrewd.smith.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources