Timestamp Update — A Sample


http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7c2985fc-1ede-43e4-bd40-9b66f3bf7fda

DECLARE @s TABLE ( Startdt DATETIME,Hours DECIMAL(6, 2))

INSERT INTO @s
VALUES (‘2012-08-09 23:00:00.000’,8.00),
(‘2012-08-09 07:00:00.000’,8.00),
(‘2012-08-09 15:00:00.000’,0.80),
(‘2012-08-09 15:00:00.000’,7.00),
(‘2012-08-09 15:00:00.000’,0.20),
(‘2012-08-10 23:00:00.000’,5.40),
(‘2012-08-10 23:00:00.000’,2.60);

WITH mycte
AS (SELECT startdt,Hours,ROW_NUMBER() OVER(Partition BY startdt ORDER BY startdt) rn FROM @s)

,mycte2
AS (SELECT startdt,MAX([1]) AS c1,MAX([2]) AS c2,MAX([3]) AS c3
FROM (SELECT startdt, Hours, rn FROM mycte) src
PIVOT (MAX(hours) FOR rn IN ([1], [2], [3])) pvt
GROUP BY startdt)

SELECT CASE WHEN col = ‘c1’ THEN startdt
WHEN col = ‘c2’ THEN e1
WHEN col = ‘c3’ THEN e2
END AS stardt,
Hours,
CASE WHEN col = ‘c1’ THEN e1
WHEN col = ‘c2’ THEN e2
WHEN col = ‘c3’ THEN e3
END AS enddt
FROM (SELECT startdt,c1,c2,c3,DATEADD(minute, c1 * 60, startdt) e1,
DATEADD(minute,( c1 + c2 ) * 60, startdt) e2,DATEADD(minute,( c1 + c2 + c3 ) * 60, startdt) e3 FROM mycte2)src
UNPIVOT(Hours FOR col IN (c1, c2, c3)) unpvt

–Result
/*

2012-08-09 07:00:00.000 8.00000 2012-08-09 15:00:00.000
2012-08-09 15:00:00.000 0.80000 2012-08-09 15:48:00.000
2012-08-09 15:48:00.000 7.00000 2012-08-09 22:48:00.000
2012-08-09 22:48:00.000 0.20000 2012-08-09 23:00:00.000
2012-08-09 23:00:00.000 8.00000 2012-08-10 07:00:00.000
2012-08-10 23:00:00.000 5.40000 2012-08-11 04:24:00.000
2012-08-11 04:24:00.000 2.60000 2012-08-11 07:00:00.000

*/

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s