clear markdown compare help donate comments/suggestions/bugs a leap of faith? diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 432228 distinct fiddles created so far.

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)