Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Id | Name
- ---------------
- 1 | John Smith
- 2 | Alan Jones
- 3 | James Jones
- Id | Title | StartDate | EmployeeId | Estimate (integer - ticks)
- ----------------------------------------------------------------------------
- 1 | task1 | 21.08.2011 | 1 | 90000000000
- 2 | task2 | 21.08.2011 | 1 | 150000000
- 3 | task3 | 22.08.2011 | 2 | 1230000000
- Date | EmployeeId | EmployeeName | SummaryEstimate
- -------------------------------------------------------------
- 19.08.2011 | 1 | John Smith | NULL
- 19.08.2011 | 2 | Alan Jones | NULL
- 19.08.2011 | 3 | James Jones | NULL
- 20.08.2011 | 1 | John Smith | NULL
- 20.08.2011 | 2 | Alan Jones | NULL
- 20.08.2011 | 3 | James Jones | NULL
- 21.08.2011 | 1 | John Smith | 90150000000
- 21.08.2011 | 2 | Alan Jones | NULL
- 21.08.2011 | 3 | James Jones | NULL
- 22.08.2011 | 1 | John Smith | NULL
- 22.08.2011 | 2 | Alan Jones | 1230000000
- 22.08.2011 | 3 | James Jones | NULL
- SELECT dates.value, employee.Id, employee.Name, sum(task.Estimate)
- FROM TableOfDates as dates
- left join Tasks as task on (dates.value = convert(varchar(10), task.StartTime, 101))
- left join Employees as employee on (employee.Id = task.EmployeeId)
- WHERE dates.value >= '2011-08-19' and dates.value < '2011-08-22'
- GROUP BY dates.value, employee.Id, employee.Name
- ORDER BY dates.value, employee.Id
- Date | EmployeeId | EmployeeName | SummaryEstimate
- -------------------------------------------------------------
- 19.08.2011 | NULL | NULL | NULL
- 20.08.2011 | NULL | NULL | NULL
- 21.08.2011 | 1 | John Smith | 90150000000
- 22.08.2011 | 2 | Alan Jones | 1230000000
- SELECT DE.DateValue, DE.EmployeeId, DE.EmployeeName, sum(task.Estimate)
- FROM
- ( SELECT
- D.value AS DateValue
- , E.Id AS EmployeeId
- , E.Name AS EmployeeName
- FROM
- TableOfDates D
- CROSS JOIN Employees E ) DE
- left join Tasks as task on DE.DateValue = convert(varchar(10), task.StartTime, 101)
- AND DE.EmployeeId = task.EmployeeId
- WHERE DE.DateValue >= '2011-08-19' and DE.DateValue < '2011-08-22'
- GROUP BY DE.DateValue, DE.EmployeeId, DE.EmployeeName
- ORDER BY DE.DateValue, DE.EmployeeId
- DECLARE @startDate DATETIME = '2011-08-01'
- DECLARE @endDate DATETIME = '2011-09-01'
- ;WITH Employees(Id,Name)
- AS
- (
- SELECT 1, 'John Smith'
- UNION ALL
- SELECT 2, 'Alan Jones'
- UNION ALL
- SELECT 3, 'James Jones'
- )
- ,Tasks (Id, Title, StartDate, EmployeeId, Estimate)
- AS
- (
- SELECT 1, 'task1', '2011-08-21', 1, 90000000000
- UNION ALL
- SELECT 2, 'task2', '2011-08-21', 1, 150000000
- UNION ALL
- SELECT 3, 'task3', '2011-08-22', 2, 1230000000
- )
- ,TableOfDates(value)
- AS
- (
- SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @startDate), 0)
- UNION ALL
- SELECT DATEADD(DAY, 1, value)
- FROM TableOfDates
- WHERE value < @endDate
- )
- SELECT dates.value
- ,employee.Id
- ,employee.Name
- ,SUM(task.Estimate) AS SummaryEstimate
- FROM TableOfDates dates
- CROSS JOIN Employees employee
- LEFT JOIN Tasks task
- ON dates.value = task.StartDate
- AND (employee.Id = task.EmployeeId)
- WHERE dates.value >= '2011-08-19'
- AND dates.value < '2011-08-26'
- GROUP BY
- dates.value
- ,employee.Id
- ,employee.Name
- ORDER BY
- dates.value
- ,employee.Id
- create table #T_dates (id_date int identity(1,1),inp_date datetime)
- create table #T_tasks (id_task int identity(1,1),key_date int, key_emp int, est int)
- create table #T_emp (id_emp int identity(1,1),name varchar(50))
- insert #T_dates (inp_date) values ('08.19.2011')
- insert #T_dates (inp_date) values ('08.20.2011')
- insert #T_dates (inp_date) values ('08.21.2011')
- insert #T_dates (inp_date) values ('08.22.2011')
- insert #T_dates (inp_date) values ('08.23.2011')
- insert #T_dates (inp_date) values ('08.24.2011')
- --select * from #T_dates
- insert #T_emp (name) values ('John Smith')
- insert #T_emp (name) values ('Alan Jones')
- insert #T_emp (name) values ('James Jones')
- --select * from #T_emp
- insert #T_tasks (key_date,key_emp,est) values (4,1,900000)
- insert #T_tasks (key_date,key_emp,est) values (4,1,15000)
- insert #T_tasks (key_date,key_emp,est) values (5,2,123000)
- --select * from #T_tasks
- select inp_date,id_emp,name,EST
- from #T_emp
- cross join #T_dates
- left join
- (
- select key_date,key_emp,SUM(est) 'EST' from #T_tasks group by key_date,key_emp
- ) Gr
- ON Gr.key_emp = id_emp and Gr.key_date = id_date
- where inp_date >= '2011-08-19' and inp_date <= '2011-08-22'
- order by inp_date,id_emp
Add Comment
Please, Sign In to add comment