Help constructing a query specifically querying against different dates

Question

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!

Answer 1

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 and Year of Targets and Analysis. Your time-related filters, therefore, would look similar to this:

...
AND analysis.Period = MONTH(DATEADD(MONTH, -4, GETDATE()))
AND analysis.Year   = YEAR (DATEADD(MONTH, -4, GETDATE()))

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 the Users 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 the Users table as the joining point for the complete query, something like this:

SELECT
  u.FullName,
  ...
FROM
  dbo.Users AS u
  INNER JOIN
  (  -- month target and analysis
    SELECT
      t.FeeEarnerRef,
      ...
    FROM
      dbo.Targets AS t
      INNER JOIN dbo.Analysis AS a ON t.FeeEarnerRef = a.FeeEarnerRef
        AND t.Period = a.Period
        AND t.Year   = a.Year
    WHERE
      t.Period = MONTH(DATEADD(MONTH, -4, GETDATE()))
      AND t.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      t.FeeEarnerRef
  ) AS mta ON u.FeeEarnerRef = mta.FeeEarnerRef
  INNER JOIN
  (  -- year target
    SELECT
      t.FeeEarnerRef,
      ...
    FROM
      dbo.Targets AS t
    WHERE
      t.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      t.FeeEarnerRef
  ) AS yt ON u.FeeEarnerRef = yt.FeeEarnerRef
  INNER JOIN
  (  -- year analysis
    SELECT
      a.FeeEarnerRef,
      ...
    FROM
      dbo.Analysis AS a
    WHERE
      a.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      a.FeeEarnerRef
  ) AS ya ON u.FeeEarnerRef = ya.FeeEarnerRef
WHERE
  u.FullName NOT LIKE 'Z%'
  AND u.Department = 'IP'
;

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 and Analysis 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:

SELECT
  GroupingCriterion,
  YearData  = SUM(data),
  MonthData = SUM(CASE Period = @ThisPeriod THEN data END)
FROM
  YourTable
WHERE
  Year = @ThisYear
GROUP BY
  GroupingCriterion
;

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:

SELECT
  u.FullName,
  dbo.HoursMins(t.MonthTarget),
  dbo.HoursMins(t.MonthCurrent),
  dbo.HoursMins(t.MonthTarget - t.MonthCurrent),
  dbo.HoursMins(t.YearTarget),
  dbo.HoursMins(t.YearCurrent),
  dbo.HoursMins(t.YearTarget - t.YearCurrent)
FROM
  dbo.Users AS u
  INNER JOIN
  (  -- target
    SELECT
      t.FeeEarnerRef,
      YearTarget   = SUM(t.ChargeableTime),
      MonthTarget  = SUM(CASE t.Period
                           WHEN MONTH(DATEADD(MONTH, -4, GETDATE()))
                           THEN t.ChargeableTime
                         END)
    FROM
      dbo.Targets AS t
    WHERE
      t.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      t.FeeEarnerRef
  ) AS t ON u.FeeEarnerRef = t.FeeEarnerRef
  INNER JOIN
  (  -- analysis
    SELECT
      a.FeeEarnerRef,
      YearCurrent  = SUM(a.ChargeableTime),
      MonthCurrent = SUM(CASE t.Period
                           WHEN MONTH(DATEADD(MONTH, -4, GETDATE()))
                           THEN a.ChargeableTime
                         END)
    FROM
      dbo.Analysis AS a
    WHERE
      a.Year = YEAR(DATEADD(MONTH, -4, GETDATE()))
    GROUP BY
      a.FeeEarnerRef
  ) AS ya ON u.FeeEarnerRef = ya.FeeEarnerRef
WHERE
  u.FullName NOT LIKE 'Z%'
  AND u.Department = 'IP'
;

Answer 2

First and foremost, I'd suggest you to create two INLINE User Defined functions just to calculate fiscal months and fiscal years.

CREATE FUNCTION dbo.fnFiscalMonth(@Date datetime)
RETURNS table 
AS
RETURN
(
    SELECT CASE WHEN MONTH(@Date) >= 1 AND MONTH(@Date) <= 4
                THEN MONTH(@Date) + 8
                ELSE MONTH(@Date) - 4
           END AS FiscalMonth
);
GO

CREATE FUNCTION dbo.fnFiscalYear(@Date datetime)
RETURNS table
AS
RETURN
(
    SELECT CASE WHEN MONTH(@Date) <= 4
                THEN YEAR(@Date) - 1
                ELSE YEAR(@Date)
           END AS FiscalYear
);
GO

Next, you almost have the solution. By adding two subqueries that calculate total by year to your main query.

DECLARE @Department varchar(50) = 'Dept1';
DECLARE @DateSel datetime = '20170302'

DECLARE @FiscalMonth int;
DECLARE @FiscalYear int;

SELECT @FiscalMonth = FiscalMonth FROM dbo.fnFiscalMonth(@DateSel);
SELECT @FiscalYear = FiscalYear FROM dbo.fnFiscalYear(@DateSel);


SELECT     CONCAT(u.Code,', ', u.FullName) AS [Full Name],
           SUM(t.ChargeableTime) AS [Target Chargeable Time],
           SUM(a.ChargeableTime) AS [Current Chargeable Time], 
           MAX(t.ChargeableTime) - SUM(a.ChargeableTime) AS [Shortfall (if negative then over target)],
           (SELECT     SUM(tg.ChargeableTime) AS [TargetYear]
            FROM       Targets tg
            INNER JOIN Users us
            ON         tg.FeeEarnerRef = us.Code
            WHERE      us.Code = u.Code
            AND        us.Department = @Department
            AND        tg.Year = @FiscalYear
           ) AS [Target YR],
           (SELECT     SUM(ay.ChargeableTime) AS [AnalysisYear]
            FROM       Analysis ay
            INNER JOIN Users us
            ON         ay.FeeEarnerRef = us.Code
            WHERE      us.Code = u.Code
            AND        us.Department = @Department
            AND        ay.Year = @FiscalYear
           ) AS [Analysis YR]
FROM       Analysis a
INNER JOIN Targets t
ON         t.Period = a.Period
AND        t.Year = a.Year
INNER JOIN Users u
ON         u.Code = a.FeeEarnerRef
WHERE      u.Department = @Department
AND        a.Period = @FiscalMonth
AND        a.Year = @FiscalYear
GROUP BY   u.Code, u.FullName
;

This is the result:

|Full Name     |Target Chargeable Time|Current Chargeable Time|Shortfall (if negative then over target)|Target YR|Analysis YR|
|:-------------|:---------------------|:----------------------|:---------------------------------------|:--------|:----------|
|DM, Dana Maid |320.00                |44.80                  |35.20                                   |765.00   |151.20     |
|JM, John McNet|320.00                |10.80                  |69.20                                   |765.00   |139.20     |

As @PaulWhite has suggested, I've added two variables @FiscalMonth and @FiscalYear and I've converted UDF into INLINE UDF, just for the sake of performance.

I've set up a dbfiddle here.