I've got this database which holds some time registrations.
It has a Users table, with a field to indicate if the User is still Active.
And a TimeTrackings table, with some hours on certain Dates for certain Users.
I need a query, where the result will give me all Dates between the first of the previous month and today, for all Active Users where the Hours are less than 8.
I came up with the following, with the correct result, but it looks overcomplicated.
It has CTE, cross join, left join, inner join, inner select. It checks twice if the User is Active, ...
Could it be simplified?
-- Test Data
declare @Users Table(UserID int IDENTITY(1,1), Name varchar(50), Active tinyint);
declare @TimeTrackings Table(TimeTrackingID int IDENTITY(1,1), UserID int, [Date] datetime, [CompletedWork] decimal(4,2));
insert into @Users
values
('Alfa',1),
('Bravo',0),
('Charlie',1);
insert into @TimeTrackings
values
(1,CAST('20130114' as datetime),8),
(1,CAST('20130115' as datetime),7),
(1,CAST('20130116' as datetime),5),
(1,CAST('20130116' as datetime),1),
(2,CAST('20130114' as datetime),4),
(2,CAST('20130117' as datetime),8),
(3,CAST('20130121' as datetime),8),
(3,CAST('20130124' as datetime),8),
(3,CAST('20130204' as datetime),3),
(3,CAST('20130204' as datetime),4),
(3,CAST('20130206' as datetime),8);
-- Query
with [Dates] as
(
select [Date] = CAST(DATEADD(DAY,-DAY(GETDATE()-1),DATEADD(MONTH,-1,GETDATE())) as date)
union all
select [Date] = CAST(DATEADD(DAY,1,[Date]) as Date)
from [Dates]
where [Date] < CAST(GETDATE() as date)
)
select [a].[Date], [a].[UserID], [a].[DailyTotal]
from
(
select [Dates].[Date], [usr].[UserID], [ttus].[DailyTotal]
from [Dates]
cross join @Users as [usr]
left join
(
select [tt].[Date], [us].[UserID], SUM([tt].[CompletedWork]) as [DailyTotal]
from @TimeTrackings as [tt]
inner join @Users as [us]
on [tt].[UserID] = [us].[UserID]
where [us].[Active] = 1
group by [tt].[Date], [us].[UserID]
) as [ttus]
on [ttus].[Date] = [Dates].[Date]
and [ttus].[UserID] = [usr].[UserID]
where [usr].[Active] = 1
) as [a]
where [a].[DailyTotal] < 8
or [a].[DailyTotal] is null
order by [a].[UserID], [a].[Date]
edit: fixed copy-paste error
can this query be simplified
Posted - 02/21/2013 : 05:12:39
