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!
INNER JOIN
s to bothTarget
andAnalysis
, you're generating a cross product. Have you tried adding each of the yearly queries as a separate CTE on the query in the function? Then, you can simply pull those results in as extra columns.fnFiscarYear(date)
andfnFiscalMonth(date)
functions?