API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 19th, 2019
85
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.15 KB | None | 0 0
raw download clone embed print report
  1. +------------------------------------------++-------------------------------------------------------+
  2. | DAILY || MONTHLY |
  3. +-------------+-------+-------+----------+-------+-------++-------+------------+-------+----------+-------+-------+
  4. | Creator | Count | Time | Average | Min. | Max. || Count | Daily Avg. | Time | Average | Min. | Max. |
  5. +-------------+-------+-------+----------+-------+-------++-------+------------+-------+----------+-------+-------+
  6. | williamsonb | 42 | 3:56 | 00:08:34 | 00:02 | 00:34 || ... | ... | ... | ... | ... | ... |
  7. | petersonl | ... | ... | ... | ... | ... || ... | ... | ... | ... | ... | ... |
  8. | ... | ... | ... | ... | ... | ... || ... | ... | ... | ... | ... | ... |
  9. +-------------+-------+-------+----------+-------+-------++-------+------------+-------+----------+-------+-------+
  10.  
  11. CREATE TABLE WorkOrders(CreatedBy CHAR(25), CreatedTime DATETIME)
  12.  
  13. INSERT INTO WorkOrders values
  14. ('williamsonb', '2017/03/26 12:05:00.000 PM'),
  15. ('williamsonb', '2017/03/26 12:12:00.000 PM'),
  16. ('williamsonb', '2017/03/26 01:31:00.000 PM'),
  17. ('williamsonb', '2017/03/02 09:45:00.000 AM'),
  18. ('williamsonb', '2017/03/02 09:49:00.000 AM'),
  19. ('williamsonb', '2017/03/02 09:51:00.000 AM'),
  20. ('williamsonb', '2017/02/04 02:14:00.000 PM'),
  21. ('petersonl', '2017/03/26 07:42:00.000 AM'),
  22. ('petersonl', '2017/03/26 07:45:00.000 AM'),
  23. ('petersonl', '2017/03/26 07:46:00.000 AM'),
  24. ('petersonl', '2017/03/05 08:47:00.000 AM'),
  25. ('petersonl', '2017/03/05 08:49:00.000 AM'),
  26. ('petersonl', '2017/03/05 08:50:00.000 AM'),
  27. ('petersonl', '2017/01/17 06:54:00.000 AM');
  28.  
  29. DECLARE
  30. @DayCount INT = COUNT(SELECT CreatedBy
  31. FROM WorkOrders
  32. WHERE CreatedTime = DATEADD(DAY, -1, GETDATE()),
  33.  
  34. @DayTimeSum DECIMAL = SUM(DATEDIFF(HOUR, SELECT (HOUR(CreatedTime)
  35. FROM WorkOrders
  36. WHERE DATEADD(DAY,-1,GETDATE()))
  37. + DATEDIFF(MINUTE, SELECT (MINUTE(CreationDate)/60
  38. FROM WorkOrders
  39. WHERE DATEADD(DAY,-1,GETDATE()))
  40. + DATEDIFF(SECOND, SELECT (SECOND(CreationDate)/3600
  41. FROM WorkOrders
  42. WHERE DATEADD(DAY,-1,GETDATE()))),
  43.  
  44. @DaySumHours INT = @DayTimeSum - MOD(@DayTimeSum % 1),
  45.  
  46. @DaySumMinutes INT = (MOD(@DayTimeSum % 1) - MOD(MOD(@DayTimeSum % 1) % 1/60) ) * 60,
  47.  
  48. @DayAvgTime DECIMAL = @DayCount / @DayTimeSum,
  49.  
  50. @DayAvgHours INT = @DayAvgTime - MOD(@DayAvgTime % 1),
  51.  
  52. @DayAvgMinutes INT = (MOD(@DayAvgTime % 1) - MOD(MOD(@DayAvgTime % 1) % 1/60)) * 60,
  53.  
  54. @DayAvgSeconds INT = ((MOD(@DayAvgTime % 1)
  55. - MOD(MOD(@DayAvgTime % 1) % 1/60))
  56. - MOD((MOD(@DayAvgTime % 1)
  57. - MOD(MOD(@DayAvgTime % 1) % 1/60) ) % 1/3600) ) * 3600,
  58.  
  59. @MonthTimeSum DECIMAL = SUM(DATEDIFF(HOUR, SELECT (HOUR(CreatedTime)
  60. FROM WorkOrders
  61. WHERE DATEADD(MONTH,-1,GETDATE()))
  62. + DATEDIFF(MINUTE, SELECT (MINUTE(CreationDate)/60
  63. FROM WorkOrders
  64. WHERE DATEADD(MONTH,-1,GETDATE()))
  65. + DATEDIFF(SECOND, SELECT (SECOND(CreationDate)/3600
  66. FROM WorkOrders
  67. WHERE DATEADD(MONTH,-1,GETDATE())));
  68.  
  69. SELECT DISTINCT CreatedBy
  70. ,@DayCount AS [Count]
  71. ,@DaySumHours + ':' + @DaySumMinutes + ':' + @DaySumSeconds AS [Total]
  72. ,@DayAvgHours + ':' + @DayAvgMinutes + ':' + @DayAvgSeconds AS [Average]
  73. ,LAST(SELECT CAST(SELECT CAST(CreatedTime AS TIME) AS CHAR(5))) AS [Min.]
  74. ,TOP 1(CAST(SELECT CAST(CreatedTime AS TIME) AS CHAR(5))) AS [Max.]
  75. FROM WorkOrders
  76. WHERE CreatedTime BETWEEN DATEADD(MONTH,-1,GETDATE()) AND GETDATE()
  77. GROUP BY CreatedBy
  78. ORDER BY CreatedTime DESC;
  79.  
  80. -- Test Data
  81. CREATE TABLE #WorkOrders(CreatedBy CHAR(25), CreatedTime DATETIME)
  82.  
  83. INSERT INTO #WorkOrders values
  84. ('williamsonb', '2017/03/27 12:05:00.000 PM'),
  85. ('williamsonb', '2017/03/27 12:12:00.000 PM'),
  86. ('williamsonb', '2017/03/27 01:31:00.000 PM'),
  87. ('williamsonb', '2017/03/26 09:04:00.000 PM'),
  88. ('williamsonb', '2017/03/26 09:12:00.000 PM'),
  89. ('williamsonb', '2017/03/26 10:32:00.000 PM'),
  90. ('williamsonb', '2017/03/02 09:45:00.000 AM'),
  91. ('williamsonb', '2017/03/02 09:49:00.000 AM'),
  92. ('williamsonb', '2017/03/02 09:51:00.000 AM'),
  93. ('williamsonb', '2017/02/04 02:14:00.000 PM'),
  94. ('petersonl', '2017/03/27 07:42:00.000 AM'),
  95. ('petersonl', '2017/03/27 07:45:00.000 AM'),
  96. ('petersonl', '2017/03/27 07:46:00.000 AM'),
  97. ('petersonl', '2017/03/26 08:41:00.000 AM'),
  98. ('petersonl', '2017/03/26 08:46:00.000 AM'),
  99. ('petersonl', '2017/03/26 08:48:00.000 AM'),
  100. ('petersonl', '2017/03/05 08:47:00.000 AM'),
  101. ('petersonl', '2017/03/05 08:49:00.000 AM'),
  102. ('petersonl', '2017/03/05 08:50:00.000 AM'),
  103. ('petersonl', '2017/01/17 06:54:00.000 AM');
  104.  
  105.  
  106.  
  107. WITH BaseData AS
  108. (SELECT CreatedBy
  109. ,CreatedTime
  110. ,LEAD(CreatedTime,1) OVER (PARTITION BY CreatedBy, DATEDIFF(DAY, 0, CreatedTime) ORDER BY CreatedTime) as EndTime
  111. FROM #WorkOrders
  112. )
  113. --SELECT * FROM BaseData
  114. ,DataWDuration AS
  115. (SELECT CreatedBy
  116. ,CreatedTime
  117. ,EndTime
  118. ,CASE WHEN DATEDIFF(SECOND, CreatedTime, EndTime) < 60 THEN 60 ELSE DATEDIFF(SECOND, CreatedTime, EndTime) END as Duration
  119. FROM BaseData
  120. )
  121. ,DayTotal AS
  122. (SELECT CreatedBy
  123. ,COUNT(*) as dailyCount
  124. ,SUM(Duration) as dailyTimeSec
  125. ,AVG(Duration) as DailyAvgTimeSec
  126. ,MIN(Duration) as DailyMinTimeSec
  127. ,MAX(Duration) as DailyMaxTimeSec
  128. FROM DataWDuration
  129. WHERE DATEDIFF(DAY, 0, CreatedTime) = DATEDIFF(DAY, 1, GETDATE()) -- compares date part of CreatedTime with yesterday's date
  130. GROUP BY CreatedBy
  131. )
  132. ,MTDTotal AS
  133. (SELECT CreatedBy
  134. ,COUNT(*) as mtdCount
  135. ,COUNT(DISTINCT DATEDIFF(DAY, 0, CreatedTime)) as ActiveDays
  136. ,COUNT(*) * 1.0 / DATEPART(DAY, GETDATE()) as mtdAvgDailyCount
  137. ,COUNT(*) * 1.0 / COUNT(DISTINCT DATEDIFF(DAY, 0, CreatedTime)) as mtdActiveDayAvgCount
  138. ,SUM(Duration) as mtdTimeSec
  139. ,AVG(Duration) as mtdAvgTimeSec
  140. ,MIN(Duration) as mtdMinTimeSec
  141. ,MAX(Duration) as mtdMaxTimeSec
  142. FROM DataWDuration
  143. WHERE CreatedTime >= DATEADD(DAY, 1 - DATEPART(DAY, DATEADD(DAY, -1, GETDATE())), CAST(DATEDIFF(DAY, 1, GETDATE()) as datetime)) -- picks up all activity for current calendar month to date
  144. AND CreatedTime < DATEDIFF(DAY, 0, GETDATE()) -- ignore anything entered today.
  145. GROUP BY CreatedBy
  146. )
  147. SELECT *
  148. FROM DayTotal dt
  149. FULL JOIN MTDTotal mt ON (dt.CreatedBy = mt.CreatedBy)
  150. ;
  151.  
  152. CreatedBy dailyCount dailyTimeSec DailyAvgTimeSec DailyMinTimeSec DailyMaxTimeSec mtdCount ActiveDays mtdAvgDailyCount mtdActiveDayAvgCount mtdTimeSec mtdAvgTimeSec mtdMinTimeSec mtdMaxTimeSec
  153. --------------- ----------- ------------ --------------- --------------- --------------- ----------- ----------- --------------------- ---------------------- ----------- ------------- ------------- -------------
  154. petersonl 3 240 120 60 180 9 3 0.321428571428 3.000000000000 840 140 60 300
  155. williamsonb 3 5160 2580 420 4740 9 3 0.321428571428 3.000000000000 10800 1800 120 4800
  156.  
  157. select cast(CreatedTime as Date) as CreatedDate, CreatedBy
  158. , CONVERT(VARCHAR(8), min(CreatedTime), 108) as [start]
  159. , CONVERT(VARCHAR(8), max(CreatedTime), 108) as [stop]
  160. , datediff(MINUTE, min(CreatedTime), MAX(CreatedTime)) as [start-stop]
  161. , count(*) as count
  162. , cast(datediff(MINUTE, min(CreatedTime), MAX(CreatedTime)) / cast((count(*) - 1) as decimal) as decimal(6,2)) as [avg]
  163. from WorkOrders
  164. group by cast(CreatedTime as Date), CreatedBy
  165. having count(*) > 1
  166. union
  167. select cast(CreatedTime as Date) as CreatedDate, CreatedBy
  168. , CONVERT(VARCHAR(8), max(CreatedTime), 108)
  169. , null
  170. , null
  171. , count(*) as count
  172. , null
  173. from WorkOrders
  174. group by cast(CreatedTime as Date), CreatedBy
  175. having count(*) = 1
  176. order by cast(CreatedTime as Date) desc, CreatedBy
  177.  
  178. select [CreatedDate], CreatedBy
  179. , CONVERT(VARCHAR(8), CreatedTime, 108) as [time]
  180. , CONVERT(VARCHAR(8), [min], 108) as [min]
  181. , CONVERT(VARCHAR(8), [max], 108) as [max]
  182. , datediff(MINUTE, [min], [max]) as [min-max]
  183. , CONVERT(VARCHAR(8), [next], 108) as [next]
  184. , datediff(MINUTE, [next], [CreatedTime]) as [time-next]
  185. , [count]
  186. , [first], [last]
  187. from
  188. ( select CreatedBy, cast(CreatedTime as date) as [CreatedDate], CreatedTime
  189. , LEAD(CreatedTime) OVER (PARTITION BY CreatedBy, cast(CreatedTime as date) ORDER BY CreatedTime desc) AS [next]
  190. , min(CreatedTime) OVER (PARTITION BY CreatedBy, cast(CreatedTime as date) ORDER BY CreatedTime) AS [min]
  191. , max(CreatedTime) OVER (PARTITION BY CreatedBy, cast(CreatedTime as date) ORDER BY CreatedTime) AS [max]
  192. , COUNT(*) OVER (PARTITION BY CreatedBy, cast(CreatedTime as date)) AS [count]
  193. , ROW_NUMBER() OVER (PARTITION BY CreatedBy, cast(CreatedTime as date) ORDER BY CreatedTime) AS [last]
  194. , ROW_NUMBER() OVER (PARTITION BY CreatedBy, cast(CreatedTime as date) ORDER BY CreatedTime desc) AS [first]
  195. from WorkOrders
  196. ) tt
  197. -- where tt.first = 1 or tt.last = 1
  198. order by tt.CreatedDate desc, CreatedBy
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!