May 8, 2013 at 1:34 pm
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
May 8, 2013 at 2:12 pm
Do you have any sample data for this?
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
May 8, 2013 at 6:15 pm
I certainly could. What would be the best way to do so? As an attachment?
May 8, 2013 at 6:18 pm
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/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
May 8, 2013 at 7:04 pm
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]
May 8, 2013 at 9:10 pm
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
May 8, 2013 at 9:24 pm
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
May 9, 2013 at 5:10 am
-- 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!!!
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
May 9, 2013 at 6:26 am
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
May 9, 2013 at 6:31 am
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.
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
May 9, 2013 at 8:23 am
So if I don't want to alter the contents of the SLHIH table directly, do I need to put the results of the query into a temp table and report off of it? Or is there a way to do this where i just get the results of the query to report from, without altering the contents of the SLHIH table?
PK
May 9, 2013 at 8:29 am
paul.j.kemna (5/9/2013)
So if I don't want to alter the contents of the SLHIH table directly, do I need to put the results of the query into a temp table and report off of it? Or is there a way to do this where i just get the results of the query to report from, without altering the contents of the SLHIH table?PK
Ah, I see - then simply remove the UPDATE query. The one above it is the one you want. I'd strongly recommend that you play with all of them to see what they do.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply