clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1130830 fiddles created (16563 in the last week).

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 );
 hidden batch(es)


CREATE FUNCTION dbo.fnFiscalYear(@Date datetime) RETURNS table AS RETURN ( SELECT WHEN MONTH(@Date) <= 4 THEN YEAR(@Date) - 1 ELSE YEAR(@Date) END as FiscalYear );
Line 6: Incorrect syntax near the keyword 'WHEN'.
 hidden batch(es)


CREATE TABLE Users ( Code char(2), FullName varchar(50), Department varchar(50) ); INSERT INTO Users VALUES ('JM', 'John McNet', 'Dept1'), ('DM', 'Dana Maid', 'Dept1');
2 rows affected
 hidden batch(es)


CREATE TABLE Analysis(FeeEarnerRef char(2), Period int, Year int, ChargeableTime decimal(19,2)); INSERT INTO Analysis VALUES ('JM',1,2016,10.2),('JM',1,2016,5.2),('JM',2,2016,3.5),('JM',3,2016,7.5),('JM',3,2016,7.5),('JM',4,2016,2.2),('JM',4,2016,10),('JM',5,2016,11), ('JM',6,2016,8.2),('JM',6,2016,3),('JM',7,2016,14.2),('JM',7,2016,7.2),('JM',8,2016,12),('JM',9,2016,3),('JM',9,2016,10.2),('JM',10,2016,0.2), ('JM',10,2016,10.2),('JM',11,2016,4.2),('JM',11,2016,1.2),('JM',12,2016,5.5),('JM',12,2016,3), ('DM',1,2016,5.2),('DM',1,2016,14.2),('DM',2,2016,7.5),('DM',3,2016,11.5),('DM',3,2016,2.5),('DM',4,2016,7.2),('DM',4,2016,12),('DM',5,2016,5), ('DM',6,2016,3.2),('DM',6,2016,1),('DM',7,2016,5.2),('DM',7,2016,12.2),('DM',8,2016,4),('DM',9,2016,2),('DM',9,2016,4.2),('DM',10,2016,10.2), ('DM',10,2016,5.2),('DM',11,2016,12.2),('DM',11,2016,10.2),('DM',12,2016,4.5),('DM',12,2016,12), ('JM',1,2017,10.2),('JM',1,2017,5.2),('JM',2,2017,3.5),('JM',3,2017,7.5),('JM',3,2017,7.5),('JM',4,2017,2.2),('JM',4,2017,10),('JM',5,2017,11), ('JM',6,2017,8.2),('JM',6,2017,3),('JM',7,2017,14.2),('JM',7,2017,7.2),('JM',8,2017,12),('JM',9,2017,3),('JM',9,2017,10.2),('JM',10,2017,0.2), ('JM',10,2017,10.2),('JM',11,2017,4.2),('JM',11,2017,1.2),('JM',12,2017,5.5),('JM',12,2017,3), ('DM',1,2017,5.2),('DM',1,2017,14.2),('DM',2,2017,7.5),('DM',3,2017,11.5),('DM',3,2017,2.5),('DM',4,2017,7.2),('DM',4,2017,12),('DM',5,2017,5), ('DM',6,2017,3.2),('DM',6,2017,1),('DM',7,2017,5.2),('DM',7,2017,12.2),('DM',8,2017,4),('DM',9,2017,2),('DM',9,2017,4.2),('DM',10,2017,10.2), ('DM',10,2017,5.2),('DM',11,2017,12.2),('DM',11,2017,10.2),('DM',12,2017,4.5),('DM',12,2017,12) ;
84 rows affected
 hidden batch(es)


CREATE TABLE Targets(FeeEarnerRef char(2), Period int, Year int, ChargeableTime decimal(19,2)); INSERT INTO Targets VALUES ('JM', 1, 2016, 50),('JM', 2, 2016, 55),('JM', 3, 2016, 45),('JM', 4, 2016, 60),('JM', 5, 2016, 55),('JM', 6, 2016, 65),('JM', 7, 2016, 75), ('JM', 8, 2016, 80),('JM', 9, 2016, 65),('JM', 10, 2016, 55),('JM', 11, 2016, 80),('JM', 12, 2016, 80), ('DM', 1, 2016, 50),('DM', 2, 2016, 55),('DM', 3, 2016, 45),('DM', 4, 2016, 60),('DM', 5, 2016, 55),('DM', 6, 2016, 65),('DM', 7, 2016, 75), ('DM', 8, 2016, 80),('DM', 9, 2016, 65),('DM', 10, 2016, 55),('DM', 11, 2016, 80),('DM', 12, 2016, 80), ('JM', 1, 2017, 50),('JM', 2, 2017, 55),('JM', 3, 2017, 45),('JM', 4, 2017, 60),('JM', 5, 2017, 55),('JM', 6, 2017, 65),('JM', 7, 2017, 75), ('JM', 8, 2017, 80),('JM', 9, 2017, 65),('JM', 10, 2017, 55),('JM', 11, 2017, 80),('JM', 12, 2017, 80), ('DM', 1, 2017, 50),('DM', 2, 2017, 55),('DM', 3, 2017, 45),('DM', 4, 2017, 60),('DM', 5, 2017, 55),('DM', 6, 2017, 65),('DM', 7, 2017, 75), ('DM', 8, 2017, 80),('DM', 9, 2017, 65),('DM', 10, 2017, 55),('DM', 11, 2017, 80),('DM', 12, 2017, 80) ;
48 rows affected
 hidden batch(es)


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 ;
Line 8: Invalid object name 'dbo.fnFiscalYear'.
 hidden batch(es)