November 5, 2018 at 12:33 pm
Okay, I'm editing my request per Lynn's suggestion. Below, in the comments, I'm posting the DDL to generate the sample data and the expected result set vs. what I have right now. The current method looks at who the current supervisor is, ignoring historical reporting relationships. I'm looking for the best way to integrate who reported to who at any given time for each timesheet. Right now, I'm thinking of about building a function to evaluate each timesheet, but I can't help but think there's a more performance friendly way within the recursive CTE... with nesting maybe?
November 5, 2018 at 2:58 pm
DBAless - Monday, November 5, 2018 12:33 PMI have a standard recursive CTE for getting my current supervisory hierarchy. I use it to rollup timesheet "utilization" information under some level of manager based on whether someone was working on a billable project.declare @EmpID
select @EmpID = 1;with AllEmployees (EmpID)
as
(
select e.EmpID
from Employees e
where e.EmpID = @EmpID
union all
select e.EmpID
from Employees e
join Employees m on e.ReportsToID = m.EmpID
join AllEmployees d on m.EmpID = d.EmpID
where e.IsSubcontractor = 0 and e.ReportsToID is not null and e.EmpID <> @EmpID
)
select e.EmpCode, e.ProperName,
TotalHours = sum(i.WorkHrs), DirectHours = sum(case when f.ChargeTypeID = 1 then i.WorkHrs else 0 end)
from TimeSheetItems i
join TimeSheets t on i.TimeID = t.TimeID
join Employees e on t.EmpID = e.EmpID
join AllEmployees ee on e.EmpID = ee.EmpID
join Projects p on i.ProjectID = p.ProjectID
join ProjectFacts f on p.FactID = f.FactID
where t.TimeSheetEndDate between @start and @end
group by e.EmpCode, e.ProperNameThe challenge is to incorporate manager effective dates into this. I have a table with EmpID, ReportsToEmpID, EffectiveStart, EffectiveEnd. I now need to report on the timesheet information only if the employees actually reported to the given manager for that given week. In other words, my current query has all of a persons time follow them to their current supervisor. What we want is that person historical time to stay reported under the supervisor they had at the time, which includes evaluating the entire supervisory tree for that time. For purposes of this example, the effective start and end represents a whole week, correlated to a predefined timesheet period.
I'm looking for guidance on how to incorporate this into the CTE expression. Or another suggested approach that I'm not thinking of. So far, all I can think of is a cursor to loop through each week at a time, but I'm guessing there's a better way to do it.
You are more likely to get answers if you also provide the DDL (CREATE TABLE statement) for the table(s) involved, sample data (not production data) for the table(s) (using INSERT statements using table value constructors), and the expected results based on the sample data (use a table and tvc to populate it).
November 6, 2018 at 9:02 am
/****************************************************************************************
Generate employee data
****************************************************************************************/
INSERT INTO Employee
VALUES (1,'Alice'),(2,'Bob'),(3,'Cathy'),(4,'Don'),(5,'Ellie'),
(6,'Fred'),(7,'Ginger'),(8,'Harry'),(9,'Iris'),(10,'Jack')
Generate supervisory hierachy with historical effective dates, using a three week period of Nov 9 to Nov 23
****************************************************************************************/
EmployeeID int NOT NULL,
ReportsToID int NOT NULL,
PEDateStart datetime NOT NULL,
PEDateEnd datetime NULL)
VALUES (1,1,'2018-11-09 00:00:00.000',null),
(2,1,'2018-11-09 00:00:00.000',null),
(3,1,'2018-11-09 00:00:00.000',null),
(4,2,'2018-11-09 00:00:00.000',null),
(5,2,'2018-11-09 00:00:00.000',null),
(6,3,'2018-11-09 00:00:00.000',null),
(7,2,'2018-11-09 00:00:00.000','2018-11-16 00:00:00.000'),
(7,3,'2018-11-23 00:00:00.000',null),
(8,7,'2018-11-09 00:00:00.000',null),
(9,6,'2018-11-09 00:00:00.000','2018-11-09 00:00:00.000'),
(9,3,'2018-11-16 00:00:00.000','2018-11-16 00:00:00.000'),
(9,2,'2018-11-23 00:00:00.000',null),
(10,4,'2018-11-09 00:00:00.000','2018-11-09 00:00:00.000'),
(10,5,'2018-11-16 00:00:00.000',null)
Generate timesheet information
****************************************************************************************/
ProjectID int NOT NULL,
ProjectType int NOT NULL) -- 1 is billable
INSERT INTO Project
VALUES (100,1),(200,2)
TimeID int NOT NULL,
EmployeeID int NOT NULL,
PEDate datetime NOT NULL)INSERT INTO TimeSheet
VALUES (1,1,'2018-11-09 00:00:00.000'),(2,1,'2018-11-16 00:00:00.000'),(3,1,'2018-11-23 00:00:00.000'),
(4,2,'2018-11-09 00:00:00.000'),(5,2,'2018-11-16 00:00:00.000'),(6,2,'2018-11-23 00:00:00.000'),
(7,3,'2018-11-09 00:00:00.000'),(8,3,'2018-11-16 00:00:00.000'),(9,3,'2018-11-23 00:00:00.000'),
(10,4,'2018-11-09 00:00:00.000'),(11,4,'2018-11-16 00:00:00.000'),(12,4,'2018-11-23 00:00:00.000'),
(13,5,'2018-11-09 00:00:00.000'),(14,5,'2018-11-16 00:00:00.000'),(15,5,'2018-11-23 00:00:00.000'),
(16,6,'2018-11-09 00:00:00.000'),(17,6,'2018-11-16 00:00:00.000'),(18,6,'2018-11-23 00:00:00.000'),
(19,7,'2018-11-09 00:00:00.000'),(20,7,'2018-11-16 00:00:00.000'),(21,7,'2018-11-23 00:00:00.000'),
(22,8,'2018-11-09 00:00:00.000'),(23,8,'2018-11-16 00:00:00.000'),(24,8,'2018-11-23 00:00:00.000'),
(25,9,'2018-11-09 00:00:00.000'),(26,9,'2018-11-16 00:00:00.000'),(27,9,'2018-11-23 00:00:00.000'),
(28,10,'2018-11-09 00:00:00.000'),(29,10,'2018-11-16 00:00:00.000'),(30,10,'2018-11-23 00:00:00.000')
CREATE TABLE TimeSheetItem(
TimeID int NOT NULL,
ProjectID int NOT NULL,
WorkDate datetime NOT NULL,
WorkHours int NOT NULL)
VALUES (1,100,'2018-11-05 00:00:00.000',10),(1,200,'2018-11-06 00:00:00.000',30),
(2,100,'2018-11-12 00:00:00.000',10),(2,200,'2018-11-13 00:00:00.000',30),
(3,100,'2018-11-19 00:00:00.000',10),(3,200,'2018-11-20 00:00:00.000',30),
(4,100,'2018-11-05 00:00:00.000',15),(4,200,'2018-11-06 00:00:00.000',25),
(5,100,'2018-11-12 00:00:00.000',15),(5,200,'2018-11-13 00:00:00.000',25),
(6,100,'2018-11-19 00:00:00.000',15),(6,200,'2018-11-20 00:00:00.000',25),
(7,100,'2018-11-05 00:00:00.000',20),(7,200,'2018-11-06 00:00:00.000',20),
(8,100,'2018-11-12 00:00:00.000',20),(8,200,'2018-11-13 00:00:00.000',20),
(9,100,'2018-11-19 00:00:00.000',20),(9,200,'2018-11-20 00:00:00.000',20),
(10,100,'2018-11-05 00:00:00.000',25),(10,200,'2018-11-06 00:00:00.000',15),
(11,100,'2018-11-12 00:00:00.000',25),(11,200,'2018-11-13 00:00:00.000',15),
(12,100,'2018-11-19 00:00:00.000',25),(12,200,'2018-11-20 00:00:00.000',15),
(13,100,'2018-11-05 00:00:00.000',30),(13,200,'2018-11-06 00:00:00.000',10),
(14,100,'2018-11-12 00:00:00.000',30),(14,200,'2018-11-13 00:00:00.000',10),
(15,100,'2018-11-19 00:00:00.000',30),(15,200,'2018-11-20 00:00:00.000',10),
(16,100,'2018-11-05 00:00:00.000',35),(16,200,'2018-11-06 00:00:00.000',10),
(17,100,'2018-11-12 00:00:00.000',35),(17,200,'2018-11-13 00:00:00.000',10),
(18,100,'2018-11-19 00:00:00.000',35),(18,200,'2018-11-20 00:00:00.000',10),
(19,100,'2018-11-05 00:00:00.000',40),(19,200,'2018-11-06 00:00:00.000',10),
(20,100,'2018-11-12 00:00:00.000',40),(20,200,'2018-11-13 00:00:00.000',10),
(21,100,'2018-11-19 00:00:00.000',40),(21,200,'2018-11-20 00:00:00.000',10),
(22,100,'2018-11-05 00:00:00.000',45),(22,200,'2018-11-06 00:00:00.000',10),
(23,100,'2018-11-12 00:00:00.000',45),(23,200,'2018-11-13 00:00:00.000',10),
(24,100,'2018-11-19 00:00:00.000',45),(24,200,'2018-11-20 00:00:00.000',10),
(25,100,'2018-11-05 00:00:00.000',50),(25,200,'2018-11-06 00:00:00.000',10),
(26,100,'2018-11-12 00:00:00.000',50),(26,200,'2018-11-13 00:00:00.000',10),
(27,100,'2018-11-19 00:00:00.000',50),(27,200,'2018-11-20 00:00:00.000',10),
(28,100,'2018-11-05 00:00:00.000',55),(28,200,'2018-11-06 00:00:00.000',10),
(29,100,'2018-11-12 00:00:00.000',55),(29,200,'2018-11-13 00:00:00.000',10),
(30,100,'2018-11-19 00:00:00.000',55),(30,200,'2018-11-20 00:00:00.000',10)
Expected Result Set
Grand Totals: BillableHours = 350, TotalHours = 460
****************************************************************************************/CREATE TABLE ResultSet (
EmpID int,
PEDate datetime,
BillableHours int,
TotalHours int)
INSERT INTO ResultSet
VALUES (3,'2018-11-09 00:00:00.000',20,40),
(3,'2018-11-16 00:00:00.000',20,40),
(3,'2018-11-23 00:00:00.000',20,40),
(6,'2018-11-09 00:00:00.000',35,45),
(6,'2018-11-16 00:00:00.000',35,45),
(6,'2018-11-23 00:00:00.000',35,45),
(7,'2018-11-23 00:00:00.000',40,50),
(8,'2018-11-23 00:00:00.000',45,55),
(9,'2018-11-09 00:00:00.000',50,50),
(9,'2018-11-16 00:00:00.000',50,50)
Current Query -- only considers who is the current supervisor
****************************************************************************************/
select @EmpID = 3
as
(
select e.EmployeeID
from Employee e
where e.EmployeeID = @EmpID
union all
select e.EmployeeID
from Employee e
join ReportsToHistory h on e.EmployeeID = h.EmployeeID and h.PEDateEnd is null
join AllEmployees d on h.ReportsToID = d.EmployeeID
where e.EmployeeID <> @EmpID
)
select e.EmployeeID, t.PEDate,
DirectHours = sum(case when p.ProjectType = 1 then i.WorkHours else 0 end),
TotalHours = sum(i.WorkHours)
from TimeSheetItem i
join TimeSheet t on i.TimeID = t.TimeID
join Employee e on t.EmployeeID = e.EmployeeID
join AllEmployees ee on e.EmployeeID = ee.EmployeeID
join Project p on i.ProjectID = p.ProjectID
group by e.EmployeeID, t.PEDate
November 6, 2018 at 9:05 am
When I copied and pasted this from SSMS, it lost most of the formatting, like tabs etc. What's the best way to keep the formatting? I can't get the SQL Code tags to work.
November 6, 2018 at 9:40 am
DBAless - Tuesday, November 6, 2018 9:05 AMWhen I copied and pasted this from SSMS, it lost most of the formatting, like tabs etc. What's the best way to keep the formatting? I can't get the SQL Code tags to work.
Okay, got the tags working... ish.
November 6, 2018 at 9:49 am
DBAless - Tuesday, November 6, 2018 9:05 AMWhen I copied and pasted this from SSMS, it lost most of the formatting, like tabs etc. What's the best way to keep the formatting?
Use the SQL Code IF code tags.
November 6, 2018 at 10:05 am
Just one thing missing, to get the expected results how is the query run. I am trying to figure out how the employees come together using the start and end dates.
November 6, 2018 at 10:15 am
Lynn Pettis - Tuesday, November 6, 2018 10:05 AMJust one thing missing, to get the expected results how is the query run. I am trying to figure out how the employees come together using the start and end dates.
That's exactly the part I'm trying to work through. How to build the query so that the recursion evaluates whether the person reported to them during that week (timesheet PEDate), while evaluating multiple weeks at a time.
November 6, 2018 at 10:38 am
DBAless - Tuesday, November 6, 2018 10:15 AMLynn Pettis - Tuesday, November 6, 2018 10:05 AMJust one thing missing, to get the expected results how is the query run. I am trying to figure out how the employees come together using the start and end dates.That's exactly the part I'm trying to work through. How to build the query so that the recursion evaluates whether the person reported to them during that week (timesheet PEDate), while evaluating multiple weeks at a time.
Is there a date range for the query? If so, what should it look like.
November 6, 2018 at 10:43 am
Lynn Pettis - Tuesday, November 6, 2018 10:38 AMDBAless - Tuesday, November 6, 2018 10:15 AMLynn Pettis - Tuesday, November 6, 2018 10:05 AMJust one thing missing, to get the expected results how is the query run. I am trying to figure out how the employees come together using the start and end dates.That's exactly the part I'm trying to work through. How to build the query so that the recursion evaluates whether the person reported to them during that week (timesheet PEDate), while evaluating multiple weeks at a time.
Is there a date range for the query? If so, what should it look like.
declare @EmpID int
select @EmpID = 3
November 6, 2018 at 10:45 am
DBAless - Tuesday, November 6, 2018 10:43 AMFor now, date range isn't important. If it was it would be:
declare @EmpID int
select @EmpID = 3;with AllEmployees (EmployeeID)
as
(
select e.EmployeeID
from Employee e
where e.EmployeeID = @EmpID
union all
select e.EmployeeID
from Employee e
join ReportsToHistory h on e.EmployeeID = h.EmployeeID and h.PEDateEnd is null
join AllEmployees d on h.ReportsToID = d.EmployeeID
where e.EmployeeID <> @EmpID
)
select e.EmployeeID, t.PEDate,
DirectHours = sum(case when p.ProjectType = 1 then i.WorkHours else 0 end),
TotalHours = sum(i.WorkHours)
from TimeSheetItem i
join TimeSheet t on i.TimeID = t.TimeID
join Employee e on t.EmployeeID = e.EmployeeID
join AllEmployees ee on e.EmployeeID = ee.EmployeeID
join Project p on i.ProjectID = p.ProjectID
where t.PEDATE between @SOMESTART and @SOMEEND
group by e.EmployeeID, t.PEDate
However, the recursive part needs to consider whether the timesheet PEDate falls between the ReportsToHistory EffectiveStart and EffectiveEnd when determining if the person reported to them during that week... and do that recursively for the entire reporting structure.
November 6, 2018 at 12:04 pm
Here is a partial solution. It doesn't incorporate the WHERE clause for the time sheet information.
/****************************************************************************************
Generate employee data
****************************************************************************************/
if object_id('[dbo].[Employee]','U') is not null
drop table [dbo].[Employee];
create table [dbo].[Employee]
(
[EmployeeID] int not null
, [EmployeeName] nvarchar(255) not null
);
insert into [dbo].[Employee]([EmployeeID],[EmployeeName])
values
(1, 'Alice')
, (2, 'Bob')
, (3, 'Cathy')
, (4, 'Don')
, (5, 'Ellie')
, (6, 'Fred')
, (7, 'Ginger')
, (8, 'Harry')
, (9, 'Iris')
, (10, 'Jack');
/****************************************************************************************
Generate supervisory hierachy with historical effective dates, using a three week period of Nov 9 to Nov 23
****************************************************************************************/
if object_id('[dbo].[ReportsToHistory]','U') is not null
drop table [dbo].[ReportsToHistory];
create table [dbo].[ReportsToHistory]
(
[EmployeeID] int not null
, [ReportsToID] int not null
, [PEDateStart] datetime not null
, [PEDateEnd] datetime null
);
insert into [dbo].[ReportsToHistory]
values
(1, 1, '2018-11-09 00:00:00.000', null)
, (2, 1, '2018-11-09 00:00:00.000', null)
, (3, 1, '2018-11-09 00:00:00.000', null)
, (4, 2, '2018-11-09 00:00:00.000', null)
, (5, 2, '2018-11-09 00:00:00.000', null)
, (6, 3, '2018-11-09 00:00:00.000', null)
, (7, 2, '2018-11-09 00:00:00.000', '2018-11-16 00:00:00.000')
, (7, 3, '2018-11-23 00:00:00.000', null)
, (8, 7, '2018-11-09 00:00:00.000', null)
, (9, 6, '2018-11-09 00:00:00.000', '2018-11-09 00:00:00.000')
, (9, 3, '2018-11-16 00:00:00.000', '2018-11-16 00:00:00.000')
, (9, 2, '2018-11-23 00:00:00.000', null)
, (10, 4, '2018-11-09 00:00:00.000', '2018-11-09 00:00:00.000')
, (10, 5, '2018-11-16 00:00:00.000', null);
/****************************************************************************************
Generate timesheet information
****************************************************************************************/
if object_id('[dbo].[Project]','U') is not null
drop table [dbo].[Project];
create table [dbo].[Project]
(
[ProjectID] int not null
, [ProjectType] int not null -- 1 is billable
);
insert into [dbo].[Project]
values
(100, 1)
, (200, 2);
if object_id('[dbo].[TimeSheet]','U') is not null
drop table [dbo].[TimeSheet];
create table [dbo].[TimeSheet]
(
[TimeID] int not null
, [EmployeeID] int not null
, [PEDate] datetime not null
);
insert into [dbo].[TimeSheet]
values
(1, 1, '2018-11-09 00:00:00.000')
, (2, 1, '2018-11-16 00:00:00.000')
, (3, 1, '2018-11-23 00:00:00.000')
, (4, 2, '2018-11-09 00:00:00.000')
, (5, 2, '2018-11-16 00:00:00.000')
, (6, 2, '2018-11-23 00:00:00.000')
, (7, 3, '2018-11-09 00:00:00.000')
, (8, 3, '2018-11-16 00:00:00.000')
, (9, 3, '2018-11-23 00:00:00.000')
, (10, 4, '2018-11-09 00:00:00.000')
, (11, 4, '2018-11-16 00:00:00.000')
, (12, 4, '2018-11-23 00:00:00.000')
, (13, 5, '2018-11-09 00:00:00.000')
, (14, 5, '2018-11-16 00:00:00.000')
, (15, 5, '2018-11-23 00:00:00.000')
, (16, 6, '2018-11-09 00:00:00.000')
, (17, 6, '2018-11-16 00:00:00.000')
, (18, 6, '2018-11-23 00:00:00.000')
, (19, 7, '2018-11-09 00:00:00.000')
, (20, 7, '2018-11-16 00:00:00.000')
, (21, 7, '2018-11-23 00:00:00.000')
, (22, 8, '2018-11-09 00:00:00.000')
, (23, 8, '2018-11-16 00:00:00.000')
, (24, 8, '2018-11-23 00:00:00.000')
, (25, 9, '2018-11-09 00:00:00.000')
, (26, 9, '2018-11-16 00:00:00.000')
, (27, 9, '2018-11-23 00:00:00.000')
, (28, 10, '2018-11-09 00:00:00.000')
, (29, 10, '2018-11-16 00:00:00.000')
, (30, 10, '2018-11-23 00:00:00.000');
if object_id('[dbo].[TimeSheetItem]','U') is not null
drop table [dbo].[TimeSheetItem];
create table [dbo].[TimeSheetItem]
(
[TimeID] int not null
, [ProjectID] int not null
, [WorkDate] datetime not null
, [WorkHours] int not null
);
insert into [dbo].[TimeSheetItem]
values
(1, 100, '2018-11-05 00:00:00.000', 10)
, (1, 200, '2018-11-06 00:00:00.000', 30)
, (2, 100, '2018-11-12 00:00:00.000', 10)
, (2, 200, '2018-11-13 00:00:00.000', 30)
, (3, 100, '2018-11-19 00:00:00.000', 10)
, (3, 200, '2018-11-20 00:00:00.000', 30)
, (4, 100, '2018-11-05 00:00:00.000', 15)
, (4, 200, '2018-11-06 00:00:00.000', 25)
, (5, 100, '2018-11-12 00:00:00.000', 15)
, (5, 200, '2018-11-13 00:00:00.000', 25)
, (6, 100, '2018-11-19 00:00:00.000', 15)
, (6, 200, '2018-11-20 00:00:00.000', 25)
, (7, 100, '2018-11-05 00:00:00.000', 20)
, (7, 200, '2018-11-06 00:00:00.000', 20)
, (8, 100, '2018-11-12 00:00:00.000', 20)
, (8, 200, '2018-11-13 00:00:00.000', 20)
, (9, 100, '2018-11-19 00:00:00.000', 20)
, (9, 200, '2018-11-20 00:00:00.000', 20)
, (10, 100, '2018-11-05 00:00:00.000', 25)
, (10, 200, '2018-11-06 00:00:00.000', 15)
, (11, 100, '2018-11-12 00:00:00.000', 25)
, (11, 200, '2018-11-13 00:00:00.000', 15)
, (12, 100, '2018-11-19 00:00:00.000', 25)
, (12, 200, '2018-11-20 00:00:00.000', 15)
, (13, 100, '2018-11-05 00:00:00.000', 30)
, (13, 200, '2018-11-06 00:00:00.000', 10)
, (14, 100, '2018-11-12 00:00:00.000', 30)
, (14, 200, '2018-11-13 00:00:00.000', 10)
, (15, 100, '2018-11-19 00:00:00.000', 30)
, (15, 200, '2018-11-20 00:00:00.000', 10)
, (16, 100, '2018-11-05 00:00:00.000', 35)
, (16, 200, '2018-11-06 00:00:00.000', 10)
, (17, 100, '2018-11-12 00:00:00.000', 35)
, (17, 200, '2018-11-13 00:00:00.000', 10)
, (18, 100, '2018-11-19 00:00:00.000', 35)
, (18, 200, '2018-11-20 00:00:00.000', 10)
, (19, 100, '2018-11-05 00:00:00.000', 40)
, (19, 200, '2018-11-06 00:00:00.000', 10)
, (20, 100, '2018-11-12 00:00:00.000', 40)
, (20, 200, '2018-11-13 00:00:00.000', 10)
, (21, 100, '2018-11-19 00:00:00.000', 40)
, (21, 200, '2018-11-20 00:00:00.000', 10)
, (22, 100, '2018-11-05 00:00:00.000', 45)
, (22, 200, '2018-11-06 00:00:00.000', 10)
, (23, 100, '2018-11-12 00:00:00.000', 45)
, (23, 200, '2018-11-13 00:00:00.000', 10)
, (24, 100, '2018-11-19 00:00:00.000', 45)
, (24, 200, '2018-11-20 00:00:00.000', 10)
, (25, 100, '2018-11-05 00:00:00.000', 50)
, (25, 200, '2018-11-06 00:00:00.000', 10)
, (26, 100, '2018-11-12 00:00:00.000', 50)
, (26, 200, '2018-11-13 00:00:00.000', 10)
, (27, 100, '2018-11-19 00:00:00.000', 50)
, (27, 200, '2018-11-20 00:00:00.000', 10)
, (28, 100, '2018-11-05 00:00:00.000', 55)
, (28, 200, '2018-11-06 00:00:00.000', 10)
, (29, 100, '2018-11-12 00:00:00.000', 55)
, (29, 200, '2018-11-13 00:00:00.000', 10)
, (30, 100, '2018-11-19 00:00:00.000', 55)
, (30, 200, '2018-11-20 00:00:00.000', 10);
/****************************************************************************************
Expected Result Set
Grand Totals: BillableHours = 350, TotalHours = 460
****************************************************************************************/
if object_id('[dbo].[ResultSet]','U') is not null
drop table [dbo].[ResultSet];
create table [dbo].[ResultSet]
(
[EmpID] int
, [PEDate] datetime
, [BillableHours] int
, [TotalHours] int
);
insert into [dbo].[ResultSet]
values
(3, '2018-11-09 00:00:00.000', 20, 40)
, (3, '2018-11-16 00:00:00.000', 20, 40)
, (3, '2018-11-23 00:00:00.000', 20, 40)
, (6, '2018-11-09 00:00:00.000', 35, 45)
, (6, '2018-11-16 00:00:00.000', 35, 45)
, (6, '2018-11-23 00:00:00.000', 35, 45)
, (7, '2018-11-23 00:00:00.000', 40, 50)
, (8, '2018-11-23 00:00:00.000', 45, 55)
, (9, '2018-11-09 00:00:00.000', 50, 50)
, (9, '2018-11-16 00:00:00.000', 50, 50);
/****************************************************************************************
Current Query -- only considers who is the current supervisor
****************************************************************************************/
declare @EmpID int;
select @EmpID = 3;
with [AllEmployees] ([EmployeeID])
as (select [e].[EmployeeID]
from [Employee] as [e]
where [e].[EmployeeID] = @EmpID
union all
select [e].[EmployeeID]
from [Employee] as [e]
join [ReportsToHistory] as [h]
on [e].[EmployeeID] = [h].[EmployeeID]
and [h].[PEDateEnd] is null
join [AllEmployees] as [d]
on [h].[ReportsToID] = [d].[EmployeeID]
where [e].[EmployeeID] <> @EmpID
)
select [e].[EmployeeID]
, [t].[PEDate]
, 'DirectHours' = sum( case
when [p].[ProjectType] = 1 then
.[WorkHours]
else
0
end
)
, 'TotalHours' = sum(.[WorkHours])
from [TimeSheetItem] as
join [TimeSheet] as [t]
on .[TimeID] = [t].[TimeID]
join [Employee] as [e]
on [t].[EmployeeID] = [e].[EmployeeID]
join [AllEmployees] as [ee]
on [e].[EmployeeID] = [ee].[EmployeeID]
join [Project] as [p]
on .[ProjectID] = [p].[ProjectID]
group by [e].[EmployeeID]
, [t].[PEDate];
-- vs. expected result
select [EmpID]
, [PEDate]
, [BillableHours]
, [TotalHours]
from [ResultSet];
go
-- Partial solution, haven't added the where clause for the timesheet info
declare @EmpID int = 3;
with Employees as (
select
[EmployeeID] = [emp].[EmployeeID]
, [EmployeeName] = [emp].[EmployeeName]
, [ReportsToID] = [rth].[ReportsToID]
, [PEDateStart] = [rth].[PEDateStart]
, [PEDateEnd] = isnull([rth].[PEDateEnd],'99991230')
from
[dbo].[Employee] as [emp]
inner join [dbo].[ReportsToHistory] as [rth]
on [emp].[EmployeeID] = [rth].[EmployeeID]
where
[emp].[EmployeeID] = @EmpID
union all
select
[EmployeeID] = [emp].[EmployeeID]
, [EmployeeName] = [emp].[EmployeeName]
, [ReportsToID] = [rth].[ReportsToID]
, [PEDateStart] = case when [rth].[PEDateStart] < [emps].[PEDateStart] then [emps].[PEDateStart] else [rth].[PEDateStart] end
, [PEDateEnd] = isnull([rth].[PEDateEnd],cast('99991230' as date))
from
[dbo].[Employee] as [emp]
inner join [dbo].[ReportsToHistory] as [rth]
on [emp].[EmployeeID] = [rth].[EmployeeID]
inner join [Employees] as [emps]
on [emps].[EmployeeID] = [rth].[ReportsToID]
) --select * from [Employees]
select
[emps].[EmployeeID]
, [ts].[PEDate]
, [DirectHours] = sum(case [prj].[ProjectType] when 1 then [tsi].[WorkHours] else 0 end)
, [TotalHours] = sum([tsi].[WorkHours])
from
[Employees] as [emps]
inner join [dbo].[TimeSheet] as [ts]
on [emps].[EmployeeID] = [ts].[EmployeeID]
and [ts].[PEDate] between [emps].[PEDateStart] and [emps].[PEDateEnd]
inner join [dbo].[TimeSheetItem] as [tsi]
on [ts].[TimeID] = [tsi].[TimeID]
inner join [dbo].[Project] as [prj]
on [tsi].[ProjectID] = [prj].[ProjectID]
group by
[emps].[EmployeeID]
, [ts].[PEDate]
order by
[emps].[EmployeeID]
, [ts].[PEDate];
go
-- Clean up the Sandbox database
if object_id('[dbo].[Employee]','U') is not null
drop table [dbo].[Employee];
if object_id('[dbo].[ReportsToHistory]','U') is not null
drop table [dbo].[ReportsToHistory];
if object_id('[dbo].[Project]','U') is not null
drop table [dbo].[Project];
if object_id('[dbo].[TimeSheet]','U') is not null
drop table [dbo].[TimeSheet];
if object_id('[dbo].[TimeSheetItem]','U') is not null
drop table [dbo].[TimeSheetItem];
if object_id('[dbo].[ResultSet]','U') is not null
drop table [dbo].[ResultSet];
go
November 6, 2018 at 12:07 pm
It probably needs some additional work but I will leave that to you to at least attempt first. Questions or problems please come back here.
November 6, 2018 at 1:27 pm
Lynn Pettis - Tuesday, November 6, 2018 12:07 PMIt probably needs some additional work but I will leave that to you to at least attempt first. Questions or problems please come back here.
Actually, that's exactly what I was asking for. I also like how you removed some redundancy I had in the original query. Thanks!
November 6, 2018 at 2:05 pm
It may still need some more work as it isn't fully tested against all possible scenarios of data.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply