Author |
Topic |
Zifter
Yak Posting Veteran
51 Posts |
Posted - 2013-02-21 : 05:12:39
|
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 Datadeclare @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 @Usersvalues('Alfa',1),('Bravo',0),('Charlie',1);insert into @TimeTrackingsvalues(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);-- Querywith [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] < 8or [a].[DailyTotal] is nullorder by [a].[UserID], [a].[Date] edit: fixed copy-paste error |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-21 : 06:28:12
|
[code]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 [Dates].[Date], [usr].[UserID], [ttus].[DailyTotal] from [Dates] cross join @Users as [usr] left join (select [tt].[Date], [tt].[UserID], SUM([tt].[CompletedWork]) as [DailyTotal] from @TimeTrackings as [tt] group by [tt].[Date], [tt].[UserID] ) as [ttus] on [ttus].[Date] = [Dates].[Date] and [ttus].[UserID] = [usr].[UserID]where [usr].[Active] = 1 and ([DailyTotal] < 8 or [DailyTotal] is null)order by [UserID], [Date];[/code]--Chandu |
|
|
Zifter
Yak Posting Veteran
51 Posts |
Posted - 2013-02-21 : 07:14:37
|
Thanks Bandi, that works perfectly!I knew it could be done (just not by me ;) |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-21 : 08:39:19
|
quote: Originally posted by Zifter Thanks Bandi, that works perfectly!I knew it could be done (just not by me ;)
Welcome--Chandu |
|
|
|
|
|