SHARE
TWEET

Untitled

a guest Jun 18th, 2019 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Call_ID        Start_Time
  2. 93856          2011-08-04 09:59:47.000
  3. 58796          2011-08-05 14:54:37.000
  4. 25489          2011-08-09 15:32:13.000
  5.      
  6. Call_ID        Start_Time                    Interval
  7. 93856          2011-08-04 09:59:47.000       0930
  8. 58796          2011-08-05 14:54:37.000       1430
  9. 25489          2011-08-09 15:32:13.000       1530
  10.      
  11. Select  Call_ID , Start_Time,
  12. CASE WHEN DATEPART(minute,Start_Time)>30 THEN
  13. RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '30'          
  14. ELSE
  15. RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '00'        
  16. END
  17. From Timezone
  18. Group By Call_ID , Start_Time,
  19. CASE WHEN DATEPART(minute,Start_Time)>30 THEN
  20. RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '30'          
  21. ELSE
  22. RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '00'        
  23. END
  24.      
  25. select Call_ID,
  26.        Start_Time,
  27.        right(100+datepart(hour, Start_Time), 2)+
  28.        right(100+30*(datepart(minute, Start_Time)/30), 2) as Interval
  29. from TimeZone
  30.      
  31. ;WITH intervals(h) AS
  32. (
  33.   SELECT TOP (48) CONVERT(TIME(0), DATEADD(MINUTE, 30*(number), '00:00'))
  34.     FROM master..spt_values
  35.     WHERE number >= 0
  36.     GROUP BY number
  37.     ORDER BY number
  38. )
  39. SELECT
  40.   t.Call_ID,
  41.   t.Start_Time,
  42.   Interval = REPLACE(CONVERT(VARCHAR(5), i.h), ':', '')
  43. FROM intervals AS i
  44. INNER JOIN dbo.TimeZone AS t
  45. ON DATEDIFF(MINUTE, i.h, CONVERT(TIME(0), t.Start_Time)) BETWEEN 1 AND 30;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top