SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Major Challenge - Too Hard to Summarize


Major Challenge - Too Hard to Summarize

Author
Message
paul.j.kemna
paul.j.kemna
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 155
I have two tables.

Table #1

CREATE 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 #2

CREATE 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
Calibear
Calibear
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 132
Do you have any sample data for this?

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
paul.j.kemna
paul.j.kemna
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 155
I certainly could. What would be the best way to do so? As an attachment?
Calibear
Calibear
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 132
Check here under The Correct Way to Post Data: http://www.sqlservercentral.com/articles/Best+Practices/61537/

We really don't need that much. Ten rows of data per table should be enough.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
paul.j.kemna
paul.j.kemna
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 155
Ok, this is more than 10 records, but I wanted to be sure to cross a weekend and a holiday. I hope this is what you had in mind.


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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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 ALL
SELECT 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]



mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1594 Visits: 3317
Hi

This is ugly, but it seems to work ... I think.
There is bound to be a more elegant and efficient way to do this:-)
WITH tryThis as (
SELECT s.workdate, s.businessunit
,s.totalcontacts, s.SIH, s.SLEligible, s.InServiceLevel
,h.workingdate
,row_number() OVER (partition by h.businessunit order by calendardate) N1
,row_number() OVER (partition by h.businessunit, workingdate order by calendardate) N2
FROM #SLHIH s
INNER JOIN #HoursOfOperation h ON h.CalendarDate = s.workdate and h.businessunit = s.businessunit
)
SELECT a.WorkDate, a.BusinessUnit,
a.totalcontacts, b.totalcontacts,
a.SIH, b.SIH,
a.SLEligible, b.SLEligible,
a.InServiceLevel, b.InServiceLevel
--UPDATE a
--SET a.totalcontacts = b.totalcontacts,
-- a.SIH = b.SIH,
-- a.SLEligible = b.SLEligible,
-- a.InServiceLevel = b.InServiceLevel
FROM tryThis a
INNER JOIN tryThis b ON b.N2 = a.N1 - a.N2 and a.workingdate = 0 and b.workingdate = 1 and a.businessunit = b.businessunit
ORDER BY a.BusinessUnit, a.WorkDate


Bevan Keighley
Bevan Keighley
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 834
Here's another option....
I've listed all the fields so you can confirm it is working. You can decide which fields to use.

Regards,
Bevan




with cte
as
(
select ho.[WorkingDate], sl.[WorkDate] , sl.[BusinessUnit], sl.[ContactType], sl.[TotalContacts], sl.[SIH], sl.[SLEligible], sl.[InServiceLevel]
from #SLHIH as sl
inner join #HoursOfOperation as ho
on sl.WorkDate = ho.CalendarDate and sl.BusinessUnit = ho.BusinessUnit
)
select a.* , max_previous_work_date.maxDate, most_recent_previous_work_day.*
from cte as a
outer apply (select max(WorkDate) as maxDate from cte as b where b.WorkDate <= a.WorkDate and b.BusinessUnit = a.BusinessUnit and b.WorkingDate=1) as max_previous_work_date
outer apply (select * from cte as c where c.WorkDate = max_previous_work_date.maxDate and c.BusinessUnit = a.BusinessUnit) as most_recent_previous_work_day



ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16396 Visits: 19554

-- Have a look at the result of this:
-- ('#' is an arbitrary divider between the columns of one table and the columns of another)
SELECT h.*, '#' '#', x.*
FROM HoursOfOperation h
OUTER APPLY (
SELECT TOP 1 hi.CalendarDate, hi.WorkingDate
FROM HoursOfOperation hi
WHERE hi.BusinessUnit = h.BusinessUnit
AND hi.CalendarDate < h.CalendarDate
AND hi.WorkingDate = 1
AND h.WorkingDate = 0
ORDER BY hi.CalendarDate DESC
) x
ORDER BY h.BusinessUnit, h.CalendarDate;

---------------------------------------------------------
-- Here's how to apply it.
---------------------------------------------------------
-- Check that everything looks ok:
WITH JoinTable AS (
SELECT h.BusinessUnit, h.CalendarDate, SwitchDate = x.CalendarDate
FROM HoursOfOperation h
CROSS APPLY (
SELECT TOP 1 hi.CalendarDate
FROM HoursOfOperation hi
WHERE hi.BusinessUnit = h.BusinessUnit
AND hi.CalendarDate < h.CalendarDate
AND hi.WorkingDate = 1
AND h.WorkingDate = 0
ORDER BY hi.CalendarDate DESC
) x
)
SELECT t.*, '#' '#', j.*, '#' '#', s.*
FROM SLHIH t
LEFT JOIN JoinTable j
ON j.BusinessUnit = t.BusinessUnit
AND j.CalendarDate = t.WorkDate
LEFT JOIN SLHIH s
ON s.BusinessUnit = j.BusinessUnit
AND s.WorkDate = j.SwitchDate
ORDER BY t.BusinessUnit, t.WorkDate;

-- Screw things down a bit and check again:
WITH JoinTable AS (
SELECT h.BusinessUnit, h.CalendarDate, SwitchDate = x.CalendarDate
FROM HoursOfOperation h
CROSS APPLY (
SELECT TOP 1 hi.CalendarDate
FROM HoursOfOperation hi
WHERE hi.BusinessUnit = h.BusinessUnit
AND hi.CalendarDate < h.CalendarDate
AND hi.WorkingDate = 1
AND h.WorkingDate = 0
ORDER BY hi.CalendarDate DESC
) x
)

SELECT t.*, '#' '#', j.*, '#' '#', s.*
FROM SLHIH t
INNER JOIN JoinTable j
ON j.BusinessUnit = t.BusinessUnit
AND j.CalendarDate = t.WorkDate
INNER JOIN SLHIH s
ON s.BusinessUnit = j.BusinessUnit
AND s.WorkDate = j.SwitchDate
ORDER BY t.BusinessUnit, t.WorkDate;

-- Convert to an UPDATE:
WITH JoinTable AS (
SELECT h.BusinessUnit, h.CalendarDate, SwitchDate = x.CalendarDate
FROM HoursOfOperation h
CROSS APPLY (
SELECT TOP 1 hi.CalendarDate
FROM HoursOfOperation hi
WHERE hi.BusinessUnit = h.BusinessUnit
AND hi.CalendarDate < h.CalendarDate
AND hi.WorkingDate = 1
AND h.WorkingDate = 0
ORDER BY hi.CalendarDate DESC
) x
)

UPDATE t SET
TotalContacts = s.TotalContacts,
SIH = s.SIH,
SLEligible = s.SLEligible,
InServiceLevel = s.InServiceLevel
FROM SLHIH t
INNER JOIN JoinTable j
ON j.BusinessUnit = t.BusinessUnit
AND j.CalendarDate = t.WorkDate
INNER JOIN SLHIH s
ON s.BusinessUnit = j.BusinessUnit
AND s.WorkDate = j.SwitchDate;

-- with even basic indexing in place, it's pretty quick
-- CREATE UNIQUE CLUSTERED INDEX ucx_BusinessUnit_CalendarDate ON HoursOfOperation (BusinessUnit, CalendarDate)
-- CREATE UNIQUE CLUSTERED INDEX ucx_BusinessUnit_WorkDate ON SLHIH (BusinessUnit, WorkDate)
-- Don't use these!!!




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
paul.j.kemna
paul.j.kemna
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 155
I am still walking through these (have to work on something else this morning) but ChrisM, does this UPDATE actually update the SLHIH table, or does it just update the result set "on the fly"?

I really don't want to alter the table.

Thanks everyone for the input!!

PK
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16396 Visits: 19554
paul.j.kemna (5/9/2013)
I am still walking through these (have to work on something else this morning) but ChrisM, does this UPDATE actually update the SLHIH table, or does it just update the result set "on the fly"?

I really don't want to alter the table.

Thanks everyone for the input!!

PK


It updates the SLHIH table. Try it with your #temp table setup.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search