Replacing Selfjoins with analytic functions in Sql Server 2012

Sql Server 2012 has introduced a  set of new  analytic  functions ,which helps to  get  different analytical   answers   from an dataset , with  a less complex and more understandable  approach.

The aim of this article is to implement newly introduced lag function ,and the same implementation with an old approach, in my case a self join   based.


DatasetUsed:Adventure Works2008R2

Imagine I want  tocompare  the sales in each country by  current  calendar year  with the sales for  previous calendar year for the below dataset


with cte
as
(
select  d.CalendarYear,t.SalesTerritoryCountry ,sum(s.SalesAmount) as sales  from [dbo].[DimDate] d inner join [dbo].[FactResellerSales] s
on d.DateKey=s.OrderDateKey
inner join DimSalesTerritory t
on s.SalesTerritoryKey=t.SalesTerritoryKey
group by d.CalendarYear,t.SalesTerritoryCountry)


Implementing using the lag function,

select cte.CalendarYear,cte.SalesTerritoryCountry,cte.sales as  CurrentYearSales,lag(cte.sales,1,0) over(partition by cte.salesterritorycountry order by Cte.calendaryear) as t   from cte


Implementing using a selfjoin approach

select cte.CalendarYear,cte.SalesTerritoryCountry,cte.sales as CurrentYearSales,Coalesce(t.sales,0) as PrevYearSales from cte
left join cte  as t on
t.CalendarYear=cte.CalendarYear-1
and  cte.SalesTerritoryCountry=t.SalesTerritoryCountry
order by SalesTerritoryCountry,calendaryear

Note:The null values which were replaced by coalsce in this query, is done by the third attribute of lag function with a value 0(lag(cte.sales,1,0))


As you can observe analytic functions makes  the code more readable,  and we can embed more complex logic dervived from this.