I have cut all the table joins down to help with making this ledgible. As mentioned this then goes into SSRS and is grouped up
declare @Startdate date = (select DATEADD ( year, -1, [Year] ) from [Time] where pk_date = cast(current_timestamp as date) )
declare @EndDate date = (select DATEADD ( year, 1, [Year] ) from [Time] where pk_date = cast(current_timestamp as date) )
declare @CurrentYear date = (select year from [Time] where pk_date = cast(current_timestamp as date) )
select * ,
case when count ( ThisYearSales ) over (partition by AccountNo ) + count ( LastYearSales ) over (partition by AccountNo ) > 0 then 0 else 1 end as NoRecentSalesFlag
from (
select Customers.AccountNo ,
Customers.CustomerName ,
SalesPersonCurrent.SalesPersonName ,
[Month] ,
[Year] ,
year (Year) as YearNumber ,
month (Month) as MonthNumber ,
Time.Year_Month ,
Time.Fiscal_Month_Friendly_Name as [Month_Name],
LineSalePrice as ThisYearSales , --YTDCmlSales ,
LAG (linesaleprice, 1) OVER (partition by Customers.AccountNo, Fiscal_Month_Friendly_Name ORDER BY [Year]) as LastYearSales ,
LineSalePrice - LAG (linesaleprice, 1) OVER (partition by Customers.AccountNo, Fiscal_Month_Friendly_Name ORDER BY [Year]) as YoYDifference ,
(LineSalePrice - LAG (linesaleprice, 1) OVER (partition by Customers.AccountNo, Fiscal_Month_Friendly_Name ORDER BY [Year]))
/ LAG (linesaleprice, 1) OVER (partition by Customers.AccountNo, Fiscal_Month_Friendly_Name ORDER BY [Year]) as YoYPCDifference
from [Time]
cross join
Customers
inner join
SalesPerson SalesPersonCurrent ON Customers.Company = SalesPersonCurrent.Company AND Customers.SalesPerson = SalesPersonCurrent.SalesPerson
left join
( select Headers.Company , Headers.AccountNo , Customers.CustomerName , Time.Year_Month ,
Sum ( lines.quantity ) as Quantity ,
sum ( lines.linesaleprice ) as LineSalePrice
FROM ThinTable -- (various tables joins here)
INNER JOIN
[Time] on Headers.InvoiceDate = [Time].PK_Date
where Headers.Company = 1
and Time.PK_Date >= @Startdate
and CustomerGroups.CustomerGroupDescription = 'Required Business Area'
group by Headers.Company , Headers.AccountNo , Customers.CustomerName , [Time].[Month] , [Time].[Year], Time.Year_Month
) Sales on Sales.AccountNo = Customers.AccountNo and Sales.Company = Customers.Company and sales.Year_Month = Time.Year_Month
where Time.Day_Of_Month = 1 and Time.PK_Date >= @Startdate and Time.PK_Date < @EndDate
) a where [Year] = @CurrentYear