<?xml version="1.0" encoding="utf-8" ?>
<settings>
    <add key="AgentTable" value="create table [dbo].[{0}](
	                            [id] [char](32) NOT NULL,
	                            [pastStates.pause.count] [int] NULL DEFAULT 0,
	                            [pastStates.waiting.count] [int] NULL DEFAULT 0,
	                            [pastStates.wrapup.count] [int] NULL DEFAULT 0,
	                            [pastStates.online.count] [int] NULL DEFAULT 0,
	                            [pastStates.preview.count] [int] NULL DEFAULT 0,
	                            [pastStates.alerting.count] [int] NULL DEFAULT 0,
	                            [pastStates.pause.duration] [int] NULL DEFAULT 0,
	                            [pastStates.waiting.duration] [int] NULL DEFAULT 0,
	                            [pastStates.wrapup.duration] [int] NULL DEFAULT 0,
	                            [pastStates.online.duration] [int] NULL DEFAULT 0,
	                            [pastStates.preview.duration] [int] NULL DEFAULT 0,
	                            [pastStates.alerting.duration] [int] NULL DEFAULT 0,
	                            [contacts.handled.voice.inbound.count] [int] NULL DEFAULT 0,
	                            [contacts.handled.voice.outbound.count] [int] NULL DEFAULT 0,
	                            [contacts.handled.synchronousText.count] [int] NULL DEFAULT 0,
	                            [contacts.handled.asynchronousText.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.positive.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.negative.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.argued.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.neutral.count] [int] NULL DEFAULT 0,
	                            CONSTRAINT [PK_UsersHistoricalSupervision{0}] PRIMARY KEY NONCLUSTERED 
	                            (
		                            [id] ASC
	                            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                            ) ON [PRIMARY]"/>
    <add key="AgentQuery" value="INSERT INTO {1} (
                                    [id],  
	                                [contacts.qualifications.positive.count],
	                                [contacts.qualifications.neutral.count],
	                                [contacts.qualifications.negative.count],
	                                [contacts.qualifications.argued.count],
	                                [contacts.handled.voice.inbound.count],
	                                [contacts.handled.asynchronousText.count],
	                                [contacts.handled.synchronousText.count],
	                                [contacts.handled.voice.outbound.count],
	                                [pastStates.preview.count] ,
	                                [pastStates.preview.duration] ,
	                                [pastStates.alerting.count] ,
	                                [pastStates.alerting.duration] ,
	                                [pastStates.online.count] ,
	                                [pastStates.online.duration] ,
	                                [pastStates.wrapup.count] ,
	                                [pastStates.wrapup.duration],
	                                [pastStates.pause.count] ,
	                                [pastStates.pause.duration] ,
	                                [pastStates.waiting.count],
	                                [pastStates.waiting.duration] 	
                                ) 
                                select 
	                            ISNULL(ISNULL(fromContacts.id, fromActions.id), fromStates.id) 'id',  
	                            ISNULL(fromContacts.[contacts.qualifications.positive.count],0),
	                            ISNULL(fromContacts.[contacts.qualifications.neutral.count],0),
	                            ISNULL(fromContacts.[contacts.qualifications.negative.count],0),
	                            ISNULL(fromContacts.[contacts.qualifications.argued.count],0),
	                            ISNULL(fromContacts.[contacts.handled.voice.inbound.count],0),
	                            ISNULL(fromContacts.[contacts.handled.asynchronousText.count],0),
	                            ISNULL(fromContacts.[contacts.handled.synchronousText.count],0),
	                            ISNULL(fromContacts.[contacts.handled.voice.outbound.count],0),
	                            ISNULL(fromActions.[pastStates.preview.count],0) ,
	                            ISNULL(fromActions.[pastStates.preview.duration],0) ,
	                            ISNULL(fromActions.[pastStates.alerting.count],0) ,
	                            ISNULL(fromActions.[pastStates.alerting.duration],0) ,
	                            ISNULL(fromActions.[pastStates.online.count],0) ,
	                            ISNULL(fromActions.[pastStates.online.duration],0) ,
	                            ISNULL(fromActions.[pastStates.wrapup.count],0) ,
	                            ISNULL(fromActions.[pastStates.wrapup.duration],0),
	                            ISNULL(fromStates.[pastStates.pause.count],0) ,
	                            ISNULL(fromStates.[pastStates.pause.duration],0) ,
	                            ISNULL(fromStates.[pastStates.waiting.count],0),
	                            ISNULL(fromStates.[pastStates.waiting.duration],0) 	
                            from 
                            (select 
	                            aa.AgentId 'id', 
	                            sum(case when ic.positive &gt; 0 or ec.positive &gt; 0 or cc.positive &gt; 0 or oc.positive &gt; 0 then 1 else 0 end) 'contacts.qualifications.positive.count',
	                            sum(case when ic.positive = 0 or ec.positive = 0 or cc.positive = 0 or oc.positive = 0 then 1 else 0 end) 'contacts.qualifications.neutral.count',
	                            sum(case when ic.positive &lt; 0 or ec.positive &lt; 0 or cc.positive &lt; 0 or oc.positive &lt; 0 then 1 else 0 end) 'contacts.qualifications.negative.count',
	                            sum(case when ic.argued = 1 or ec.argued = 1 or cc.argued = 1 or oc.argued = 1 then 1 else 0 end) 'contacts.qualifications.argued.count',
	                            sum(case when ic.id is not null then 1 else 0 end) 'contacts.handled.voice.inbound.count',
	                            sum(case when ec.id is not null then 1 else 0 end) 'contacts.handled.asynchronousText.count',
	                            sum(case when cc.id is not null then 1 else 0 end) 'contacts.handled.synchronousText.count',
	                            sum(case when oc.id is not null then 1 else 0 end) 'contacts.handled.voice.outbound.count'
                            from [{0}]..AgentActions aa 
                            left join [{0}]..IN_Contact ic on ic.id = aa.ContactId 
                            left join [{0}]..EmailInbound_Contact ec on ec.id = aa.ContactId 
                            left join [{0}]..ChatInbound_Contact cc on cc.id = aa.ContactId 
                            left join [{0}]..OUT_Contact oc on oc.id = aa.ContactId 
                            where aa.ActionId=2 and aa.DateTimeValue between @rangeStart and @rangeEnd
                            group by aa.AgentId) fromContacts 

                            full outer join

                            (select 
	                            aa.AgentId 'id', 
	                            sum(case when ActionId=7 then 1 else 0 end) 'pastStates.preview.count' ,
	                            sum(case when ActionId=7 then duration else 0 end) 'pastStates.preview.duration' ,
	                            sum(case when ActionId=1 then 1 else 0 end) 'pastStates.alerting.count' ,
	                            sum(case when ActionId=1 then duration else 0 end) 'pastStates.alerting.duration' ,
	                            sum(case when ActionId=2 then 1 else 0 end) 'pastStates.online.count' ,
	                            sum(case when ActionId=2 then duration else 0 end) 'pastStates.online.duration' ,
	                            sum(case when ActionId=4 then 1 else 0 end) 'pastStates.wrapup.count' ,
	                            sum(case when ActionId=4 then duration else 0 end) 'pastStates.wrapup.duration' 
                            from [{0}]..AgentActions aa where aa.DateTimeValue between @rangeStart and @rangeEnd
                            group by aa.AgentId) fromActions

                            on  fromContacts.id = fromActions.id

                            full outer join

                            (select 
	                            aa.AgentId 'id', 
	                            sum(case when stateid=3 then 1 else 0 end) 'pastStates.pause.count' ,
	                            sum(case when stateid=3 then duration else 0 end) 'pastStates.pause.duration' ,
	                            sum(case when stateid=4 or stateid=10 then 1 else 0 end) 'pastStates.waiting.count',
	                            sum(case when stateid=4 or stateid=10 then duration else 0 end) 'pastStates.waiting.duration' 	
                            from [{0}]..Agentstates aa where aa.DateTimeValue between @rangeStart and @rangeEnd
                            group by aa.AgentId) fromStates 
                            on fromStates.id = ISNULL(fromActions.id, fromContacts.id)"/>
    
    <add key="QueueTable" value="create table [dbo].[{0}](
		                        [id] [char](32) NOT NULL,
	                            [contacts.past.abandoned.count] [int] NULL DEFAULT 0,
								[contacts.past.handled.respectingSLA.count] [int] NULL DEFAULT 0,
								[contacts.past.handled.notRespectingSLA.count] [int] NULL DEFAULT 0,
	                            CONSTRAINT [PK_UsersHistoricalSupervision{0}] PRIMARY KEY NONCLUSTERED 
	                            (
		                            [id] ASC
	                            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                            ) ON [PRIMARY]"/>
    <add key="QueueQuery" value="INSERT INTO {1}(
					[id],
					[contacts.past.abandoned.count],
					[contacts.past.handled.respectingSLA.count],
					[contacts.past.handled.notRespectingSLA.count]
				)
				select 
				allqueues.queueid 'id',
				sum(case when allqueues.abandoned=1 then 1 else 0 end) 'contacts.past.abandoned.count',
				sum(case when allqueues.respectingSLATime is null and ISNULL(allqueues.abandoned,0)&lt;&gt;1 and overflow.Contactid is null and (a.Id is null or allqueues.duration &lt;= a.SlaTime or a.SlaMode &lt;&gt; 1) then 1 when allqueues.respectingSLATime is not null and ISNULL(allqueues.abandoned,0)&lt;&gt;1 and overflow.Contactid is null and allqueues.respectingSLATime=1 then 1 else 0 end) 'contacts.past.handled.respectingSLA.count',
				sum(case when allqueues.respectingSLATime is null and ISNULL(allqueues.abandoned,0)&lt;&gt;1 and overflow.Contactid is null and a.SlaMode = 1 and allqueues.duration &gt; a.SlaTime then 1 when allqueues.respectingSLATime is not null and ISNULL(allqueues.abandoned,0)&lt;&gt;1 and overflow.Contactid is null and allqueues.respectingSLATime=0 then 1 else 0 end) 'contacts.past.handled.notRespectingSLA.count'
			from
				(select ContactId, queueid, abandoned, respectingSLATime, duration from [{0}]..in_queue where DateTimeValue between @rangeStart and @rangeEnd and queueid is not null
				union all
				select contactid, queueid, abandoned, respectingSLATime, duration from [{0}]..out_queue where DateTimeValue between @rangeStart and @rangeEnd and queueid is not null) allqueues
			left join [{0}]..overflow on allqueues.ContactId = Overflow.ContactId
			left join 
				(select activityid, id from [{0}]..In_Contact where activityId is not null and activityid &lt;&gt; '' and DateTimeValue between @rangeStart and @rangeEnd
				union all
				select activityid, id from [{0}]..ChatInbound_Contact where activityId is not null and activityid &lt;&gt; '' and DateTimeValue between @rangeStart and @rangeEnd
				union all
				select activityid, id from [{0}]..EmailInbound_Contact where activityId is not null and activityid &lt;&gt; '' and DateTimeValue between @rangeStart and @rangeEnd) allcontacts
			on allqueues.ContactId = allcontacts.Id
			left join [{2}]..InboundActivities a on allcontacts.activityid = a.id
			group by allqueues.queueid"/>
    
    <add key="InboundTable" value="create table [dbo].[{0}](
	                            [id] [char](32) NOT NULL,
	                            [contacts.past.systemProcessing.count] [int] NULL DEFAULT 0,
	                            [contacts.past.duringClosingHours.count] [int] NULL DEFAULT 0,
	                            [contacts.past.automatedProcessing.handled.count] [int] NULL DEFAULT 0,
	                            [contacts.past.automatedProcessing.abandoned.count] [int] NULL DEFAULT 0,
	                            [contacts.past.abandoned.count] [int] NULL DEFAULT 0,
								[contacts.past.handled.respectingSLA.count] [int] NULL DEFAULT 0,
								[contacts.past.handled.notRespectingSLA.count] [int] NULL DEFAULT 0,
	                            [contacts.past.overflowed.count] [int] NULL DEFAULT 0,
	                            [contacts.past.transferred.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.positive.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.negative.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.argued.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.neutral.count] [int] NULL DEFAULT 0,
	                            CONSTRAINT [PK_UsersHistoricalSupervision{0}] PRIMARY KEY NONCLUSTERED 
	                            (
		                            [id] ASC
	                            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                            ) ON [PRIMARY]"/>
							
    <add key="InboundQuery" value="INSERT INTO {1}(
					[id],
					[contacts.past.systemProcessing.count],
					[contacts.past.duringClosingHours.count],
					[contacts.past.automatedProcessing.handled.count],
					[contacts.past.automatedProcessing.abandoned.count],
					[contacts.past.overflowed.count],
					[contacts.past.transferred.count],
					[contacts.past.abandoned.count],
					[contacts.past.handled.respectingSLA.count],
					[contacts.past.handled.notRespectingSLA.count],
					[contacts.qualifications.negative.count],
					[contacts.qualifications.positive.count],
					[contacts.qualifications.neutral.count],
					[contacts.qualifications.argued.count]
				)
				select 
				activityid 'id',
				sum(case when contactstateid=2 then 1 else 0 end) 'contacts.past.systemProcessing.count',
				sum(case when contactstateid=1 then 1 else 0 end) 'contacts.past.duringClosingHours.count',
				sum(case when contactstateid=9 then 1 else 0 end) 'contacts.past.automatedProcessing.handled.count',
				sum(case when contactstateid=7 then 1 else 0 end) 'contacts.past.automatedProcessing.abandoned.count',
				sum(case when contactstateid=4 then 1 else 0 end) 'contacts.past.overflowed.count',
				sum(case when contactstateid in (8,6,108) then 1 else 0 end) 'contacts.past.transferred.count',
				sum(case when contactstateid=3 then 1 else 0 end) 'contacts.past.abandoned.count',
				sum(case when respectingSLATime is null and contactstateid = 5 and (queuetime &lt;= a.SlaTime or a.SlaMode &lt;&gt; 1) then 1 when respectingSLATime is not null and contactstateid=5 and respectingSLATime=1 then 1 else 0 end) 'contacts.past.handled.respectingSLA.count',
				sum(case when respectingSLATime is null and contactstateid = 5 and a.SlaMode = 1 and queuetime &gt; a.SlaTime then 1 when respectingSLATime is not null and contactstateid=5 and respectingSLATime=0 then 1 else 0 end) 'contacts.past.handled.notRespectingSLA.count',			
				sum(case when positive &lt; 0 then 1 else 0 end) 'contacts.qualifications.negative.count',
				sum(case when positive &gt; 0 then 1 else 0 end) 'contacts.qualifications.positive.count',
				sum(case when positive = 0 then 1 else 0 end) 'contacts.qualifications.neutral.count',
				sum(case when argued = 1 then 1 else 0 end) 'contacts.qualifications.argued.count'
			from
			(select activityid, contactstateid, positive, argued, respectingSLATime, queuetime from [{0}]..In_Contact where DateTimeValue between @rangeStart and @rangeEnd and activityId is not null and activityid &lt;&gt; ''
			union all
			select activityid, contactstateid, positive, argued, respectingSLATime, queuetime from [{0}]..ChatInbound_Contact where DateTimeValue between @rangeStart and @rangeEnd and activityId is not null and activityid &lt;&gt; ''
			union all
			select activityid, contactstateid, positive, argued, respectingSLATime, queuetime from [{0}]..EmailInbound_Contact where DateTimeValue between @rangeStart and @rangeEnd and activityId is not null and activityid &lt;&gt; '')
			allcontacts
			left join [{2}]..InboundActivities a on allcontacts.activityid = a.id
			group by activityId"/>
    
    <add key="OutboundTable" value="create table [dbo].[{0}](
	                            [id] [char](32) NOT NULL,
	                            [contacts.past.systemProcessing.count] [int] NULL DEFAULT 0,
	                            [contacts.past.automatedProcessing.handled.count] [int] NULL DEFAULT 0,
	                            [contacts.past.automatedProcessing.abandoned.count] [int] NULL DEFAULT 0,
	                            [contacts.past.abandoned.count] [int] NULL DEFAULT 0,
								[contacts.past.handled.count] [int] NULL DEFAULT 0,
	                            [contacts.past.overflowed.count] [int] NULL DEFAULT 0,
	                            [contacts.past.transferred.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.positive.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.negative.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.argued.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.neutral.count] [int] NULL DEFAULT 0,
	                            CONSTRAINT [PK_UsersHistoricalSupervision{0}] PRIMARY KEY NONCLUSTERED 
	                            (
		                            [id] ASC
	                            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                            ) ON [PRIMARY]"/>
    <add key="OutboundQuery" value="INSERT INTO {1}(
					[id],
					[contacts.past.systemProcessing.count],
					[contacts.past.automatedProcessing.handled.count],
					[contacts.past.automatedProcessing.abandoned.count],
					[contacts.past.overflowed.count],
					[contacts.past.transferred.count],
					[contacts.past.abandoned.count],
					[contacts.past.handled.count],
					[contacts.qualifications.negative.count],
					[contacts.qualifications.positive.count],
					[contacts.qualifications.neutral.count],
					[contacts.qualifications.argued.count]
				)
				select 
				activityid 'id',
				sum(case when contactstateid=2 then 1 else 0 end) 'contacts.past.systemProcessing.count',
				sum(case when contactstateid=9 then 1 else 0 end) 'contacts.past.automatedProcessing.handled.count',
				sum(case when contactstateid=7 then 1 else 0 end) 'contacts.past.automatedProcessing.abandoned.count',
				sum(case when contactstateid=4 then 1 else 0 end) 'contacts.past.overflowed.count',
				sum(case when contactstateid in (8,6,108) then 1 else 0 end) 'contacts.past.transferred.count',
				sum(case when contactstateid=3 then 1 else 0 end) 'contacts.past.abandoned.count',
				sum(case when contactstateid=5 then 1 else 0 end) 'contacts.past.handled.count',
				sum(case when positive &lt; 0 then 1 else 0 end) 'contacts.qualifications.negative.count',
				sum(case when positive &gt; 0 then 1 else 0 end) 'contacts.qualifications.positive.count',
				sum(case when positive = 0 then 1 else 0 end) 'contacts.qualifications.neutral.count',
				sum(case when argued = 1 then 1 else 0 end) 'contacts.qualifications.argued.count'
			from [{0}]..Out_Contact where DateTimeValue between @rangeStart and @rangeEnd and activityId is not null and activityid &lt;&gt; ''
			group by activityId"/>
    
    <add key="CampaignTable" value="create table [dbo].[{0}](
	                            [id] [char](32) NOT NULL,
	                            [contacts.qualifications.positive.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.negative.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.argued.count] [int] NULL DEFAULT 0,
	                            [contacts.qualifications.neutral.count] [int] NULL DEFAULT 0,
	                            CONSTRAINT [PK_UsersHistoricalSupervision{0}] PRIMARY KEY NONCLUSTERED 
	                            (
		                            [id] ASC
	                            )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                            ) ON [PRIMARY]"/>
    <add key="CampaignQuery" value="INSERT INTO {1}(
					[id],
					[contacts.qualifications.negative.count],
					[contacts.qualifications.positive.count],
					[contacts.qualifications.neutral.count],
					[contacts.qualifications.argued.count]
				)
				select 
				campaignid 'id',
				sum(case when positive &lt; 0 then 1 else 0 end) 'contacts.qualifications.negative.count',
				sum(case when positive &gt; 0 then 1 else 0 end) 'contacts.qualifications.positive.count',
				sum(case when positive = 0 then 1 else 0 end) 'contacts.qualifications.neutral.count',
				sum(case when argued = 1 then 1 else 0 end) 'contacts.qualifications.argued.count'
			from
			(select campaignId, positive, argued from [{0}]..In_Contact where DateTimeValue between @rangeStart and @rangeEnd and campaignId is not null and campaignId &lt;&gt; ''
			union all
			select campaignId, positive, argued from [{0}]..ChatInbound_Contact where DateTimeValue between @rangeStart and @rangeEnd and campaignId is not null and campaignId &lt;&gt; ''
			union all
			select campaignId, positive, argued from [{0}]..EmailInbound_Contact where DateTimeValue between @rangeStart and @rangeEnd and campaignId is not null and campaignId &lt;&gt; ''
			union all
			select campaignId, positive, argued from [{0}]..Out_Contact where DateTimeValue between @rangeStart and @rangeEnd and campaignId is not null and campaignId &lt;&gt; '')
			allcontacts
			group by campaignId"/>
</settings>           
