CREATE procedure [dbo].[usp_rpt_DigitalChannelConnectivity]-- 532,'<R><P Id ="4"></P></R>','ALL','<R><P Id ="47"></P><P Id ="69"></P><P Id ="70"></P><P Id ="186"></P><P Id ="273"></P><P Id ="306"></P><P Id ="468"></P></R>','<R><P Id="127"></P></R>'
@p_WeekIds int,
@p_OperatorTypeIds Varchar(MAX)='',
@p_MarketIds Varchar(MAX)='',
@p_ChannelIds Varchar(MAX)='',
@p_ClientIds Varchar(MAX)=''
AS
SET NOCOUNT ON
SET ARITHABORT ON
BEGIN
print 'step 1'
print getdate()
DECLARE @t_ChannelIds Table (Id Int)
DECLARE @t_OperatorTypeIds Table (Id Int)
DECLARE @t_MarketIds Table (Id Int)
DECLARE @t_ClientIds Table (Id Int)
INSERT INTO @t_ChannelIds EXECUTE dbo.usp_ParameterIDS @p_ChannelIds
INSERT INTO @t_OperatorTypeIds EXECUTE dbo.usp_ParameterIDS @p_OperatorTypeIds
INSERT INTO @t_MarketIds EXECUTE dbo.usp_ParameterIDS @p_MarketIds
INSERT INTO @t_ClientIds EXECUTE dbo.usp_ParameterIDS @p_ClientIds
print 'step 2'
print getdate()
IF OBJECT_ID(N'tempdb..#Result') IS NOT NULL
BEGIN
DROP TABLE #Result
END
SELECT Channel_Id,Channel_Name,Market_Id,Market_Name,Operator_Id,Operator_Line_Id,
Operator_Type_Id,Operator_Type_Name,Operator_Weightage,Week_Id,Week_Number,
transchannel,[Rank]
INTO #Result FROM
(SELECT temp.Channel_Id,temp.Channel_Name,temp.Market_Id,temp.Market_Name,temp.Operator_Id,temp.Operator_Line_Id,
temp.Operator_Type_Id,temp.Operator_Type_Name,temp.Operator_Weightage,temp.Week_Id,temp.Week_Number,
ISNULL(Trn.Channel_Id,0) as transchannel,
rank() over ( partition by Trn.Week_Id,Trn.Operator_id,Trn.Operator_Line_Id,Trn.Channel_id,Trn.Market_Id
order by CAST(trn.EPG_Number as int)) as [Rank]
from
(SELECT OptWtg.Market_Id,OptWtg.Market_Name,OptWtg.Operator_Id,OptWtg.Operator_Line_Id,
OptWtg.Operator_Type_Id,OptWtg.Operator_Type_Name,OptWtg.Week_Id,OptWtg.Week_Number,OptWtg.Operator_Weightage,
CM.Channel_Id,CM.Channel_Name from
(SELECT Wtg.Market_Id,Mkt.Market_Name,Wtg.Operator_Id,Wtg.Operator_Line_Id,
Wtg.Operator_Type_Id,OTM.Operator_Type_Name,Wtg.Week_Id,WM.Week_Number,Wtg.Operator_Weightage from dbo.tblOperatorWeightage Wtg
INNER JOIN @t_OperatorTypeIds k ON (case when k.Id=0 then Wtg.Operator_Type_Id else k.Id end)=Wtg.Operator_Type_Id
INNER JOIN dbo.vw_OperatorTypeMaster OTM On OTM.Operator_Type_Id=Wtg.Operator_Type_Id
INNER JOIN @t_MarketIds m ON (case when m.Id=0 then Wtg.Market_Id else m.Id end)=Wtg.Market_Id
INNER JOIN dbo.vw_TRPMarket Mkt On Mkt.Market_Id=Wtg.Market_Id
INNER JOIN dbo.vw_WeekMaster WM ON WM.Week_Id=Wtg.Week_Id
where Wtg.Week_Id=@p_WeekIds and Wtg.Weightage_Type_Id=103)OptWtg
CROSS JOIN @t_ChannelIds C
INNER JOIN dbo.vw_ChannelMaster CM ON (case when C.Id=0 then CM.Channel_Id else C.Id end)=CM.Channel_Id ) temp
Left Join dbo.vw_OperatorMasterMarketDetails Trn On Trn.Market_Id=temp.Market_Id and Trn.Operator_Id=temp.Operator_Id and Trn.Operator_Line_Id=temp.Operator_Line_Id
and Trn.Operator_Type_id=temp.Operator_Type_Id and Trn.Week_Id=temp.Week_Id and Trn.Channel_Id=temp.Channel_Id)A
WHERE A.[Rank]=1
GROUP BY
Channel_Id,Channel_Name,Market_Id,Market_Name,Operator_Id,Operator_Line_Id,
Operator_Type_Id,Operator_Type_Name,Operator_Weightage,Week_Id,Week_Number,
transchannel,
[Rank]
print 'step 3'
print getdate()
IF((Select COUNT(*) from @t_ClientIds)=1 and (Select Id from @t_ClientIds)<>0)
BEGIN
select r2.Market_Name,r2.Week_Number,r2.Channel_Name,
ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel<>0),2),0) as Available,
ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel=0),2),0) as Unavailable,
((ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel<>0),2),0))+
(ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel=0),2),0))) as Total
from #Result r2
INNER JOIN dbo.vw_ClientChannelDetails CCD ON CCD.Channel_Id=r2.Channel_Id
INNER JOIN @t_ClientIds CL ON (case when CL.Id=0 then CCD.Client_Id else CL.Id end)=CCD.Client_Id
group by r2.Market_Name,r2.Week_Id,r2.Channel_Name,r2.Market_Id,r2.Week_Id,r2.Channel_Id,r2.Week_Number,CCD.Sort_Order
order by r2.Market_Name,r2.Week_Id,CCD.Sort_Order
END
Else
BEGIN
select r2.Market_Name,r2.Week_Number,r2.Channel_Name,
ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel<>0),2),0) as Available,
ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel=0),2),0) as Unavailable,
((ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel<>0),2),0))+
(ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel=0),2),0))) as Total
from #Result r2
group by r2.Market_Name,r2.Week_Id,r2.Channel_Name,r2.Market_Id,r2.Week_Id,r2.Channel_Id,r2.Week_Number
order by r2.Market_Name,r2.Week_Id,r2.Channel_Name
END
print 'step 4'
print getdate()
IF OBJECT_ID(N'tempdb..#Result') IS NOT NULL
BEGIN
DROP TABLE #Result
END
END
SET ARITHABORT OFF