Apologies for the title but it's not easy to summarise what the issue i'm having is! I'm still a very beginner in SQL (literally 8 weeks of learning, and I'm completely stumped!)
I work for a law firm and have been asked to produce a report to go to the head of each department. This report will have details for each solicitor in the department showing what their target amount of chargeable hours for the month is, how many chargeable hours they have submitted this month, what their target for the year is, and what their progress for the year is (hope that makes sense).
This information is stored in 2 tables; Targets and Analysis. (as well as a user table which converts their name, which is stored as initials in these 2 tables, into their full names). These tables have multiple time entries for each month (but months are stored as period, hence the case statement below to convert period to months in a financial year (May – Apr), and as such, multiple month entries for each year for the solicitors to record their time spent on each case they are working on.
I have a query (that is actually a stored function) that achieves this for the monthly figures as below :
USE [Partner]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Departmental_Chargeable_Time_Targets] (@department VARCHAR(15))
RETURNS TABLE
AS
RETURN (
SELECT users.fullname AS [Name]
,dbo.HoursMins(max(targets.chargeabletime)) AS [Target Chargeable Time]
,dbo.HoursMins(sum(analysis.chargeabletime)) AS [Current Chargeable Time]
,dbo.HoursMins(max(targets.chargeabletime) - sum(analysis.chargeabletime)) AS [Shortfall (if negative then over target)]
FROM Analysis
INNER JOIN targets ON analysis.feeearnerref = targets.feeearnerref
AND Analysis.Period = Targets.period
AND analysis.year = targets.year
INNER JOIN users ON analysis.feeearnerref = users.Code
WHERE users.Department = @department
AND users.FullName NOT LIKE 'Z%'
AND analysis.Period = CASE
WHEN MONTH(GETDATE()) = 1
THEN 9
WHEN MONTH(GETDATE()) = 2
THEN 10
WHEN MONTH(GETDATE()) = 3
THEN 11
WHEN MONTH(GETDATE()) = 4
THEN 12
WHEN MONTH(GETDATE()) = 5
THEN 1
WHEN MONTH(GETDATE()) = 6
THEN 2
WHEN MONTH(GETDATE()) = 7
THEN 3
WHEN MONTH(GETDATE()) = 8
THEN 4
WHEN MONTH(GETDATE()) = 9
THEN 5
WHEN MONTH(GETDATE()) = 10
THEN 6
WHEN MONTH(GETDATE()) = 11
THEN 7
WHEN MONTH(GETDATE()) = 12
THEN 8
END
AND analysis.year = CASE
WHEN MONTH(GETDATE()) IN (
1
,2
,3
,4
)
THEN YEAR(GetDate()) - 1
ELSE analysis.year
END
GROUP BY users.fullname
)
This produces a table like this:
What I'm struggling with is adding the yearly figures to this table, or even to each other. I have written 2 separate queries that work. The 1st one gives the yearly target, and the 2nd gives the progress towards the yearly target. They are as follows ;
SELECT u.FullName
,dbo.hoursmins((sum(t.ChargeableTime))) AS 'Year Target'
FROM Targets t
INNER JOIN users u ON t.feeearnerref = u.Code
WHERE u.FullName NOT LIKE 'Z%'
AND t.year = CASE
WHEN MONTH(GETDATE()) IN (
1
,2
,3
,4
)
THEN YEAR(GetDate()) - 1
ELSE t.year
END
AND u.Department = 'IP'
GROUP BY u.FullName
and
SELECT u.FullName
,dbo.hoursmins((sum(a.chargeabletime))) AS 'Current Chargeable Time'
FROM Analysis a
INNER JOIN users u ON a.feeearnerref = u.Code
WHERE u.FullName NOT LIKE 'Z%'
AND a.year = CASE
WHEN MONTH(GETDATE()) IN (
1
,2
,3
,4
)
THEN YEAR(GetDate()) - 1
ELSE a.year
END
AND u.Department = 'IP'
GROUP BY u.FullName
My problems is that 1) I can't even join 2 yearly queries together, whenever I try I get an error along the lines of "Arithmetic overflow error converting expression to data type int". 2) I need both these sets of data adding to that initial function I create.
Sorry this is so long and rambling but I'm stumped and it's not easy to explain!
Best Answer
Calendar month to financial month
First of all, you can do without all those nicely formatted CASEs and use simple date arithmetic instead.
Your financial year starts in May, which means the number of a month as a financial year month lags behind the number of that month as a calendar month by four. Similarly, the year value of a financial year changes to a new one four months later than that of the calendar year.
Therefore, to convert the current calendar year and month to the corresponding financial year and month, you just need to subtract four months from the current date and extract the year and the month from the result to match against
Period
andYear
ofTargets
andAnalysis
. Your time-related filters, therefore, would look similar to this:Joining the results
Use your current queries (the monthly query, the yearly target query and the yearly actual data query) as derived tables and join them together on the employee ID (
FeeEarnerRef
). Do not use theUsers
inside the derived tables, as there is no need to join it several times when you can do that just once. In fact, you can use theUsers
table as the joining point for the complete query, something like this:I would also suggest that you not apply the formatting (the
dbo.HoursMins
function) in the derived tables. Ideally, you should not apply formatting in a SQL query at all; instead, you should delegate that job to the presentation layer. However, if you must do the formatting in the query, do that in the main SELECT only, applying it to the data returned by the derived tables.Alternative method
The solution suggested above merely resolves the immediate issues you have faced trying to join various pieces of data in a single result set. There is a potentially better way to do the same calculations which allows the both
Targets
andAnalysis
to be scanned only once.The idea is to aggregate both the yearly and the monthly portion of data in the same (sub)query, using a conditional when aggregating the monthly data. Basically, a query of that kind would look like this:
You can use the above pattern to calculate separately the target data and the actual data and join them as derived tables to
Users
similarly to the first solution: