Moving Annual Total cross tab by month with Difference in one column
-
Hi
I'm trying to create the below without relying on Excel, but am struggling to get the difference value and % difference showing vertically in one column.
Account No Customer Name Year April May June July August September October November December January February MAT Total A123 Fidget spinner LTD 2016 - 2017 £91,441 £84,629 £93,823 £92,732 £96,511 £100,846 £89,192 £94,777 £101,450 £85,837 £86,788 £1,018,027 A123 Fidget spinner LTD 2017 - 2018 £84,608 £96,389 £110,082 £137,461 £107,662 £107,299 £135,743 £117,290 £124,586 £124,031 £52,770 £1,197,921 Difference -£6,833 £11,760 £16,259 £44,728 £11,151 £6,452 £46,551 £22,513 £23,136 £38,194 -£34,019 £179,894 % -7% 14% 17% 48% 12% 6% 52% 24% 23% 44% -39% 18% -
Thanks. I managed to solve this linking to a time table and using Lag Over (partition AccountNo, [Timetable].Month Order by [Year]. The data was then returned to SSRS and grouped appropriately.
- Trawler - Friday, February 23, 2018 4:34 AM
Okay, fine
- Trawler - Friday, February 23, 2018 4:34 AM
So share... post your code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply