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 2012 Forums
 Transact-SQL (2012)
 How to update columns

Author  Topic 

paulnamroud
Starting Member

26 Posts

Posted - 2014-10-31 : 09:49:28
Hello,

I have the following table content.

Record Id Start_Date Interval_In_Minutes
--------------------------------------------------------------------------------------------
1 2014-10-30 00:00:00 20
2 2014-10-30 00:00:00 20
3 2014-10-30 00:00:00 20
4 2014-10-30 00:00:00 20
5 2014-10-30 00:00:00 20
6 2014-10-30 00:00:00 20

7 2014-10-31 00:00:00 30
8 2014-10-31 00:00:00 30

9 2014-11-01 00:00:00 60

10 2014-11-02 00:00:00 15
11 2014-11-02 00:00:00 15
12 2014-11-02 00:00:00 15
13 2014-11-02 00:00:00 15
14 2014-11-02 00:00:00 15
15 2014-11-02 00:00:00 15
16 2014-11-02 00:00:00 15
17 2014-11-02 00:00:00 15
18 2014-11-02 00:00:00 15
19 2014-11-02 00:00:00 15


My objective is to have to following result by using one SQL statement to update Start_Date and calculate the column End_Date.
Does anyone can help me ?



Record Id Start_Date End_Date
--------------------------------------------------------------------------------------------
1 2014-10-30 00:00:00 2014-10-30 00:20:00
2 2014-10-30 00:20:00 2014-10-30 00:40:00
3 2014-10-30 00:40:00 2014-10-30 01:00:00
4 2014-10-30 01:00:00 2014-10-30 01:20:00
5 2014-10-30 01:20:00 2014-10-30 01:40:00
6 2014-10-30 01:40:00 2014-10-30 02:00:00

7 2014-10-31 00:00:00 2014-10-31 00:30:00
8 2014-10-31 00:30:00 2014-10-31 01:00:00

9 2014-11-01 00:00:00 2014-11-01 01:00:00
10 2014-11-02 00:00:00 2014-11-02 00:15:00
11 2014-11-02 00:15:00 2014-11-02 00:30:00
12 2014-11-02 00:30:00 2014-11-02 00:45:00
13 2014-11-02 00:45:00 2014-11-02 01:00:00
14 2014-11-02 01:00:00 2014-11-02 01:15:00
15 2014-11-02 01:15:00 2014-11-02 01:30:00
16 2014-11-02 01:30:00 2014-11-02 01:45:00
17 2014-11-02 01:45:00 2014-11-02 02:00:00
18 2014-11-02 02:00:00 2014-11-02 02:15:00
19 2014-11-02 02:15:00 2014-11-02 02:30:00


Note: I know that I can create a cursor and loop through the table and I will take so much time. So i'm looking for a fast solution

Thank you

Paul

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-31 : 10:12:18
do it like in the example below. You can probably abbreviate or omit some of the "rows between" clauses but, i have put the conditions explicitly so you can see what it is trying to do
create table #A(recordid int, Start_Date	 datetime, Interval_In_Minutes int);
insert into #A values (1,'2014-10-30 00:00:00', 20),
(1,'2014-10-30 00:00:00', 20), (1,'2014-10-30 00:00:00', 20), (1,'2014-10-30 00:00:00', 20);


select
recordid,
dateadd
(
mi,
COALESCE(sum(Interval_In_Minutes) over (partition by Start_Date order by recordid
rows between unbounded preceding and 1 preceding),0),
Start_Date
) as Start_Date ,
dateadd
(
mi,
COALESCE(sum(Interval_In_Minutes) over (partition by Start_Date order by recordid
rows between unbounded preceding and current row),0),
Start_Date
) as Start_Date
from
#A;

drop table #A
Go to Top of Page
   

- Advertisement -