Link to home
Start Free TrialLog in
Avatar of DrakeCA
DrakeCAFlag for Canada

asked on

SQL query to summarize items per month

We have a current SQL query which summarizes the hours worked by a particular branches staff.  Rather then running this report every month I would like to create a new report which automatically calculates the totals for each month, over an 18 month period.

EXAMPLE:

Current query has TOTAL HOURS -- EMPLOYEE FIRST NAME -- EMPLOYEE LAST NAME as headers.

I would like to see: EMPLOYEE FIRST - EMPLOYEE LAST - 2015JAN - 2015FEB...2016JAN - 2016FEB...

The current query is listed below:

use DB1
go
select      sum(timesheet_orders.no_of_hours) as TotalHours,
            user_profile.first_name, user_profile.last_name
From orders
            Inner Join timesheet_orders on orders.order_id = timesheet_orders.order_id
            Inner Join timesheet_header on timesheet_orders.timesheet_number = timesheet_header.timesheet_number
            Inner Join client_organization on orders.group_id = client_organization.group_id
            Inner Join user_profile on timesheet_header.contractor_id = user_profile.user_id

where timesheet_header.start_date >= dateadd(mm,-1,getdate())
             and timesheet_orders.status = 'P' and orders.office_id = '2601'

group by user_profile.first_name, user_profile.last_name

order by TotalHours desc


I know another query we created uses the DATEPART command but I am unsure of the proper syntax to use something like this with our current query.

Any assistance would be appreciated.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

* - Added MS SQL Servers as Topic.

try..
use DB1
go
select      sum(timesheet_orders.no_of_hours) as TotalHours, 
            user_profile.first_name, user_profile.last_name,
			YEAR(timesheet_header.start_date) + ' ' + LEFT(DATENAME(MONTH,timesheet_header.start_date),3) YearMonth 
From orders
            Inner Join timesheet_orders on orders.order_id = timesheet_orders.order_id
            Inner Join timesheet_header on timesheet_orders.timesheet_number = timesheet_header.timesheet_number
            Inner Join client_organization on orders.group_id = client_organization.group_id
            Inner Join user_profile on timesheet_header.contractor_id = user_profile.user_id

where timesheet_header.start_date >= dateadd(mm,-1,getdate()) 
      and timesheet_orders.status = 'P' and orders.office_id = '2601'

group by user_profile.first_name, user_profile.last_name, YEAR(timesheet_header.start_date), MONTH(timesheet_header.start_date)

order by TotalHours desc

Open in new window

Avatar of DrakeCA

ASKER

Thanks for the input.  Looks interesting.

When I try running it I get the following error:

"Msg 8120, Level 16, State 1, Line 1
Column 'timesheet_header.start_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."


Any suggestions?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DrakeCA

ASKER

This does exactly what I need it to.  Thanks so much for your assistance!
You're welcome!  The totalling technique is very helpful once you see it used and get used to it.  Btw, the column renaming code actually took more time than the totals query itself :-) .