March 8, 2009 at 6:40 pm
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
timecode datestart dateend weight
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
March 8, 2009 at 6:58 pm
Hi Justin
What are the criteria to aggregate the time spans?
Greets
Flo
March 8, 2009 at 7:07 pm
the aggregation is base on the the timecode and the timeline.
so in this example:
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
I'll want to sum the first A, then sum of first and second B, then the second and third A, then the third B.
so from the 1st table, it should be orderd by timestart before it gets group-ed.
from 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
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
thank you for your reply.
March 8, 2009 at 7:43 pm
This method used to solve this problem of combining sequential rows originated from the following article:
http://www.sqlmag.com/Article/ArticleID/93606/sql_server_93606.html
DECLARE @data TABLE (
timecode char(1),
datestart datetime,
dateend datetime,
totalweight int
)
/* Insert test data */
INSERT INTO @data (timecode, datestart, dateend, totalweight)
SELECT 'A','07:00','07:02',2 UNION ALL
SELECT 'A','07:20','07:21',1 UNION ALL
SELECT 'A','07:30','07:32',3 UNION ALL
SELECT 'B','07:05','07:06',2 UNION ALL
SELECT 'B','07:07','07:09',2 UNION ALL
SELECT 'B','07:40','07:41',1
;WITH cteSEQ AS (
SELECT
CN = ROW_NUMBER() OVER (ORDER BY datestart)
- ROW_NUMBER() OVER (PARTITION BY timecode ORDER BY datestart),
timecode,
datestart,
dateend,
totalweight
FROM @data
)
SELECT
S.timecode,
MIN(S.datestart) AS datestart,
MAX(S.dateend) AS dateend,
SUM(totalweight) AS weight
FROM cteSEQ S
GROUP BY S.timecode, S.CN
ORDER BY MIN(S.datestart)
The ROW_NUMBER difference expression in the CTE gives the same value for consecutive rows with the same timecode when ordered by the datestart column, and it is always a different value from that for other rows with the same timecode that are not in the same consecutive sequence. This calculated value can therefore be used together with the timecode column in the GROUP BY clause of the SELECT statement to group consecutive rows with the same timecode.
The results from the query are:
[font="Courier New"]timecode datestart dateend weight
-------- ----------------------- ----------------------- -----------
A 1900-01-01 07:00:00.000 1900-01-01 07:02:00.000 2
B 1900-01-01 07:05:00.000 1900-01-01 07:09:00.000 4
A 1900-01-01 07:20:00.000 1900-01-01 07:32:00.000 4
B 1900-01-01 07:40:00.000 1900-01-01 07:41:00.000 1
(4 row(s) affected)[/font]
March 8, 2009 at 7:50 pm
you are the best...
it is exactly how i want it..
thanks so much.
March 8, 2009 at 8:07 pm
Glad to help, but I'm not as good as whoever thought up that ROW_NUMBER difference trick in the first place.
--Andrew
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply