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
 General SQL Server Forums
 New to SQL Server Programming
 Grouping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nkpriya
Starting Member

31 Posts

Posted - 03/07/2014 :  16:23:32  Show Profile  Reply with Quote
I need help as I couldn't figure it out.

I have a table which has 4 columns:
Week ending, Resource Name, # of hours worked, Mgr Name

Jan 3, 2014 A 42 Mg1
Jan 10,2014 B 45 Mg2

Now, I need to figure it out how many resources worked >40 for a month(instead of week).

Any thoughts?

Please let me know. Thanks!

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/07/2014 :  16:37:10  Show Profile  Reply with Quote
Here is one way:
DECLARE @Foo TABLE(WeekEnding DATE, ResourceName CHAR(1), HourWorked INT, MgrName VARCHAR(20))

INSERT @Foo VALUES
('Jan 3, 2014', 'A', 42, 'Mg1'),
('Jan 10,2014', 'B', 45, 'Mg2')

SELECT YEAR(WeekEnding), MONTH(WeekEnding), SUM(HourWorked) AS MonthlyHourWorked
FROM @Foo
GROUP BY YEAR(WeekEnding), MONTH(WeekEnding)
Go to Top of Page

nkpriya
Starting Member

31 Posts

Posted - 03/07/2014 :  16:49:51  Show Profile  Reply with Quote
I need count, like how many resources worked more than 45hrs in a week. Thanks!
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/07/2014 :  17:22:56  Show Profile  Reply with Quote
Do you want it by week or month? Your posts are conflicting. Basically, it is a simple group by query with a HAVING clause:
SELECT ResourceName
FROM @Foo
GROUP BY YEAR(WeekEnding), MONTH(WeekEnding), ResourceName 
HAVING SUM(HourWorked) > 45
Go to Top of Page

nkpriya
Starting Member

31 Posts

Posted - 03/07/2014 :  17:34:22  Show Profile  Reply with Quote
I want it by month whereeas I have data by week.
Category for Hrs would be based on 40hrs/week

I need result something like:

MgrName, Month, [CategoryHrs],[ResourceCount]

Mg1 Jan [>40] 2
Mg1 Jan [=40] 3
Mg1 Feb [>40] 10

Thanks!
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.11 seconds. Powered By: Snitz Forums 2000