How to update columns - 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 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources