sql server - sql query for by periods -

i have 2 fields in table, nexttime , endtime, table name visit.

i value page 00:05:00 or sth that. minimum 5 minute.

i have query database based on value.

i earliest nexttime , time + 00:05:00.

let's earlist nexttime 06:44.

my query 06:45, 06:50, , on..

i have input value interval time.

nvm that.

i wanna know how query interval.

the result looked this.

nexttime : endtime

06:55:00 ~ 06:58:00

07:25:00 ~ 07:28:00

07:35:00 ~ 08:52:00

08:38:00 ~ 08:48:00

08:40:00 ~ 08:54:00

08:43:00 ~ 09:36:00

09:12:00 ~ 09:30:00

here's based on information provided , understanding of question solution i'm proposing. in order meet chart periods, change periods within both select statements , clauses each unioned query.

use master go  create database tests go  use tests go  create table visits (     nexttime    datetime    not null     , endtime   datetime    not null ) go  begin transaction  insert visits (nexttime, endtime) select n'06:55:00', n'06:58:00' insert visits (nexttime, endtime) select n'07:25:00', n'07:28:00' insert visits (nexttime, endtime) select n'07:35:00', n'08:52:00' insert visits (nexttime, endtime) select n'08:38:00', n'08:48:00' insert visits (nexttime, endtime) select n'08:40:00', n'08:54:00' insert visits (nexttime, endtime) select n'08:43:00', n'09:36:00' insert visits (nexttime, endtime) select n'09:12:00', n'09:30:00'  commit  select n'06:30 - 07:00' period     , sum(case when datediff(mi, nexttime, endtime) between 5 , 10 1 else 0 end) [5:00]     , sum(case when datediff(mi, nexttime, endtime) between 10 , 15 1 else 0 end) [10:00]     , sum(case when datediff(mi, nexttime, endtime) between 15 , 20 1 else 0 end) [15:00]     , sum(case when datediff(mi, nexttime, endtime) between 20 , 25 1 else 0 end) [20:00]     , sum(case when datediff(mi, nexttime, endtime) between 25 , 30 1 else 0 end) [25:00]     , sum(case when datediff(mi, nexttime, endtime) between 30 , 35 1 else 0 end) [30:00]     , sum(case when datediff(mi, nexttime, endtime) between 35 , 40 1 else 0 end) [35:00]     , sum(case when datediff(mi, nexttime, endtime) between 40 , 45 1 else 0 end) [40:00]     , sum(case when datediff(mi, nexttime, endtime) between 45 , 50 1 else 0 end) [45:00]     , sum(case when datediff(mi, nexttime, endtime) >= 50 1 else 0 end) [50:00]  visits nexttime between convert(datetime, n'06:30:00', 108) , convert(datetime, n'07:00:00', 108) union select n'07:00 - 07:30' period     , sum(case when datediff(mi, nexttime, endtime) between 5 , 10 1 else 0 end) [5:00]     , sum(case when datediff(mi, nexttime, endtime) between 10 , 15 1 else 0 end) [10:00]     , sum(case when datediff(mi, nexttime, endtime) between 15 , 20 1 else 0 end) [15:00]     , sum(case when datediff(mi, nexttime, endtime) between 20 , 25 1 else 0 end) [20:00]     , sum(case when datediff(mi, nexttime, endtime) between 25 , 30 1 else 0 end) [25:00]     , sum(case when datediff(mi, nexttime, endtime) between 30 , 35 1 else 0 end) [30:00]     , sum(case when datediff(mi, nexttime, endtime) between 35 , 40 1 else 0 end) [35:00]     , sum(case when datediff(mi, nexttime, endtime) between 40 , 45 1 else 0 end) [40:00]     , sum(case when datediff(mi, nexttime, endtime) between 45 , 50 1 else 0 end) [45:00]     , sum(case when datediff(mi, nexttime, endtime) >= 50 1 else 0 end) [50:00]  visits nexttime between convert(datetime, n'07:00:00', 108) , convert(datetime, n'07:30:00', 108) union select n'07:30 - 08:00' period     , sum(case when datediff(mi, nexttime, endtime) between 5 , 10 1 else 0 end) [5:00]     , sum(case when datediff(mi, nexttime, endtime) between 10 , 15 1 else 0 end) [10:00]     , sum(case when datediff(mi, nexttime, endtime) between 15 , 20 1 else 0 end) [15:00]     , sum(case when datediff(mi, nexttime, endtime) between 20 , 25 1 else 0 end) [20:00]     , sum(case when datediff(mi, nexttime, endtime) between 25 , 30 1 else 0 end) [25:00]     , sum(case when datediff(mi, nexttime, endtime) between 30 , 35 1 else 0 end) [30:00]     , sum(case when datediff(mi, nexttime, endtime) between 35 , 40 1 else 0 end) [35:00]     , sum(case when datediff(mi, nexttime, endtime) between 40 , 45 1 else 0 end) [40:00]     , sum(case when datediff(mi, nexttime, endtime) between 45 , 50 1 else 0 end) [45:00]     , sum(case when datediff(mi, nexttime, endtime) >= 50 1 else 0 end) [50:00]  visits nexttime between convert(datetime, n'07:30:00', 108) , convert(datetime, n'08:00:00', 108) union select n'08:00 - 08:30' period     , sum(case when datediff(mi, nexttime, endtime) between 5 , 10 1 else 0 end) [5:00]     , sum(case when datediff(mi, nexttime, endtime) between 10 , 15 1 else 0 end) [10:00]     , sum(case when datediff(mi, nexttime, endtime) between 15 , 20 1 else 0 end) [15:00]     , sum(case when datediff(mi, nexttime, endtime) between 20 , 25 1 else 0 end) [20:00]     , sum(case when datediff(mi, nexttime, endtime) between 25 , 30 1 else 0 end) [25:00]     , sum(case when datediff(mi, nexttime, endtime) between 30 , 35 1 else 0 end) [30:00]     , sum(case when datediff(mi, nexttime, endtime) between 35 , 40 1 else 0 end) [35:00]     , sum(case when datediff(mi, nexttime, endtime) between 40 , 45 1 else 0 end) [40:00]     , sum(case when datediff(mi, nexttime, endtime) between 45 , 50 1 else 0 end) [45:00]     , sum(case when datediff(mi, nexttime, endtime) >= 50 1 else 0 end) [50:00]  visits nexttime between convert(datetime, n'08:00:00', 108) , convert(datetime, n'08:30:00', 108) union select n'08:30 - 09:00' period     , sum(case when datediff(mi, nexttime, endtime) between 5 , 10 1 else 0 end) [5:00]     , sum(case when datediff(mi, nexttime, endtime) between 10 , 15 1 else 0 end) [10:00]     , sum(case when datediff(mi, nexttime, endtime) between 15 , 20 1 else 0 end) [15:00]     , sum(case when datediff(mi, nexttime, endtime) between 20 , 25 1 else 0 end) [20:00]     , sum(case when datediff(mi, nexttime, endtime) between 25 , 30 1 else 0 end) [25:00]     , sum(case when datediff(mi, nexttime, endtime) between 30 , 35 1 else 0 end) [30:00]     , sum(case when datediff(mi, nexttime, endtime) between 35 , 40 1 else 0 end) [35:00]     , sum(case when datediff(mi, nexttime, endtime) between 40 , 45 1 else 0 end) [40:00]     , sum(case when datediff(mi, nexttime, endtime) between 45 , 50 1 else 0 end) [45:00]     , sum(case when datediff(mi, nexttime, endtime) >= 50 1 else 0 end) [50:00]  visits nexttime between convert(datetime, n'08:30:00', 108) , convert(datetime, n'09:00:00', 108) union select n'09:00 - 09:30' period     , sum(case when datediff(mi, nexttime, endtime) between 5 , 10 1 else 0 end) [5:00]     , sum(case when datediff(mi, nexttime, endtime) between 10 , 15 1 else 0 end) [10:00]     , sum(case when datediff(mi, nexttime, endtime) between 15 , 20 1 else 0 end) [15:00]     , sum(case when datediff(mi, nexttime, endtime) between 20 , 25 1 else 0 end) [20:00]     , sum(case when datediff(mi, nexttime, endtime) between 25 , 30 1 else 0 end) [25:00]     , sum(case when datediff(mi, nexttime, endtime) between 30 , 35 1 else 0 end) [30:00]     , sum(case when datediff(mi, nexttime, endtime) between 35 , 40 1 else 0 end) [35:00]     , sum(case when datediff(mi, nexttime, endtime) between 40 , 45 1 else 0 end) [40:00]     , sum(case when datediff(mi, nexttime, endtime) between 45 , 50 1 else 0 end) [45:00]     , sum(case when datediff(mi, nexttime, endtime) >= 50 1 else 0 end) [50:00]  visits nexttime between convert(datetime, n'09:00:00', 108) , convert(datetime, n'09:30:00', 108) 

output

╔══════════════╦═══╦═══╦═══╦═══╦═══╦═══╦═══╦═══╦═══╦═══╗ ║06:30 - 07:00 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ ╠══════════════╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╣ ║07:00 - 07:30 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ ╠══════════════╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╣ ║07:30 - 08:00 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 1 ║ ╠══════════════╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╣ ║08:00 - 08:30 ║   ║   ║   ║   ║   ║   ║   ║   ║   ║   ║ ╠══════════════╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╣ ║08:30 - 09:00 ║ 1 ║ 2 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 1 ║ ╠══════════════╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╣ ║09:00 - 09:30 ║ 0 ║ 0 ║ 1 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ ╚══════════════╩═══╩═══╩═══╩═══╩═══╩═══╩═══╩═══╩═══╩═══╝ 

feel free whether have correctly understood question or not.