July 21, 2011 at 9:21 am
DECLARE @TABLE TABLE(
[time] datetime)
INSERT INTO @TABLE
SELECT'10:30'UNION ALL
SELECT'10:35'UNION ALL
SELECT'11:00'UNION ALL
SELECT'11:10'
SELECT[the SUM] = (DATEDIFF(minute, t1.[time], t2.[time]))
FROM
(
SELECT[time], row = row_number() OVER (ORDER BY [time])
FROM@TABLE
) t1
INNER JOIN
(
SELECT[time], row = row_number() OVER (ORDER BY [time])
FROM@TABLE
) t2
ONt1.row= t2.row - 1
======
Result Set:
THE SUM
5
25
10
Now I need to include an insert statement to insert the result set into another table.
Does any one has an idea about this.
Thanks!
July 21, 2011 at 9:25 am
Insert into dbo.OtherTable (FieldName)
SELECT [the SUM] = (DATEDIFF(minute, t1.[time], t2.[time]))
FROM
(
SELECT [time], row = row_number() OVER (ORDER BY [time])
FROM @TABLE
) t1
INNER JOIN
(
SELECT [time], row = row_number() OVER (ORDER BY [time])
FROM @TABLE
) t2
ON t1.row = t2.row - 1
July 25, 2011 at 6:14 am
You Can Use this Way of Insertion into a Temporary Table
SELECT [the SUM] = (DATEDIFF(minute, t1.[time], t2.[time])) into #p
FROM
(
SELECT [time], row = row_number() OVER (ORDER BY [time])
FROM @TABLE
) t1
INNER JOIN
(
SELECT [time], row = row_number() OVER (ORDER BY [time])
FROM @TABLE
) t2
ON t1.row = t2.row - 1
It works fine and results the same.
Good Day
🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply