Get yearly total checks issued and total amount

Transact-SQL
1

Greetings experts,

I have the following query:

SELECT distinct r.ERS_EMPNUM, r.LastName, r.FirstName, r.MiddleName,d.CheckDate, Count(Amount) as count_check, SUM(Amount) as total_check
FROM Retirees r inner join EMPLOYEES d ON r.ERS_EMPNUM = d.ERS_EMPNUM 
GROUP BY r.ERS_EMPNUM, r.LastName, r.FirstName, r.MiddleName,d.CheckDate
ORDER BY d.CheckDate DESC, r.ERS_EMPNUM

When this code is run, it displays among other things, total checks issued to an employee every two weeks and the total amount of those two checks.

This works great.

We would like to extend this code to produce how many checks are issued to each employee on 12 month calendar year and the total of those checks.

For instance, if 48 checks are issued, from Jan 2014 to December 2014, what is the total amount of those checks?

I have been stuck on this now for over two days.

Thanks a lot in advance.

2

In your query there isn't anything that filters on the date, so how do you know this query only returns the summary for every 2 weeks?

3

CheckDate shows date checks are issued.

By using COUNT(Amount) as count_check, using SUM(Amount) as total amount of check and finally, by GROUPING, you get the result.

Those checks are issued bi-weekly, around 15th and 31st of each month.

4

Does this help? Couple of different approaches

DECLARE @Chuff TABLE
(
	ERS_EMPNUM INT NOT NULL,
	Name VARCHAR(10) NOT NULL,
	CheckDate DATETIME NOT NULL,
	Amount DECIMAL(18,2) NOT NULL
);

--Mix of dates beyond year range
INSERT INTO @Chuff (ERS_EMPNUM,Name,CheckDate,Amount)
VALUES (1,'Bob','20150115',50.35),
(1,'Bob','20150131',5.33),
(1,'Bob','20150215',500.15),
(1,'Bob','20150228',20.55),
(1,'Bob','20150315',40.25),
(1,'Bob','20141231',20.55),
(1,'Bob','20160115',40.25)

--Declare Date Range
DECLARE @FromDate DATETIME = '20150101',
		@ToDate DATETIME = '20160101';

--Group and return topline view
SELECT	C.ERS_EMPNUM,
		C.Name,
		CheckCount = COUNT(C.Amount),
		CheckAmount = SUM(C.Amount)
FROM	@Chuff AS C
WHERE	C.CheckDate >= @FromDate AND C.CheckDate < @ToDate
GROUP	BY C.ERS_EMPNUM,
		C.Name;

--Group by checkdate and show aggregate total
SELECT	C.ERS_EMPNUM,
		C.Name,
		C.CheckDate,
		--Use GROUPING to add subtotal headings etc.
		CheckDate = CASE WHEN GROUPING(C.CheckDate) = 1 THEN 'Total' ELSE CAST(C.CheckDate AS SQL_VARIANT) END,
		CheckCount = COUNT(C.Amount),
		CheckAmount = SUM(C.Amount)
FROM	@Chuff AS C
WHERE	C.CheckDate >= @FromDate AND C.CheckDate <= @ToDate
GROUP BY GROUPING SETS((C.ERS_EMPNUM,C.Name,C.CheckDate),(ERS_EMPNUM,Name))
5

YOU are the man, Dohsan!

This works a treat!

Thank you soooo much.

The only issue that I have is that assume that an employee started working say, 07/15/1994 and retires say 06/15/2001, is it possible to break it down by year?

For instance, for employee called Bob, is it possible to show:

Bob
from 07/15/1994 26 checks 65,000
from 07/15/1995 25 checks 66,000
etc
ect

Right now, using example above, it gives me a total of 51 checks for a total of 131,000.

Perhaps, there might be a way to divide total checks by total number of years (including leap year) and divide total amount.

This is a complicated mess.

6

If you run the 2 following queries with the test data I posted previously you should be able to see a yearly summary. Utilises the YEAR function to return the year of the provided datetime.

SELECT	C.ERS_EMPNUM,
		C.Name,
		YEAR(C.CheckDate),
		CheckCount = COUNT(C.Amount),
		CheckAmount = SUM(C.Amount)
FROM	@Chuff AS C
GROUP	BY C.ERS_EMPNUM,
		C.Name,
		YEAR(C.CheckDate);
		
		
SELECT	C.ERS_EMPNUM,
		C.Name,
		YEAR(C.CheckDate),
		CheckCount = COUNT(C.Amount),
		CheckAmount = SUM(C.Amount)
FROM	@Chuff AS C
GROUP BY GROUPING SETS((C.ERS_EMPNUM,C.Name,YEAR(C.CheckDate)),(ERS_EMPNUM,Name))

If you're going to be doing a lot of these date manipulations (Working out months/years from a date etc.) I'd advise you read up on using a calendar table to help cut down on some of the overhead Calendar Tables in T-SQL

7

Thank you so much for your help.