Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Call_ID Start_Time
- 93856 2011-08-04 09:59:47.000
- 58796 2011-08-05 14:54:37.000
- 25489 2011-08-09 15:32:13.000
- Call_ID Start_Time Interval
- 93856 2011-08-04 09:59:47.000 0930
- 58796 2011-08-05 14:54:37.000 1430
- 25489 2011-08-09 15:32:13.000 1530
- Select Call_ID , Start_Time,
- CASE WHEN DATEPART(minute,Start_Time)>30 THEN
- RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '30'
- ELSE
- RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '00'
- END
- From Timezone
- Group By Call_ID , Start_Time,
- CASE WHEN DATEPART(minute,Start_Time)>30 THEN
- RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '30'
- ELSE
- RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '00'
- END
- select Call_ID,
- Start_Time,
- right(100+datepart(hour, Start_Time), 2)+
- right(100+30*(datepart(minute, Start_Time)/30), 2) as Interval
- from TimeZone
- ;WITH intervals(h) AS
- (
- SELECT TOP (48) CONVERT(TIME(0), DATEADD(MINUTE, 30*(number), '00:00'))
- FROM master..spt_values
- WHERE number >= 0
- GROUP BY number
- ORDER BY number
- )
- SELECT
- t.Call_ID,
- t.Start_Time,
- Interval = REPLACE(CONVERT(VARCHAR(5), i.h), ':', '')
- FROM intervals AS i
- INNER JOIN dbo.TimeZone AS t
- ON DATEDIFF(MINUTE, i.h, CONVERT(TIME(0), t.Start_Time)) BETWEEN 1 AND 30;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement