SQL query to summarize items per month
Posted on 2016-11-23
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.
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:
select sum(timesheet_orders.no_of_hours) as TotalHours,
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.