Advertisement
Guest User

Untitled

a guest
Apr 20th, 2014
54
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.14 KB | None | 0 0
  1. SELECT
  2. SUM(CASE WHEN a.Type = 1
  3. THEN CAST(DATEDIFF(s,
  4. ca.StartTime,
  5. ca.EndTime) AS FLOAT)
  6. / 60.0
  7. ELSE NULL
  8. END) AS WorkingTime ,
  9. AnnotherResult ...
  10.  
  11. Select ... , something/WorkingTime, something*WorkingTime, ...
  12. From
  13. (
  14. Select *, CASE WHEN a.Type = 1
  15. THEN CAST(DATEDIFF(s,
  16. ca.StartTime,
  17. ca.EndTime) AS FLOAT)
  18. / 60.0
  19. ELSE NULL
  20. END) AS WorkingTime
  21. from myTable
  22. ) t
  23.  
  24. DECLARE @CA TABLE
  25. (
  26. StartTime datetime,
  27. EndTime datetime,
  28. ActivityType int,
  29. Employee varchar(50)
  30. );
  31. INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-01 1:00 PM', '2014-04-01 2:00 PM', 1, 'Jim');
  32. INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-03 1:00 PM', '2014-04-03 3:00 PM', 1, 'Jim');
  33. INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-04 9:00 AM', '2014-04-04 11:00 AM', 1, 'Jim');
  34. INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-05 10:00 AM', '2014-04-04 11:13 AM', 0, 'Jim');
  35. INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-02 10:00 AM', '2014-04-02 3:00 PM', 1, 'Sally');
  36. INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-04-04 1:55 PM', '2014-04-04 3:11 PM', 1, 'Sally');
  37. INSERT INTO @CA (StartTime, EndTime, ActivityType, Employee) VALUES ('2014-03-30 1:45 PM', '2014-04-01 1:00 AM', 0, 'Sally');
  38.  
  39. -- Method #1: CTE
  40.  
  41. WITH BaseData AS
  42. (
  43. SELECT
  44. Employee,
  45. SUM (
  46. CASE
  47. WHEN ca.ActivityType = 1 THEN
  48. 1
  49. ELSE
  50. 0
  51. END
  52. ) AS NumType1Entries,
  53. SUM (
  54. CASE
  55. WHEN ca.ActivityType = 1 THEN
  56. CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
  57. ELSE
  58. NULL
  59. END
  60. ) AS TotalMinutesType1
  61. FROM @CA ca
  62. GROUP BY Employee
  63. )
  64. SELECT
  65. BaseData.Employee,
  66. BaseData.NumType1Entries,
  67. BaseData.TotalMinutesType1,
  68. CASE
  69. WHEN BaseData.NumType1Entries > 0 THEN
  70. BaseData.TotalMinutesType1 / BaseData.NumType1Entries
  71. ELSE
  72. NULL
  73. END AS MinutesPerType1Entry
  74. FROM BaseData;
  75.  
  76. -- Method #2: Derived Table
  77.  
  78. SELECT
  79. BaseData.Employee,
  80. BaseData.NumType1Entries,
  81. BaseData.TotalMinutesType1,
  82. CASE
  83. WHEN BaseData.NumType1Entries > 0 THEN
  84. BaseData.TotalMinutesType1 / BaseData.NumType1Entries
  85. ELSE
  86. NULL
  87. END AS MinutesPerType1Entry
  88. FROM (
  89. SELECT
  90. Employee,
  91. SUM (
  92. CASE
  93. WHEN ca.ActivityType = 1 THEN
  94. 1
  95. ELSE
  96. 0
  97. END
  98. ) AS NumType1Entries,
  99. SUM (
  100. CASE
  101. WHEN ca.ActivityType = 1 THEN
  102. CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
  103. ELSE
  104. NULL
  105. END
  106. ) AS TotalMinutesType1
  107. FROM @CA ca
  108. GROUP BY Employee
  109. ) AS BaseData;
  110.  
  111. -- Method #3 - Cross Apply
  112.  
  113. SELECT
  114. Employee,
  115. SUM (Calcs.Type1EntryCounter) AS NumType1Entries,
  116. SUM (Calcs.Type1EntryTotalMinutes) AS TotalMinutesType1,
  117. CASE
  118. WHEN SUM (Type1EntryCounter) > 0 THEN
  119. SUM (Type1EntryTotalMinutes) / SUM (Type1EntryCounter)
  120. ELSE
  121. NULL
  122. END AS MinutesPerType1Entry
  123. FROM @CA ca
  124. CROSS APPLY (
  125. SELECT
  126. CASE
  127. WHEN ca.ActivityType = 1 THEN
  128. 1
  129. ELSE
  130. 0
  131. END AS Type1EntryCounter,
  132. CASE
  133. WHEN ca.ActivityType = 1 THEN
  134. CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
  135. ELSE
  136. NULL
  137. END AS Type1EntryTotalMinutes
  138. ) AS Calcs
  139. GROUP BY Employee;
  140.  
  141. -- Method #4 - Inline Table-Valued User-Defined Function, Deterministic
  142.  
  143. -- Commented-out because it can't be run without creating the function first.
  144.  
  145. /*
  146.  
  147. SELECT
  148. Employee,
  149. SUM (Calcs.Type1EntryCounter) AS NumType1Entries,
  150. SUM (Calcs.Type1EntryTotalMinutes) AS TotalMinutesType1,
  151. CASE
  152. WHEN SUM (Type1EntryCounter) > 0 THEN
  153. SUM (Type1EntryTotalMinutes) / SUM (Type1EntryCounter)
  154. ELSE
  155. NULL
  156. END AS MinutesPerType1Entry
  157. FROM @CA ca
  158. CROSS APPLY dbo.CalcType1Statistics
  159. (
  160. ca.ActivityType,
  161. ca.StartTime,
  162. ca.EndTime
  163. ) AS Calcs
  164. GROUP BY Employee;
  165.  
  166. */
  167.  
  168. -- Method #5 - Inline Table-Valued User-Defined Function, Non-Deterministic Aggregation
  169.  
  170. -- Commented-out because it can't be run without creating the function first.
  171. /*
  172.  
  173. SELECT
  174. EmpSummary.Employee,
  175. EmpSummary.NumType1Entries,
  176. EmpSummary.TotalMinutesType1,
  177. CASE
  178. WHEN EmpSummary.NumType1Entries THEN
  179. EmpSummary.TotalMinutesType1 / EmpSummary.NumType1Entries
  180. ELSE
  181. NULL
  182. END AS MinutesPerType1Entry
  183. FROM dbo.GetEmployeeSummary () AS EmpSummary
  184.  
  185. */
  186.  
  187. SELECT WorkingTime
  188. FROM (
  189. SELECT SUM(CASE WHEN a.Type = 1 THEN CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
  190. ELSE NULL
  191. END) AS WorkingTime .....)
  192.  
  193. ;WITH WT AS (SELECT SUM(CASE WHEN a.Type = 1 THEN CAST(DATEDIFF(s, ca.StartTime, ca.EndTime) AS FLOAT) / 60.0
  194. ELSE NULL
  195. END) AS WorkingTime .....)
  196. SELECT WorkingTime
  197. FROM WT
  198. JOIN .....
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement