March 6, 2008 at 7:04 am
In many of our reports we query the Time table to get staff time data. Now each staff is assigned to a team. They can only be changed on the first of the month, so we have an Effective Date table that looks like this:
effectiveID : int : identity
staffID : int
teamID : int
effectiveDate : datetime
A slice of our Time table looks like this:
staffID : int
date : datetime
When querying a yearly set of data we are seeing big slowdowns in performance because of the way we are checking for each days team assignment. Right now we cannot add the teamID to the Time table as it is a vendor's product. Here is how we are currently retrieving this data.
Select time.staffID,
team.team,
sum(time.hours) as hours
from time
inner join team on team.teamID = (select top 1 teamid from StaffTeamAssignment where staffid=time.staffid and effectivedate <= time.date order by effectivedate desc)
Any suggestions to improve? There has got to be a better way...
March 6, 2008 at 12:26 pm
It would help us help you if you would provide us with the DDL (create statements) for the tables, some sample data (in the form of unioned insert statements), and the expected results based on the sample data.
😎
March 6, 2008 at 3:24 pm
I hope this can help you get started on a solution to your current issue:
create table dbo.TeamEffectiveDate (
EffectiveDateId int identity(1,1),
StaffId int,
TeamId int,
EffectiveDate datetime
);
create table dbo.StaffTime (
StaffTimeId int identity(1,1),
StaffId int,
StaffDate datetime,
Hours tinyint
);
go
insert into dbo.TeamEffectiveDate (StaffId, TeamId, EffectiveDate)
select 1, 1, '2008-01-01' union all
select 2, 1, '2008-01-01' union all
select 3, 2, '2008-01-01' union all
select 1, 1, '2008-02-01' union all
select 2, 1, '2008-02-01' union all
select 3, 2, '2008-02-01' union all
select 1, 2, '2008-03-01' union all
select 2, 1, '2008-03-01' union all
select 3, 2, '2008-03-01' union all
select 1, 2, '2008-04-01' union all
select 2, 1, '2008-04-01' union all
select 3, 1, '2008-04-01';
go
insert into dbo.StaffTime (StaffId, StaffDate, Hours)
select 1, '2008-01-01', 8 union all
select 1, '2008-01-02', 8 union all
select 1, '2008-01-03', 8 union all
select 1, '2008-02-01', 8 union all
select 1, '2008-02-02', 8 union all
select 1, '2008-02-03', 8 union all
select 1, '2008-03-01', 8 union all
select 1, '2008-03-01', 8 union all
select 1, '2008-03-01', 8 union all
select 2, '2008-01-01', 8 union all
select 2, '2008-01-02', 8 union all
select 2, '2008-01-03', 8 union all
select 2, '2008-02-01', 8 union all
select 2, '2008-02-02', 8 union all
select 2, '2008-02-03', 8 union all
select 2, '2008-03-01', 8 union all
select 2, '2008-03-01', 8 union all
select 2, '2008-03-01', 8 union all
select 3, '2008-01-01', 8 union all
select 3, '2008-01-02', 8 union all
select 3, '2008-01-03', 8 union all
select 3, '2008-02-01', 8 union all
select 3, '2008-02-02', 8 union all
select 3, '2008-02-03', 8 union all
select 3, '2008-03-01', 8 union all
select 3, '2008-03-01', 8 union all
select 3, '2008-03-01', 8;
go
--select * from dbo.TeamEffectiveDate;
--go
with cteTeamEffectiveDate (
RowNumber,
StaffId,
TeamId,
EffectiveDate
) as (
select
row_number() over(partition by StaffId order by StaffId, EffectiveDate),
StaffId,
TeamId,
EffectiveDate
from
dbo.TeamEffectiveDate
), cteStaffTeamDates (
StaffId,
TeamId,
StartDate,
EndDate
) as (
select
ted1.StaffId,
ted1.TeamId,
ted1.EffectiveDate as StartDate,
coalesce(ted2.EffectiveDate, getdate()) as EndDate
from
cteTeamEffectiveDate ted1
left outer join cteTeamEffectiveDate ted2
on (ted1.StaffId = ted2.StaffId
and ted1.RowNumber = ted2.RowNumber - 1)
)
select
-- std.*,
-- st.*
std.StaffId,
std.TeamId,
std.StartDate,
std.EndDate,
sum(st.Hours) as SumHours
from
cteStaffTeamDates std
inner join StaffTime st
on (std.StaffId = st.StaffId
and st.StaffDate >= std.StartDate
and st.StaffDate < std.EndDate)
group by
std.StaffId,
std.TeamId,
std.StartDate,
std.EndDate
order by
std.StaffId,
std.TeamId
;
go
drop table dbo.TeamEffectiveDate;
drop table dbo.StaffTime;
go
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply