USE [PUniTest]
GO
/****** Object:  Trigger [dbo].[SUMMARY_UPDATE_Profile]    Script Date: 09/07/2010 09:46:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- =============================================
CREATE TRIGGER [dbo].[SUMMARY_UPDATE_Profile] ON [dbo].[Profile]
FOR INSERT, UPDATE

AS

Declare @Custid Varchar(50)
Declare @Year varchar(4)
Declare @Month varchar(2)

Select @Custid=Custid
FROM inserted WHERE
isnull(inserted.repyear,'') > ''
AND
isnull(inserted.repmonth,'') > ''

IF @Custid IS NOT NULL

  and TRIGGER_NESTLEVEL() > 1
     RETURN

Begin

Select  @Year =  RepYear
from inserted
where Custid = @Custid

Select  @Month = RepMonth
from inserted
where Custid = @Custid


UPDATE Profile
set 
profile.RepNumExtAccess = t1.RepNumExtAccess,
profile.RepNumJanetAccess = t2.RepNumJanetAccess,
profile.RepNumJanetService = t3.RepNumJanetService,
profile.AvgDurExtAccess = t1.AvgDurExtAccess,
profile.AvgDurJanetAccess = t2.AvgDurJanetAccess,
profile.AvgDurJanetService = t3.AvgDurJanetService,
profile.MaxDurExtAccess = t1.MaxDurExtAccess,
profile.MaxDurJanetAccess = t2.MaxDurJanetAccess,
profile.MaxDurJanetService = t3.MaxDurJanetService,
profile.OversixtyExtAccess = t1.OversixtyExtAccess,
profile.OversixtyJanetAccess = t2.OversixtyJanetAccess,
profile.OversixtyJanetService = t3.OversixtyJanetService

from profile, 
(Select Count(Callid) as "RepNumExtAccess" , 
Avg(RepDuration) as "AvgDurExtAccess",
Max(RepDuration) as "MaxDurExtAccess",
(Select COUNT (callid) from 
calllog where calllog.repduration > 60 and calllog.swerncode = 'Ext-access' and
Month (cast(IncidentStart as datetime)) =  datepart(month,(cast('2018-'+@month+'-20' as datetime)))
AND
Year(cast(IncidentStart as datetime)) =  datepart(year,(cast(@year+'-01'+'-20' as datetime)))) as "OversixtyExtAccess"
from calllog
where calllog.swerncode = 'Ext-access' and
Month (cast(IncidentStart as datetime)) =  datepart(month,(cast('2018-'+@month+'-20' as datetime)))
AND
Year(cast(IncidentStart as datetime)) =  datepart(year,(cast(@year+'-01'+'-20' as datetime))))
 as "t1",

(Select Count(Callid) as "RepNumJanetAccess" , 
Avg(RepDuration) as "AvgDurJanetAccess",
Max(RepDuration) as "MaxDurJanetAccess",
(Select COUNT (callid) from 
calllog where calllog.repduration > 60 and calllog.swerncode = 'Janet-access' and
Month (cast(IncidentStart as datetime)) =  datepart(month,(cast('2018-'+@month+'-20' as datetime)))
AND
Year(cast(IncidentStart as datetime)) =  datepart(year,(cast(@year+'-01'+'-20' as datetime)))) as "OversixtyJanetAccess"
from calllog
where calllog.swerncode = 'Janet-access' and
Month (cast(IncidentStart as datetime)) =  datepart(month,(cast('2018-'+@month+'-20' as datetime)))
AND
Year(cast(IncidentStart as datetime)) =  datepart(year,(cast(@year+'-01'+'-20' as datetime))))
 as "t2",

(Select Count(Callid) as "RepNumJanetService" , 
Avg(RepDuration) as "AvgDurJanetService",
Max(RepDuration) as "MaxDurJanetService",
(Select COUNT (callid) from 
calllog where calllog.repduration > 60 and calllog.swerncode = 'Janet-service' and
Month (cast(IncidentStart as datetime)) =  datepart(month,(cast('2018-'+@month+'-20' as datetime)))
AND
Year(cast(IncidentStart as datetime)) =  datepart(year,(cast(@year+'-01'+'-20' as datetime)))) as "OversixtyJanetService"
from calllog
where calllog.swerncode = 'Janet-service' and
Month (cast(IncidentStart as datetime)) =  datepart(month,(cast('2018-'+@month+'-20' as datetime)))
AND
Year(cast(IncidentStart as datetime)) =  datepart(year,(cast(@year+'-01'+'-20' as datetime))))
 as "t3"


Where Profile.CustiD = @Custid



END