Select TOTAL(last year), TOTAL(year to date), TOTAL (last month)

1

Hi All,

Using TSQL.

I have table BATCHDETAIL which contains

{Person, DateOfPayment, Amount}

I need a simple select query that uses today's date to work out

The prior year
The current year
The prior month

So for today's date 6 Sep 2022

The prior year = 2021
The current year = 2022
The prior month = Aug 2022

I then need to list amounts by person that fit those criteria

EG

Ben Smith 10 20 5

ie. Ben Smith paid £10 in 2021, £20 in 2022 to date and £5 in August 2022.

I can only use a select query to do this as I am not directly interrogating SQL SERVER but am using a simplified third-party front end.

Any help much appreciated. I can't use stored procedures, declarations etc. Just select.

Tom

2

maybe this?

;with miniCalendar
as
(
SELECT
	DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfThisYear,
	DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS EndOfThisYear,
	dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) StartOfLastYear,
	dateadd(yy,-1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)) EndOfLastYear,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) startOfLastMonth,
	DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) as endOfLastMonth

), lastyear
as
(
select sum(a.Amount) as lastYearSum, Person
  From @BATCHDETAIL a
  cross join miniCalendar b
   where DateOfPayment between b.StartOfLastYear and b.EndOfLastYear + 1
   group by Person
), thismonth as
(
	select sum(a.Amount) thisMonthSum, Person
	 From @BATCHDETAIL a
	 cross join miniCalendar b 
	 where a.DateOfPayment between b.startOfLastMonth and b.endOfLastMonth + 1 
	group by Person
), thisyear as
(
select sum(a.Amount) thisYearSum, Person
	 From @BATCHDETAIL a
	 cross join miniCalendar b 
	 where a.DateOfPayment between b.StartOfThisYear and b.EndOfThisYear + 1 
	 group by Person
)
/*
	Ben Smith 10 20 5
	Ben Smith paid £10 in 2021, £20 in 2022 to date and £5 in August 2022.
*/

select  concat(ly.Person,
' paid £',
ly.lastYearSum,
' in ', 
year(StartOfLastYear),
', £',thisYearSum, 
' to date and £', 
thisMonthSum, 
' in ', 
DATENAME(mm,startOfLastMonth), 
' ', 
year(StartOfThisYear)), *
 from lastyear ly
cross apply thismonth
cross apply thisYear
cross apply miniCalendar
1 Like
3

Thank you so much for your help. I'll revert when I've digested this fully!

4

for situations where one of the results is no rows

select  concat(ly.Person,
			' paid £',
			ly.lastYearSum,
			' in ', 
			year(StartOfLastYear),
			', £',thisYearSum, 
			' to date and £', 
			isnull(thisMonthSum,0.00), 
			' in ', 
			DATENAME(mm,startOfLastMonth), 
			' ', 
			year(StartOfThisYear)), *
 from lastyear ly
FULL JOIN  thismonth on 1 = 1 
FULL JOIN thisYear on 1 = 1 
FULL JOIN miniCalendar on 1 = 1