Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 can this query be simplified

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 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

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
Go to Top of Page

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 ;)
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -