daily pastebin goal
15%
SHARE
TWEET

Untitled

a guest Feb 19th, 2019 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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
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