Guest User

Untitled

a guest
Jul 20th, 2018
68
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.87 KB | None | 0 0
  1. Id | Name
  2. ---------------
  3. 1 | John Smith
  4. 2 | Alan Jones
  5. 3 | James Jones
  6.  
  7. Id | Title | StartDate | EmployeeId | Estimate (integer - ticks)
  8. ----------------------------------------------------------------------------
  9. 1 | task1 | 21.08.2011 | 1 | 90000000000
  10. 2 | task2 | 21.08.2011 | 1 | 150000000
  11. 3 | task3 | 22.08.2011 | 2 | 1230000000
  12.  
  13. Date | EmployeeId | EmployeeName | SummaryEstimate
  14. -------------------------------------------------------------
  15. 19.08.2011 | 1 | John Smith | NULL
  16. 19.08.2011 | 2 | Alan Jones | NULL
  17. 19.08.2011 | 3 | James Jones | NULL
  18. 20.08.2011 | 1 | John Smith | NULL
  19. 20.08.2011 | 2 | Alan Jones | NULL
  20. 20.08.2011 | 3 | James Jones | NULL
  21. 21.08.2011 | 1 | John Smith | 90150000000
  22. 21.08.2011 | 2 | Alan Jones | NULL
  23. 21.08.2011 | 3 | James Jones | NULL
  24. 22.08.2011 | 1 | John Smith | NULL
  25. 22.08.2011 | 2 | Alan Jones | 1230000000
  26. 22.08.2011 | 3 | James Jones | NULL
  27.  
  28. SELECT dates.value, employee.Id, employee.Name, sum(task.Estimate)
  29.  
  30. FROM TableOfDates as dates
  31. left join Tasks as task on (dates.value = convert(varchar(10), task.StartTime, 101))
  32. left join Employees as employee on (employee.Id = task.EmployeeId)
  33.  
  34. WHERE dates.value >= '2011-08-19' and dates.value < '2011-08-22'
  35.  
  36. GROUP BY dates.value, employee.Id, employee.Name
  37.  
  38. ORDER BY dates.value, employee.Id
  39.  
  40. Date | EmployeeId | EmployeeName | SummaryEstimate
  41. -------------------------------------------------------------
  42. 19.08.2011 | NULL | NULL | NULL
  43. 20.08.2011 | NULL | NULL | NULL
  44. 21.08.2011 | 1 | John Smith | 90150000000
  45. 22.08.2011 | 2 | Alan Jones | 1230000000
  46.  
  47. SELECT DE.DateValue, DE.EmployeeId, DE.EmployeeName, sum(task.Estimate)
  48.  
  49. FROM
  50. ( SELECT
  51. D.value AS DateValue
  52. , E.Id AS EmployeeId
  53. , E.Name AS EmployeeName
  54. FROM
  55. TableOfDates D
  56. CROSS JOIN Employees E ) DE
  57. left join Tasks as task on DE.DateValue = convert(varchar(10), task.StartTime, 101)
  58. AND DE.EmployeeId = task.EmployeeId
  59.  
  60. WHERE DE.DateValue >= '2011-08-19' and DE.DateValue < '2011-08-22'
  61.  
  62. GROUP BY DE.DateValue, DE.EmployeeId, DE.EmployeeName
  63.  
  64. ORDER BY DE.DateValue, DE.EmployeeId
  65.  
  66. DECLARE @startDate DATETIME = '2011-08-01'
  67. DECLARE @endDate DATETIME = '2011-09-01'
  68.  
  69. ;WITH Employees(Id,Name)
  70. AS
  71. (
  72. SELECT 1, 'John Smith'
  73. UNION ALL
  74. SELECT 2, 'Alan Jones'
  75. UNION ALL
  76. SELECT 3, 'James Jones'
  77. )
  78. ,Tasks (Id, Title, StartDate, EmployeeId, Estimate)
  79. AS
  80. (
  81. SELECT 1, 'task1', '2011-08-21', 1, 90000000000
  82. UNION ALL
  83. SELECT 2, 'task2', '2011-08-21', 1, 150000000
  84. UNION ALL
  85. SELECT 3, 'task3', '2011-08-22', 2, 1230000000
  86. )
  87. ,TableOfDates(value)
  88. AS
  89. (
  90. SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @startDate), 0)
  91. UNION ALL
  92. SELECT DATEADD(DAY, 1, value)
  93. FROM TableOfDates
  94. WHERE value < @endDate
  95. )
  96.  
  97. SELECT dates.value
  98. ,employee.Id
  99. ,employee.Name
  100. ,SUM(task.Estimate) AS SummaryEstimate
  101. FROM TableOfDates dates
  102. CROSS JOIN Employees employee
  103. LEFT JOIN Tasks task
  104. ON dates.value = task.StartDate
  105. AND (employee.Id = task.EmployeeId)
  106. WHERE dates.value >= '2011-08-19'
  107. AND dates.value < '2011-08-26'
  108. GROUP BY
  109. dates.value
  110. ,employee.Id
  111. ,employee.Name
  112. ORDER BY
  113. dates.value
  114. ,employee.Id
  115.  
  116. create table #T_dates (id_date int identity(1,1),inp_date datetime)
  117. create table #T_tasks (id_task int identity(1,1),key_date int, key_emp int, est int)
  118. create table #T_emp (id_emp int identity(1,1),name varchar(50))
  119.  
  120. insert #T_dates (inp_date) values ('08.19.2011')
  121. insert #T_dates (inp_date) values ('08.20.2011')
  122. insert #T_dates (inp_date) values ('08.21.2011')
  123. insert #T_dates (inp_date) values ('08.22.2011')
  124. insert #T_dates (inp_date) values ('08.23.2011')
  125. insert #T_dates (inp_date) values ('08.24.2011')
  126. --select * from #T_dates
  127.  
  128. insert #T_emp (name) values ('John Smith')
  129. insert #T_emp (name) values ('Alan Jones')
  130. insert #T_emp (name) values ('James Jones')
  131. --select * from #T_emp
  132.  
  133. insert #T_tasks (key_date,key_emp,est) values (4,1,900000)
  134. insert #T_tasks (key_date,key_emp,est) values (4,1,15000)
  135. insert #T_tasks (key_date,key_emp,est) values (5,2,123000)
  136. --select * from #T_tasks
  137.  
  138. select inp_date,id_emp,name,EST
  139. from #T_emp
  140. cross join #T_dates
  141. left join
  142. (
  143. select key_date,key_emp,SUM(est) 'EST' from #T_tasks group by key_date,key_emp
  144. ) Gr
  145. ON Gr.key_emp = id_emp and Gr.key_date = id_date
  146. where inp_date >= '2011-08-19' and inp_date <= '2011-08-22'
  147. order by inp_date,id_emp
Add Comment
Please, Sign In to add comment