SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 can this query be simplified
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zifter
Starting Member

47 Posts

Posted - 02/21/2013 :  05:12:39  Show Profile  Reply with Quote
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

Edited by - Zifter on 02/21/2013 06:06:50

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/21/2013 :  06:28:12  Show Profile  Reply with Quote

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


--
Chandu
Go to Top of Page

Zifter
Starting Member

47 Posts

Posted - 02/21/2013 :  07:14:37  Show Profile  Reply with Quote
Thanks Bandi, that works perfectly!

I knew it could be done (just not by me ;)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/21/2013 :  08:39:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000