ronaldo is back
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-12-2023 04:49 AM
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;
