SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding Seconds
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 07/25/2006 :  02:37:38  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Hi,

i have a huge table.One of the columns of the table is fTime, which is datetime data type. But there is NO seconds in the fTime column. What i wanna do is to add seconds to this column according to the example below;

2006-07-24 18:10:00----> 2006-07-24 18:10:00
2006-07-24 18:10:00----> 2006-07-24 18:10:59

2006-07-25 10:20:00----> 2006-07-25 10:20:00
2006-07-25 10:20:00----> 2006-07-25 10:20:15
2006-07-25 10:20:00----> 2006-07-25 10:20:30
2006-07-25 10:20:00----> 2006-07-25 10:20:45
2006-07-25 10:20:00----> 2006-07-25 10:20:59

2006-07-25 12:00:00----> 2006-07-25 12:00:00
2006-07-25 12:00:00----> 2006-07-25 12:00:30
2006-07-25 12:00:00----> 2006-07-25 12:00:59


Shortly, as you can understand from above, the first second should be 00, the last second should be 59 and the rest will be calculated by, 60/count of same fTime rows - except the last row. There is one important thing to consider, which is the speed of the statements which include those calculations because of the huge number of rows i my table.

thanks

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/25/2006 :  03:04:12  Show Profile  Reply with Quote
To be honest I'm a little bit confused about your objective but to add seconds to a datetime datatype all you do is this:

SELECT DATEADD(ss, 15, myDateField) AS newDate

DATEADD itself is really fast (it's a native sql server function) but you'll have to elaborate a bit on your problem to make it clear what you want to do.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 07/25/2006 :  03:20:15  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
2006-07-24 18:10:00----> 2006-07-24 18:10:00 --------> I have 2 rows of the same fTime column so i just put 00 seconds to
2006-07-24 18:10:00----> 2006-07-24 18:10:59 the first row and 59 seconds to the last row thats easy.

2006-07-25 10:20:00----> 2006-07-25 10:20:00 -------> when i have more than one rows, again i put 00 second to the first
2006-07-25 10:20:00----> 2006-07-25 10:20:15 row and 59 seconds to the last one. To calculate the in between rows, i
2006-07-25 10:20:00----> 2006-07-25 10:20:30 need to make a little calculation. Skip the last row, there is 4 rows.
2006-07-25 10:20:00----> 2006-07-25 10:20:45 60 seconds / 4 rows = 15 seconds. We found the seconds of the
2006-07-25 10:20:00----> 2006-07-25 10:20:59 row number 2. Than for the third row add another 15 seconds and so on.

2006-07-25 12:00:00----> 2006-07-25 12:00:00 ------> Again the same story, first row is 00 seconds, last row is 59 seconds. 60
2006-07-25 12:00:00----> 2006-07-25 12:00:30 seconds / 2 rows (excluding the last row always) = 30 seconds. We put the
2006-07-25 12:00:00----> 2006-07-25 12:00:59 30 seconds to the row number 2.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/25/2006 :  04:07:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try this one! EDIT: No need for primary key.
-- Prepare test data
declare	@table table (dt datetime)

insert	@table
select	'2006-07-24 18:10:00' union all----> 2006-07-24 18:10:00
select	'2006-07-24 18:10:00' union all----> 2006-07-24 18:10:59
select	'2006-07-25 10:20:00' union all----> 2006-07-25 10:20:00
select	'2006-07-25 10:20:00' union all----> 2006-07-25 10:20:15
select	'2006-07-25 10:20:00' union all----> 2006-07-25 10:20:30
select	'2006-07-25 10:20:00' union all----> 2006-07-25 10:20:45
select	'2006-07-25 10:20:00' union all----> 2006-07-25 10:20:59
select	'2006-07-25 12:00:00' union all----> 2006-07-25 12:00:00
select	'2006-07-25 12:00:00' union all----> 2006-07-25 12:00:30
select	'2006-07-25 12:00:00' union all----> 2006-07-25 12:00:59
select	'2006-07-26 12:00:00'          ----> 2006-07-26 12:00:00

-- Do the work
select		w.dt oldValue,
		dateadd(second, case when z.num * w.i = 60 then 59 else z.num * w.i end, w.dt) newValue
from		(
			select		b0.i + b1.i + b2.i + b3.i + b4.i + b5.i Num
			from		(select 0 i union all select 1) b0
			cross join	(select 0 i union all select 2) b1
			cross join	(select 0 i union all select 4) b2
			cross join	(select 0 i union all select 8) b3
			cross join	(select 0 i union all select 16) b4
			cross join	(select 0 i union all select 32) b5
		) z
inner join	(
			select		dt,
					count(*) c,
					case when count(*) = 1 then 0 else 60.0 / (count(*) - 1) end i
			from		@table
			group by	dt
		) w on w.c > z.num
order by	w.dt,
		z.num
Output is
oldValue                 newValue
-----------------------  -----------------------
2006-07-24 18:10:00.000	 2006-07-24 18:10:00.000
2006-07-24 18:10:00.000	 2006-07-24 18:10:59.000
2006-07-25 10:20:00.000	 2006-07-25 10:20:00.000
2006-07-25 10:20:00.000	 2006-07-25 10:20:15.000
2006-07-25 10:20:00.000	 2006-07-25 10:20:30.000
2006-07-25 10:20:00.000	 2006-07-25 10:20:45.000
2006-07-25 10:20:00.000	 2006-07-25 10:20:59.000
2006-07-25 12:00:00.000	 2006-07-25 12:00:00.000
2006-07-25 12:00:00.000	 2006-07-25 12:00:30.000
2006-07-25 12:00:00.000	 2006-07-25 12:00:59.000
2006-07-26 12:00:00.000	 2006-07-26 12:00:00.000

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 07/25/2006 06:17:56
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/25/2006 :  04:15:05  Show Profile  Reply with Quote
Something like this perhaps? [Revised to include data - thanks for the data Peso!]

DECLARE	@MyTable TABLE
(
	MyPK int IDENTITY NOT NULL,
	MyCopyOriginal datetime NULL,
	MyDateColumn datetime NOT NULL,
	PRIMARY KEY
	(
		MyPK
	)
)

insert	@MyTable(MyDateColumn)
select	'2006-07-24 18:10:00' union all
select	'2006-07-24 18:10:00' union all
select	'2006-07-25 10:20:00' union all
select	'2006-07-25 10:20:00' union all
select	'2006-07-25 10:20:00' union all
select	'2006-07-25 12:00:00' union all
select	'2006-07-25 12:00:00' union all
select	'2006-07-25 12:00:00' union all
select	'2006-07-25 10:20:00' union all
select	'2006-07-25 10:20:00' union all
select	'2006-07-26 12:00:00'

-- Copy MyDateColumn to MyCopyOriginal for later checking
UPDATE	@MyTable
SET	MyCopyOriginal = MyDateColumn

-- Make list of all the items which will be updated - this REQUIRES a Unique Primary Key on MyTable
DECLARE @WorkingData TABLE
(
	W_ID int IDENTITY NOT NULL,
	W_PK1 int NOT NULL,
--	W_PK2 SomeDataType NOT NULL, -- Include one column for each PK field
	W_DateTime datetime NOT NULL,
	PRIMARY KEY
	(
		W_ID
	)
)

-- Create working data
INSERT INTO @WorkingData(W_PK1, /* W_PK2, ..., */ W_DateTime)
SELECT MyPK, /* PK2, ..., */ MyDateColumn
FROM   @MyTable
ORDER BY MyDateColumn, MyPK /* , PK2, ..., */

-- Summary data - how many of each date/time exist
DECLARE @SummaryData TABLE
(
	S_DateTime datetime NOT NULL,
	S_MinID int NOT NULL,	-- ID of lowest row with this date/time (will be left at 00 seconds)
	S_MaxID int NOT NULL,	-- ID of highest row with this date/time (will be set to 59 seconds)
	S_Count int NOT NULL,
	PRIMARY KEY
	(
		S_DateTime
	)
)

-- Create summary data
INSERT INTO @SummaryData(S_DateTime, S_MinID, S_MaxID, S_Count)
SELECT W_DateTime, MIN(W_ID), MAX(W_ID), COUNT(*)
FROM   @WorkingData
GROUP BY W_DateTime
HAVING COUNT(*) >= 2   -- Only the ones with 2 or more identical times need "calculated times"

-- Update original table
UPDATE U 
SET MyDateColumn = 
	CASE WHEN W_ID = S_MinID THEN MyDateColumn
			WHEN W_ID = S_MaxID THEN DATEADD(Second, 59, MyDateColumn)
			ELSE DATEADD(Second, 60/(S_Count-1)*(W_ID - S_MinID), MyDateColumn)
			END
FROM	@WorkingData AS W
	JOIN @SummaryData AS S
		 ON S_DateTime = W_DateTime
	JOIN @MyTable AS U
		 ON U.MyPK = W_PK1
/*		AND U.PK2 = W_PK2	*/


SELECT	*
FROM	@MyTable
ORDER BY MyCopyOriginal, MyPK

You could manage without the @WorkingData temporary table IF you have a single-part unique index, or PK, on your MyTable - otherwise its too hard for my little brain!

The final update could be in batches, within a loop, if it is too slow (given that you said there are lots of records to update)

Kristen

Edited by - Kristen on 07/25/2006 04:35:53
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/25/2006 :  06:25:33  Show Profile  Reply with Quote
"No need for primary key"

I wouldn't have thought to use a TallyTable Peso.

Any idea how efficient an UPDATE would be?

I was thinking the TempTable route to be able to use a PK:PK JOIN for the update, but I do need a full-size <g> TempTable to achieve that ... which may be rubbish for performance

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/25/2006 :  06:32:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The reason for using tally table is that I can't figure out if poster wants a one-time update, or just doing this for presentation issues.

If for one-time update, staging tables are more efficient and quicker, as in your solution.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/25/2006 :  06:40:00  Show Profile  Reply with Quote
"What i wanna do is to add seconds to this column according to the example below"

Clearly a one time UPDATE, eh?

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/25/2006 :  06:50:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Most likely, yes.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/25/2006 :  08:01:49  Show Profile  Reply with Quote
I wish I shared your confidence!
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 07/27/2006 :  04:11:31  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Hi,

i need an algorithm to do the sample below;
i have duplicate fTime datas.

fTime
2005-01-02 20:23:00.000
2005-01-02 20:23:00.000
2005-01-02 20:23:00.000
...

what i need to do is;
2005-01-02 20:23:01.01 ----> Add 1 second to the first fTime and also 1 Msecond.Then till 60, MSeconds will be increased one by one like below;

2005-01-02 20:23:01.02
2005-01-02 20:23:01.03
2005-01-02 20:23:01.04
2005-01-02 20:23:01.05
...
2005-01-02 20:23:01.58
2005-01-02 20:23:01.59 ----> when the Msecond comes to 60, the seconds will be increased by one then again Mseconds will be increased one by one, like below

2005-01-02 20:23:02.00
2005-01-02 20:23:02.01
2005-01-02 20:23:02.02
2005-01-02 20:23:02.03
...
2005-01-02 20:23:02.59
2005-01-02 20:23:03.00
2005-01-02 20:23:03.01
2005-01-02 20:23:03.02





Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/27/2006 :  04:19:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you sure you want this? Not evenly spread, but just adding?

What if there are no more than two records of
2005-01-02 20:23:00.000
2005-01-02 20:23:00.000

Should that be
2005-01-02 20:23:01.010
2005-01-02 20:23:01.020

or
2005-01-02 20:23:01.010
2005-01-02 20:23:59.590

like before?


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 07/27/2006 04:20:36
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/27/2006 :  04:20:26  Show Profile  Reply with Quote
I've already done the donkey work on this one, so I reckon you should ahve a go at the Fine Detail by yourself.

Happy to help if you get stuck though.

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/27/2006 :  04:30:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Prepare test data
declare	@table table (dt datetime)

insert	@table
select	'2006-07-24 18:10:00' union all
select	'2006-07-24 18:10:00'

-- Do the work
select		w.dt oldValue,
		dateadd(ms, 1010 + 10 * z.num, w.dt) newValue
from		(
			select		b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i + b7.i + b8.i + b9.i + b10.i + b11.i + b12.i Num
			from		(select 0 i union all select 1) b0
			cross join	(select 0 i union all select 2) b1
			cross join	(select 0 i union all select 4) b2
			cross join	(select 0 i union all select 8) b3
			cross join	(select 0 i union all select 16) b4
			cross join	(select 0 i union all select 32) b5
			cross join	(select 0 i union all select 64) b6
			cross join	(select 0 i union all select 128) b7
			cross join	(select 0 i union all select 256) b8
			cross join	(select 0 i union all select 512) b9
			cross join	(select 0 i union all select 1024) b10
			cross join	(select 0 i union all select 2048) b11
			cross join	(select 0 i union all select 4096) b12
			where		b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i + b7.i + b8.i + b9.i + b10.i + b11.i + b12.i < 5959
		) z
inner join	(
			select		dt,
					count(*) c
			from		@table
			group by	dt
		) w on w.c > z.num
order by	w.dt,
		z.num



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 07/27/2006 :  04:49:23  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Hi,

Peso Mseconds passes 60 like below;

2005-01-02 19:53:01.800 ---> what i need is 2005-01-02 19:53:01.590 then 2005-01-02 19:53:02.00 when it gets the 60th.


Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 07/27/2006 :  04:56:48  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
I asked this question "i need an algorithm to do the sample below;
i have duplicate fTime datas..." because i have duplicates more than 60 even more than 100 rows. I m not dealing with no more than 2 rows, it was an other issue nothing to do with this one. And i m not adding Mseconds to the previous one because there was no duplicates more than 60.
Go to Top of Page

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 07/27/2006 :  06:33:29  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
Peso,

the code you have sent to me, increments seconds when the MSeconds reaches to 1000?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 07/27/2006 :  09:48:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Look at this
-- Prepare test data
declare	@table table (dt datetime)

insert	@table
select	'2006-07-24 18:10:00' union all
select	'2006-07-24 18:10:00'

-- Do the work
select		w.dt oldValue,
		dateadd(second, 1 + (10 + 10 * z.Num) / 600, dateadd(ms, (10 + 10 * z.num) % 600, w.dt)) newValue
from		(
			select		b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i + b7.i + b8.i + b9.i + b10.i + b11.i + b12.i Num
			from		(select 0 i union all select 1) b0
			cross join	(select 0 i union all select 2) b1
			cross join	(select 0 i union all select 4) b2
			cross join	(select 0 i union all select 8) b3
			cross join	(select 0 i union all select 16) b4
			cross join	(select 0 i union all select 32) b5
			cross join	(select 0 i union all select 64) b6
			cross join	(select 0 i union all select 128) b7
			cross join	(select 0 i union all select 256) b8
			cross join	(select 0 i union all select 512) b9
			cross join	(select 0 i union all select 1024) b10
			cross join	(select 0 i union all select 2048) b11
			cross join	(select 0 i union all select 4096) b12
			where		b0.i + b1.i + b2.i + b3.i + b4.i + b5.i + b6.i + b7.i + b8.i + b9.i + b10.i + b11.i + b12.i < 5959
		) z
inner join	(
			select		dt,
					count(*) c
			from		@table
			group by	dt
		) w on w.c > z.num
order by	w.dt,
		z.num



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 07/27/2006 :  16:06:54  Show Profile  Reply with Quote
raysefo,

quote:

the code you have sent to me, increments seconds when the MSeconds reaches to 1000?



What would you expect it to do? 1000 milliseconds is 1 second, so seconds increments and MS is reset to zero.

Ken
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000