Author |
Topic |
paulkem
Starting Member
28 Posts |
Posted - 2013-05-08 : 15:31:49
|
I have two tables. Table #1CREATE TABLE [XXX].[SLHIH]( [WorkDate] [date] NOT NULL, [BusinessUnit] [varchar](5) NOT NULL, [ContactType] [varchar](5) NULL, [TotalContacts] [int] NULL, [SIH] [bigint] NULL, [SLEligible] [int] NULL, [InServiceLevel] [int] NULL, CONSTRAINT [PK_SLHIH] PRIMARY KEY CLUSTERED ( [WorkDate] ASC, [BusinessUnit] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]Table #2CREATE TABLE [XXX].[HoursOfOperation]( [CalendarDate] [date] NOT NULL, [BusinessUnit] [varchar](5) NOT NULL, [StartTime] [time](0) NOT NULL, [EndTime] [time](0) NOT NULL, [GMTDifference] [int] NULL, [WorkingDate] [bit] NULL, CONSTRAINT [PK_HoursOfOperation] PRIMARY KEY CLUSTERED ( [CalendarDate] ASC, [BusinessUnit] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]Data is added to SLHIH daily via scheduled process.For the report, I need to find the dates on SLHIH that are WorkingDate = FALSE for each BusinessUnit and replace their data fields (TotalContacts, SIH, SLEligible, InServiceLevel) with the contents of the most recent previous date for that same BusinessUnit that WAS a WorkingDate (WorkingDate = TRUE). For example, if Sunday is not a WorkingDate for BusinessUnit A, and Saturday was, I need Saturday's data to replace Sunday.I would prefer to do this “on the fly” rather than actually replace the contents on the table.How in the heck?PK |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-08 : 20:04:32
|
Hi there,Need some clarification;SLHIH table does not contain WorkingDate field, did you mean to say HoursOfOperation table? Are you replacing data in SLHIH with the data from the same table?It helps if you can provide some example data in a consumable format and your intended output. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-09 : 00:39:15
|
[code];With CTEAS(SELECT s.*,h.WorkingDate,ROW_NUMBER() OVER (PARTITION BY s.BusinessUnit ORDER BY s.WorkDate) AS RnFROM [XXX].[SLHIH] sINNER JOIN [XXX].[HoursOfOperation] hON h.[CalendarDate] = s.[WorkDate] )SELECT c.[WorkDate],c.[BusinessUnit],c.[ContactType],CASE WHEN c.WorkingDate = 1 THEN c.[TotalContacts] ELSE c1.[TotalContacts] END,CASE WHEN c.WorkingDate = 1 THEN c.[SIH] ELSE c1.[SIH] END,CASE WHEN c.WorkingDate = 1 THEN c.[SLEligible] ELSE c1.[SLEligible] END,CASE WHEN c.WorkingDate = 1 THEN c.[InServiceLevel] ELSE c1.[InServiceLevel] END FROM CTE cOUTER APPLY (SELECT TOP 1 TotalContacts, SIH, SLEligible, InServiceLevel FROM CTE WHERE Rn < c.Rn AND BusinessUnit = c.BusinessUnit AND WorkingDate = 1 ORDER BY Rn DESC )c1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-09 : 01:08:20
|
Nice Visakh. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-09 : 01:24:28
|
quote: Originally posted by russell Nice Visakh.
Thanks ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
paulkem
Starting Member
28 Posts |
Posted - 2013-05-09 : 08:12:44
|
I will try to test this a bit later. In the meantime, I did prepare some sample data. CREATE TABLE #HoursOfOperation ( [CalendarDate] [date] NOT NULL, [BusinessUnit] [varchar](5) NOT NULL, [StartTime] [time](0) NOT NULL, [EndTime] [time](0) NOT NULL, [GMTDifference] [int] NULL, [WorkingDate] [bit] NULL)INSERT INTO #HoursOfOperation ([CalendarDate] ,[BusinessUnit] ,[StartTime] ,[EndTime] ,[GMTDifference] ,[WorkingDate])SELECT N'2012-05-18' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-18' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-18' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-18' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-19' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'08:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-19' AS [CalendarDate], N'C' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-19' AS [CalendarDate], N'M' AS [BusinessUnit], N'08:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-19' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-20' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-20' AS [CalendarDate], N'C' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-20' AS [CalendarDate], N'M' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-20' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-21' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-21' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-21' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-21' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-22' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-22' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-22' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-22' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-23' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-23' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-23' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-23' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-24' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-24' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-24' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-24' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-25' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-25' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-25' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-25' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-26' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'08:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-26' AS [CalendarDate], N'C' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-26' AS [CalendarDate], N'M' AS [BusinessUnit], N'08:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-26' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-27' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-27' AS [CalendarDate], N'C' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-27' AS [CalendarDate], N'M' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-27' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-28' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-28' AS [CalendarDate], N'C' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-28' AS [CalendarDate], N'M' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-28' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-05-29' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-29' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-29' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-29' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-30' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-30' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-30' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-30' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-31' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-31' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-31' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-05-31' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-01' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-01' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-01' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-01' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-02' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'08:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-02' AS [CalendarDate], N'C' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-06-02' AS [CalendarDate], N'M' AS [BusinessUnit], N'08:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-02' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-06-03' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-06-03' AS [CalendarDate], N'C' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-06-03' AS [CalendarDate], N'M' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-06-03' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'00:00:00' AS [StartTime], N'00:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'0' AS [WorkingDate] UNION ALLSELECT N'2012-06-04' AS [CalendarDate], N'BCE' AS [BusinessUnit], N'06:00:00' AS [StartTime], N'23:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-04' AS [CalendarDate], N'C' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'19:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-04' AS [CalendarDate], N'M' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'18:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate] UNION ALLSELECT N'2012-06-04' AS [CalendarDate], N'SLP' AS [BusinessUnit], N'07:00:00' AS [StartTime], N'17:00:00' AS [EndTime], N'-5' AS [GMTDifference], N'1' AS [WorkingDate]CREATE TABLE #SLHIH ( [WorkDate] [date] NOT NULL, [BusinessUnit] [varchar](5) NOT NULL, [ContactType] [varchar](5) NULL, [TotalContacts] [int] NULL, [SIH] [bigint] NULL, [SLEligible] [int] NULL, [InServiceLevel] [int] NULL)INSERT INTO #SLHIH ([WorkDate] ,[BusinessUnit] ,[ContactType] ,[TotalContacts] ,[SIH] ,[SLEligible] ,[InServiceLevel])SELECT N'2012-05-18' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'556' AS [TotalContacts], N'14873523' AS [SIH], N'541' AS [SLEligible], N'539' AS [InServiceLevel] UNION ALLSELECT N'2012-05-18' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'118' AS [TotalContacts], N'1161279' AS [SIH], N'105' AS [SLEligible], N'101' AS [InServiceLevel] UNION ALLSELECT N'2012-05-18' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'90' AS [TotalContacts], N'1554031' AS [SIH], N'84' AS [SLEligible], N'79' AS [InServiceLevel] UNION ALLSELECT N'2012-05-18' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'22' AS [TotalContacts], N'85229' AS [SIH], N'20' AS [SLEligible], N'19' AS [InServiceLevel] UNION ALLSELECT N'2012-05-19' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'153' AS [TotalContacts], N'13578396' AS [SIH], N'136' AS [SLEligible], N'136' AS [InServiceLevel] UNION ALLSELECT N'2012-05-19' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'4' AS [TotalContacts], N'74586' AS [SIH], N'1' AS [SLEligible], N'1' AS [InServiceLevel] UNION ALLSELECT N'2012-05-19' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'21' AS [TotalContacts], N'627826' AS [SIH], N'16' AS [SLEligible], N'16' AS [InServiceLevel] UNION ALLSELECT N'2012-05-19' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'1' AS [TotalContacts], N'6481' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-20' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'17' AS [TotalContacts], N'13444390' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-20' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'3' AS [TotalContacts], N'69794' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-20' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'5' AS [TotalContacts], N'405417' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-20' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'1' AS [TotalContacts], N'6481' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-21' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'995' AS [TotalContacts], N'24977712' AS [SIH], N'968' AS [SLEligible], N'957' AS [InServiceLevel] UNION ALLSELECT N'2012-05-21' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'149' AS [TotalContacts], N'1227155' AS [SIH], N'141' AS [SLEligible], N'139' AS [InServiceLevel] UNION ALLSELECT N'2012-05-21' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'86' AS [TotalContacts], N'974033' AS [SIH], N'75' AS [SLEligible], N'73' AS [InServiceLevel] UNION ALLSELECT N'2012-05-21' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'30' AS [TotalContacts], N'211086' AS [SIH], N'28' AS [SLEligible], N'28' AS [InServiceLevel] UNION ALLSELECT N'2012-05-22' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'688' AS [TotalContacts], N'17449948' AS [SIH], N'661' AS [SLEligible], N'655' AS [InServiceLevel] UNION ALLSELECT N'2012-05-22' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'121' AS [TotalContacts], N'592491' AS [SIH], N'106' AS [SLEligible], N'105' AS [InServiceLevel] UNION ALLSELECT N'2012-05-22' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'89' AS [TotalContacts], N'1155091' AS [SIH], N'78' AS [SLEligible], N'77' AS [InServiceLevel] UNION ALLSELECT N'2012-05-22' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'32' AS [TotalContacts], N'201052' AS [SIH], N'31' AS [SLEligible], N'31' AS [InServiceLevel] UNION ALLSELECT N'2012-05-23' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'633' AS [TotalContacts], N'19515345' AS [SIH], N'614' AS [SLEligible], N'609' AS [InServiceLevel] UNION ALLSELECT N'2012-05-23' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'90' AS [TotalContacts], N'2002184' AS [SIH], N'69' AS [SLEligible], N'65' AS [InServiceLevel] UNION ALLSELECT N'2012-05-23' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'91' AS [TotalContacts], N'1154680' AS [SIH], N'73' AS [SLEligible], N'73' AS [InServiceLevel] UNION ALLSELECT N'2012-05-23' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'39' AS [TotalContacts], N'256769' AS [SIH], N'39' AS [SLEligible], N'38' AS [InServiceLevel] UNION ALLSELECT N'2012-05-24' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'248' AS [TotalContacts], N'7116399' AS [SIH], N'183' AS [SLEligible], N'181' AS [InServiceLevel] UNION ALLSELECT N'2012-05-24' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'95' AS [TotalContacts], N'2611811' AS [SIH], N'69' AS [SLEligible], N'56' AS [InServiceLevel] UNION ALLSELECT N'2012-05-24' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'78' AS [TotalContacts], N'2302893' AS [SIH], N'66' AS [SLEligible], N'65' AS [InServiceLevel] UNION ALLSELECT N'2012-05-24' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'22' AS [TotalContacts], N'339496' AS [SIH], N'18' AS [SLEligible], N'18' AS [InServiceLevel] UNION ALLSELECT N'2012-05-25' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'390' AS [TotalContacts], N'3412457' AS [SIH], N'379' AS [SLEligible], N'378' AS [InServiceLevel] UNION ALLSELECT N'2012-05-25' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'128' AS [TotalContacts], N'3186444' AS [SIH], N'108' AS [SLEligible], N'108' AS [InServiceLevel] UNION ALLSELECT N'2012-05-25' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'61' AS [TotalContacts], N'1089600' AS [SIH], N'56' AS [SLEligible], N'53' AS [InServiceLevel] UNION ALLSELECT N'2012-05-25' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'26' AS [TotalContacts], N'197847' AS [SIH], N'23' AS [SLEligible], N'23' AS [InServiceLevel] UNION ALLSELECT N'2012-05-26' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'33' AS [TotalContacts], N'2261914' AS [SIH], N'29' AS [SLEligible], N'29' AS [InServiceLevel] UNION ALLSELECT N'2012-05-26' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'19' AS [TotalContacts], N'2435021' AS [SIH], N'2' AS [SLEligible], N'2' AS [InServiceLevel] UNION ALLSELECT N'2012-05-26' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'21' AS [TotalContacts], N'1050826' AS [SIH], N'1' AS [SLEligible], N'1' AS [InServiceLevel] UNION ALLSELECT N'2012-05-26' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'3' AS [TotalContacts], N'109073' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-27' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'4' AS [TotalContacts], N'2248488' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-27' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'19' AS [TotalContacts], N'2795087' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-27' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'20' AS [TotalContacts], N'1046419' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-27' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'3' AS [TotalContacts], N'109073' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-28' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'4' AS [TotalContacts], N'2248488' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-28' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'19' AS [TotalContacts], N'2795087' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-28' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'20' AS [TotalContacts], N'1046419' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-28' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'3' AS [TotalContacts], N'109073' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-05-29' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'298' AS [TotalContacts], N'5509843' AS [SIH], N'297' AS [SLEligible], N'288' AS [InServiceLevel] UNION ALLSELECT N'2012-05-29' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'98' AS [TotalContacts], N'3879565' AS [SIH], N'91' AS [SLEligible], N'72' AS [InServiceLevel] UNION ALLSELECT N'2012-05-29' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'102' AS [TotalContacts], N'1963964' AS [SIH], N'97' AS [SLEligible], N'86' AS [InServiceLevel] UNION ALLSELECT N'2012-05-29' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'20' AS [TotalContacts], N'238803' AS [SIH], N'19' AS [SLEligible], N'17' AS [InServiceLevel] UNION ALLSELECT N'2012-05-30' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'1021' AS [TotalContacts], N'19662093' AS [SIH], N'737' AS [SLEligible], N'733' AS [InServiceLevel] UNION ALLSELECT N'2012-05-30' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'166' AS [TotalContacts], N'2203307' AS [SIH], N'99' AS [SLEligible], N'97' AS [InServiceLevel] UNION ALLSELECT N'2012-05-30' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'78' AS [TotalContacts], N'2340464' AS [SIH], N'31' AS [SLEligible], N'29' AS [InServiceLevel] UNION ALLSELECT N'2012-05-30' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'11' AS [TotalContacts], N'12338' AS [SIH], N'10' AS [SLEligible], N'10' AS [InServiceLevel] UNION ALLSELECT N'2012-05-31' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'715' AS [TotalContacts], N'15827720' AS [SIH], N'712' AS [SLEligible], N'700' AS [InServiceLevel] UNION ALLSELECT N'2012-05-31' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'189' AS [TotalContacts], N'5195575' AS [SIH], N'119' AS [SLEligible], N'88' AS [InServiceLevel] UNION ALLSELECT N'2012-05-31' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'122' AS [TotalContacts], N'3431575' AS [SIH], N'103' AS [SLEligible], N'99' AS [InServiceLevel] UNION ALLSELECT N'2012-05-31' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'26' AS [TotalContacts], N'137101' AS [SIH], N'19' AS [SLEligible], N'17' AS [InServiceLevel] UNION ALLSELECT N'2012-06-01' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'401' AS [TotalContacts], N'3127629' AS [SIH], N'387' AS [SLEligible], N'385' AS [InServiceLevel] UNION ALLSELECT N'2012-06-01' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'156' AS [TotalContacts], N'4531080' AS [SIH], N'94' AS [SLEligible], N'81' AS [InServiceLevel] UNION ALLSELECT N'2012-06-01' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'76' AS [TotalContacts], N'2309846' AS [SIH], N'64' AS [SLEligible], N'54' AS [InServiceLevel] UNION ALLSELECT N'2012-06-01' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'34' AS [TotalContacts], N'178935' AS [SIH], N'33' AS [SLEligible], N'33' AS [InServiceLevel] UNION ALLSELECT N'2012-06-02' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'57' AS [TotalContacts], N'116675' AS [SIH], N'55' AS [SLEligible], N'55' AS [InServiceLevel] UNION ALLSELECT N'2012-06-02' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'58' AS [TotalContacts], N'1551854' AS [SIH], N'9' AS [SLEligible], N'6' AS [InServiceLevel] UNION ALLSELECT N'2012-06-02' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'37' AS [TotalContacts], N'1725412' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-06-02' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'1' AS [TotalContacts], N'1302' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-06-03' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'2' AS [TotalContacts], N'12855' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-06-03' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'50' AS [TotalContacts], N'1226539' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-06-03' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'35' AS [TotalContacts], N'1676243' AS [SIH], N'0' AS [SLEligible], N'0' AS [InServiceLevel] UNION ALLSELECT N'2012-06-04' AS [WorkDate], N'BCE' AS [BusinessUnit], N'E' AS [ContactType], N'467' AS [TotalContacts], N'2880005' AS [SIH], N'465' AS [SLEligible], N'453' AS [InServiceLevel] UNION ALLSELECT N'2012-06-04' AS [WorkDate], N'C' AS [BusinessUnit], N'E' AS [ContactType], N'142' AS [TotalContacts], N'4508731' AS [SIH], N'94' AS [SLEligible], N'85' AS [InServiceLevel] UNION ALLSELECT N'2012-06-04' AS [WorkDate], N'M' AS [BusinessUnit], N'E' AS [ContactType], N'120' AS [TotalContacts], N'2511312' AS [SIH], N'116' AS [SLEligible], N'96' AS [InServiceLevel] UNION ALLSELECT N'2012-06-04' AS [WorkDate], N'SLP' AS [BusinessUnit], N'E' AS [ContactType], N'41' AS [TotalContacts], N'200880' AS [SIH], N'31' AS [SLEligible], N'31' AS [InServiceLevel] |
|
|
|
|
|