DrakeCA
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.contracto r_id = user_profile.user_id
where timesheet_header.start_dat e >= 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.
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
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
Inner Join client_organization on orders.group_id = client_organization.group_
Inner Join user_profile on timesheet_header.contracto
where timesheet_header.start_dat
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.
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_da te' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Any suggestions?
When I try running it I get the following error:
"Msg 8120, Level 16, State 1, Line 1
Column 'timesheet_header.start_da
Any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :-) .
try..
Open in new window