Fee Discount by Year

This is a quick and dirty look at fee discounts by year for a given client. It utilizes the matter history table.

USE son_db;
GO

DECLARE @client NVARCHAR(10);

SET @client = N'000001';


SELECT
CAST(YEAR(peendt) AS VARCHAR(10)) AS 'Year'
,SUM(mhdowkdb) AS 'Worked'
,SUM(mhdobidb) AS 'Billed'
,SUM(mhdobidb)-SUM(mhdowkdb) AS 'Discount'
,CASE WHEN SUM(mhdowkdb)=0 THEN 0 ELSE (SUM(mhdobidb)-SUM(mhdowkdb))/SUM(mhdowkdb) END AS 'Disc %'
FROM matths
INNER JOIN periodt ON mhper=pe
INNER JOIN matter ON mhmatter=mmatter
WHERE mclient=@client
GROUP BY YEAR(peendt)

UNION

SELECT
'Total' AS 'Year'
,SUM(mhdowkdb) AS 'Worked'
,SUM(mhdobidb) AS 'Billed'
,SUM(mhdobidb)-SUM(mhdowkdb) AS 'Discount'
,CASE WHEN SUM(mhdowkdb)=0 THEN 0 ELSE (SUM(mhdobidb)-SUM(mhdowkdb))/SUM(mhdowkdb) END AS 'Disc %'
FROM matths
INNER JOIN periodt ON mhper=pe
INNER JOIN matter ON mhmatter=mmatter
WHERE mclient=@client