ronaldo is back

fazlu_don23 — Tue, 12 Sep 2023 11:49:12 GMT

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)

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;

topic ronaldo is back in Get Started Discussions