ronaldo is back

[fazlu_don23]

create table SalesReport(

TerritoryName NVARCHAR(50), 

ProductName NVARCHAR(100), 

TotalSales DECIMAL(10,2), 

PreviousYearSales DECIMAL(10,2), 

GrowthRate DECIMAL(10,2)); 

create table ErrorLog( 

ErrorID int, 

ErrorMessage nvarchar(max),

ErrorDate datetime); 

create or alter procedure sp_territory 

@tablename nvarchar(max) 

as 

begin

-- Declare variables 

declare @SQL nvarchar(max); 

set @SQL = N'with cte 

as (select *, 

CEILING(((TotalSales - PreviousYearSales)*100)/PreviousYearSales) as GrowthRate 

from(select TerritoryName, ProductName, 

Amount as TotalSales, 

lag(amount) over(partition by TerritoryName order by SaleDate) as PreviousYearSales 

from Sales_tbl s 

join Territories_tbl t 

on s.TerritoryID = t.TerritoryID 

join Products_tbl p 

on s.ProductID = p.ProductID 

where Amount > 1000) sub) 

insert into ' + @tablename + ' (TerritoryName, ProductName, TotalSales, PreviousYearSales, GrowthRate) 

select * 

from cte' 

-- Execute the dynamic SQL 

execute sp_executesql @SQL 

end; 

-- Exectuing the sp

execute sp_territory 'SalesReport'; 

--Viewin the table

select * from SalesReport;