Grouping - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Grouping

Author

Topic

nkpriya
Starting Member

34 Posts

Posted - 2014-03-07 : 16:23:32

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-07 : 16:37:10

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)

nkpriya
Starting Member

34 Posts

Posted - 2014-03-07 : 16:49:51

I need count, like how many resources worked more than 45hrs in a week. Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-07 : 17:22:56

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

nkpriya
Starting Member

34 Posts

Posted - 2014-03-07 : 17:34:22

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources