Comments by ChandrakanthGaddam (Top 1 by date)

Hi
Thanks for responding my questions. I have executed entire query by passing parameters. Please find the Query below.

---------------------------------------------------------

declare @DateVal Varchar(250) = '07/10/2012',
@PnlSelValue varchar(50) = 'Weekly',
@YearNumber int = 2012,
@Category varchar(max)= '0'

Declare @TTotalProductsValue int
Declare @LTotalProductsValue int
declare @WeekNo int,@MonthNo int

declare @SubCategoryCommasep varchar(max)
declare @count int

select distinct @count=count(Category) from iView_ProductMaster
where Category in ((Select convert(varchar,ID) From fnSplitter(@Category)))

if @count > 0
begin
select @SubCategoryCommasep = COALESCE(@SubCategoryCommasep + ',', '') + Cast(SubCategory as varchar(1000)) from iView_ProductMaster
where Category in ((Select convert(varchar,ID) From fnSplitter(@Category)))

print @SubCategoryCommasep
end
else
begin
set @SubCategoryCommasep = 0
end

IF @PnlSelValue = 'Weekly' or @PnlSelValue = 'Bi-Weekly'
begin
set @DateVal = CONVERT(datetime,@DateVal,103)
select @WeekNo = convert(varchar(10),datepart(wk,@DateVal)), @MonthNo = convert(varchar(10),Month(@DateVal)) ,@YearNumber = convert(varchar(10),year(@DateVal))
end
else if @PnlSelValue = 'Monthly' or @PnlSelValue='Yearly'
begin
set @DateVal = @DateVal
set @YearNumber = @YearNumber
end

SELECT @TTotalProductsValue = SUM(TotalSales), @LTotalProductsValue = SUM(TotalSalesLY) FROM dbo.tbl_Aggregated_Data
Where WeekDate = @DateVal and Yearnumber = @YearNumber and PeriodType = @PnlSelValue
and EAN in (Select EAN from iView_ProductMaster where Category = @Category);

with t as
(select dbo.[InitCap](p.Category)as Category,Sum(d.TotalSalesLY) as 'TotalSalesLY',
Sum(d.TotalSales) as 'TotalSales',Sum(d.TotalUnitsLY) as TotalUnitsLY,Sum(d.TotalUnits) as TotalUnits,
Sum(StoreCountLY) as 'Storecount LY',Sum(StoreCountTY) as 'StoreCount TY'
from tbl_Aggregated_Data d inner join iView_ProductMaster p on p.EAN = d.EAN
Where d.PeriodType = @PnlSelValue and d.WeekDate = @DateVal and Yearnumber = @YearNumber
and d.EAN in (Select EAN from iView_ProductMaster where
(@count =0 or p.Category in (Select convert(varchar,ID) From fnSplitter(@SubCategoryCommasep))))
group by p.Category)
select Category,TotalSalesLY,TotalSales,TotalUnitsLY,TotalUnits,
Convert(decimal(18,1),dbo.ValueShare(TotalSalesLY,@LTotalProductsValue))as ValueShareLY,
Convert(decimal(18,1), dbo.ValueShare(TotalSales,@TTotalProductsValue))as ValueShareTY ,
Convert(decimal(18,1),dbo.ValueGrowth_Per_YA(TotalSalesLY,TotalSales)) as ValueGrowth,
[Storecount LY],[Storecount TY] from t;


Thanks
Chandrakanth