CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
SamuraiJack
SamuraiJack

Reputation: 5539

How break a time range between n numbers of equal intervals?

TimeMin: 2015-04-29 10:57:56.623

TimeMax: 2015-04-29 11:04:35.133

I am trying to write a select query to break this into n equal intervals

This is my attempt:

declare @Min int
select @Min  = min(DATEDIFF(ss,'1970-01-01', biddate)) from tbl_bids
declare @Max int
select @Max  = max(DATEDIFF(ss,'1970-01-01', biddate)) from tbl_bids

declare @NumParts int 
select @NumParts =COUNT(*) from tbl_bids

select ((DATEDIFF(ss,'1970-01-01', biddate) * (@Max - @Min) / @NumParts) + 1) - ((@Max - @Min) / @NumParts), 
(DATEDIFF(ss,'1970-01-01', biddate) * (@Max - @Min) / @NumParts) + 1
from tbl_bids 
where DATEDIFF(ss,'1970-01-01', biddate)<= @NumParts

But it returns 0 rows.

EXAMPLE:

Min: 2015-04-29 10:50:00

Max: 2015-04-29 11:00:00

if NumParts = 5 (breaking into 5 equal intervals)

Output should be:

2015-04-29 10:52:00
2015-04-29 10:54:00
2015-04-29 10:56:00
2015-04-29 10:58:00
2015-04-29 11:00:00

Upvotes: 0

Views: 83

Answers (1)

ughai
ughai

Reputation: 9890

You can get the total diff of seconds between the dates and then use it to get the equal parts. Something like this.

DECLARE @dt_min DATETIME = '2015-04-29 10:50:00'

DECLARE @dt_max DATETIME = '2015-04-29 11:00:00'

DECLARE @parts INT = 5
DECLARE @sec BIGINT = DATEDIFF(SECOND,@dt_min,@dt_max)/@parts

SELECT TOP (@parts) DATEADD(second,@sec*(ROW_NUMBER()OVER(ORDER BY (SELECT 1)) - 1) ,@dt_min) FROM sys.tables

And your query, would be something like this.

declare @dt_min DATETIME
select @dt_min  = min(biddate) from tbl_bids
declare @dt_max DATETIME
select @dt_max  = max(biddate) from tbl_bids

declare @NumParts int 
select @NumParts =COUNT(*) from tbl_bids

DECLARE @sec BIGINT = DATEDIFF(SECOND,@dt_min,@dt_max)/@NumParts

select DATEADD(second,@sec*(ROW_NUMBER()OVER(ORDER BY biddate) - 1) ,@dt_min)
from tbl_bids

Upvotes: 1

Related Questions