April 16, 2010 at 3:33 pm
I would look at it the other way around:
I would update the columns that failed based on the validity check of the "previous" permits to a value other than 0 and 1.
Then I would set all other processed rows to 1 in each column that would match my update cirteria (PrevPermPrefX NOT IN ('P', 'H','') AND ConversionStatusPrevX IS NULL).
This might be easier. The downside of that approach is that you're using the same logicin two places in your code (insert and update). Both have to be consistent. If not, you'd update wrong columns...
April 16, 2010 at 3:49 pm
Lutz, before I read your last post, I updated the Update statement for the previous permits, this will do the successful inserts, as well as the failed inserts all in the same shot. I believe this works as I've been checking the data to make certain.
By the way, this proc (which includes EVERYTHING from my crappy original) takes only 2 minutes and 34 seconds to run!!!!!
Use IARTS
Go
/* -- test setup to reset tables
UPDATE PWDPermitConversionData2
SET conversionstatus=0,
conversionstatusprev1 = null,
conversionstatusprev2 = null,
conversionstatusprev3 = null,
conversionstatusprev4 = null,
conversionstatusprev5 = null,
conversionstatusprev6 = null,
conversionstatusprev7 = null,
conversionstatusprev8 = null,
conversionstatusprev9 = null,
conversionstatusprev10 = null
FROM PWDPermitConversionData2
TRUNCATE TABLE PWDPermit2
TRUNCATE TABLE PWDPermitConversionErrors2
*/
DECLARE
@User VARCHAR(8),
@True BIT,
@False BIT,
@PermitType_Org INT,
@PermitType_Ind INT,
@PermitType_Temp INT,
@PermitType_Sticker INT,
@PermitStatus_RU INT,
@PermitDeviceType_Placard INT,
@PermitDeviceType_Sticker INT,
@PermitDeviceType_Unknown INT,
@Today DATETIME,
@MinDate DATETIME,
@MaxDate DATETIME,
@DateMinus1Year DATETIME
SET @User = 'PWDConv'
SET @True = 0x1
SET @False = 0x0
SET @Today = GETDATE()
SET @MinDate = CONVERT(DATETIME, '1901-01-01')
SET @MaxDate = CONVERT(DATETIME, '2100-01-01')
SET @DateMinus1Year = DATEADD(yy, -1, @Today)
SELECT @PermitType_Org = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Organization'
SELECT @PermitType_Ind = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Permanent'
SELECT @PermitType_Temp = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Temporary'
SELECT @PermitType_Sticker = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Sticker'
SELECT @PermitStatus_RU = PWDPermitStatusID From IARTS..PWDPermitStatus Where LookupValue = 'RtrndUnDel'
SELECT @PermitDeviceType_Placard = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Placard'
SELECT @PermitDeviceType_Sticker = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Sticker'
SELECT @PermitDeviceType_Unknown = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Unknown'
--------------------------------------------------------------------------------------------------------
-- Create the table that will be used to track what was actually inserted
--------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PermitsInserted]') AND type in (N'U'))
DROP TABLE [dbo].[PermitsInserted]
Create Table IARTS..PermitsInserted
(
[PWDPermitConversionDataID] Int Not Null,
[RecordIndicator] Int Not Null
)
--------------------------------------------------------------------------------------------------------
-- build intermeditate table to hold invalid values
--------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#inv_PWDConvData') IS NOT NULL
DROP TABLE #inv_PWDConvData
SELECT0 AS PWDPermitConversionDataID,
0 AS [RecordIndicator],
0 AS errorid,
CAST('' AS VARCHAR(MAX)) AS ErrorMessage
INTO #inv_PWDConvData
WHERE 1=2
--------------------------------------------------------------------------------------------------------
-- populate intermeditate table
--------------------------------------------------------------------------------------------------------
INSERT INTO #inv_PWDConvData
SELECT PWDPermitConversionDataID,
0 AS [RecordIndicator],
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' Or [PermitNumber] = ''THEN 5000
WHEN [PermitPrefix] NOT IN ('D','M','V','S')THEN 5001--'','P', 'H') THEN 5001
WHEN ([Status] NOT IN('RP','01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99', NULL) AND [Status] LIKE '%[^0-9]%') THEN 5002
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0)THEN 5003
ELSE 5004END AS errorid,
CASE
WHEN [PermitNumber] LIKE '%[^0-9]%' Or [PermitNumber] = ''THEN 'non-numeric [PermitNumber]'
WHEN [PermitPrefix] NOT IN ('D','M','V','S')THEN 'invalid [PermitPrefix]'--,'','P', 'H') THEN 'invalid [PermitPrefix]'
WHEN ([Status] NOT IN('RP','01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99', NULL) AND [Status] LIKE '%[^0-9]%') THEN 'invalid [Status]'
WHEN (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0)THEN 'invalid [ExpirationDate]'
ELSE 'unknown error'END AS ErrorMessage
FROMPWDPermitConversionData2
WHEREConversionStatus = 0
And
(([PermitNumber] LIKE '%[^0-9]%' Or [PermitNumber] = '')--select non-numeric values
OR [PermitPrefix] NOT IN ('D','M','V','S','P', 'H') -- invalid prefix
OR ([Status] NOT IN('RP','01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') AND [Status] LIKE '%[^0-9]%') -- invalid status
OR (ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) = 0) -- invalid ExpirationDate
)
--------------------------------------------------------------------------------------------------------
-- Insert rows with failed test
--------------------------------------------------------------------------------------------------------
INSERT INTO PWDPermitConversionErrors2
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
SELECTPWDPermitConversionDataID,
0,
errorid,
ErrorMessage
FROM#inv_PWDConvData
--------------------------------------------------------------------------------------------------------
-- Insert the primary record data inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
INSERT INTO PWDPermit2
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate],
[PWDPermitConversionDataID],
[RecordIndicator]
)
OUTPUT
INSERTED.PWDPermitConversionDataID,
INSERTED.RecordIndicator
INTO IARTS..PermitsInserted
SELECTCASE WHEN PermitNumber = '' THEN NULL
ELSE CAST(PermitNumber AS INT)
END,
CustomerNumber,
CASE PermitPrefix
WHEN 'D' THEN @PermitType_Org
WHEN 'M' THEN @PermitType_Ind
WHEN 'V' THEN @PermitType_Temp
WHEN 'S' THEN @PermitType_Sticker
ELSE PermitPrefix -- This will make it fail for sure
END,
CASE [Status]
WHEN 'RP' THEN @PermitStatus_RU
ELSE CAST([Status] AS INT)
END,
CASE WHEN PermitPrefix = 'S' THEN @PermitDeviceType_Sticker
WHEN PermitPrefix IN ('D', 'M', 'V') THEN @PermitDeviceType_Placard
ELSE @PermitDeviceType_Unknown
END,
CASE WHEN Location = '' OR Location IS NULL THEN '999'
ELSE Location
END,
CASE WHEN HCExam = '' OR HCExam IS NULL THEN '999'
ELSE HCExam
END,
CASE WHEN HCStation = '' OR HCStation IS NULL THEN '99'
ELSE HCStation
END,
CAST(IssueDate AS DATETIME),
CASE WHEN ExpirationDate = 'NONE' OR ExpirationDate = '' OR ExpirationDate = 'NOE' THEN @MaxDate
ELSE CAST(ExpirationDate AS DATETIME)
END,
CASE WHEN InvalidDLNumber = 'Y' THEN @True
ELSE @False
END,
@False,
@User,
@Today,
PWDPermitConversionDataID,
0
FROM PWDPermitConversionData2
WHERENOT EXISTS
(
SELECT 1
FROM #inv_PWDConvData
WHERE #inv_PWDConvData.PWDPermitConversionDataID = PWDPermitConversionData2.PWDPermitConversionDataID
)
AND CustomerNumber > 0 -- which also cover NOT NULL
AND ConversionStatus = 0
AND PermitPrefix NOT IN ('P', 'H')
AND [Status] <> '06'
AND
(
(
(
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END IS NULL
OR
CASE
WHEN ExpirationDate <> 'NONE' AND ExpirationDate <> 'NOE' AND ExpirationDate <> ''
THEN CONVERT(DATETIME, ExpirationDate)
ELSE @MaxDate
END > @DateMinus1Year
)
AND PermitPrefix <> 'M'
)
OR
(
PermitPrefix = 'M'
)
)
End Try
Begin Catch
Print ''
Print 'Insert failed'
Print '----------------------------------------'
Print Cast(Error_Message() as varchar(max))
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the primary records
--------------------------------------------------------------------------------------------------------
UPDATE PWDPermitConversionData2
SETConversionStatus = ISNULL(i.[PWDPermitConversionDataID], -1),
ConversionErrorMessage = i.[ErrorMessage]
FROM PWDPermitConversionData2 p
Inner JOIN #inv_PWDConvData i ON p.[PWDPermitConversionDataID] = i.[PWDPermitConversionDataID]
Wherep.ConversionStatus = 0
UpdatePWDPermitConversionData2
SetConversionStatus = 1
FromPWDPermitConversionData2 p
Inner Join PermitsInserted [pi] On [pi].PWDPermitConversionDataID = p.PWDPermitConversionDataID
And [pi].RecordIndicator = 0
Wherep.ConversionStatus = 0
--------------------------------------------------------------------------------------------------------
-- Insert the previous permits
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
-- build intermeditate table to hold invalid values
--------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#inv_PrevPWDConvData') IS NOT NULL
DROP TABLE #inv_PrevPWDConvData
SELECT0 AS PWDPermitConversionDataID,
0 AS [RecordIndicator],
0 AS errorid,
CAST('' AS VARCHAR(MAX)) AS ErrorMessage
INTO #inv_PrevPWDConvData
WHERE 1=2
Insert Into #inv_PrevPWDConvData
SelectPWDPermitConversionDataID,
RecordIndicator,
Case
When PrevPermNum Like '%[^0-9]%' Or PrevPermNum = ''Then 5000
When PrevPermPref Not In ('M', 'V', 'S', '', Null)Then 5001
When (PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')Then 5002
When (PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)Then 5003
Else 5004
EndAs [ErrorID],
Case
When PrevPermNum Like '%[^0-9]%' Or PrevPermNum = ''Then 'non-numeric [PermitNumber]'
When PrevPermPref Not In ('M', 'V', 'S', '', Null)Then 'invalid [PermitPrefix]'
When (PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')Then 'invalid [Status]'
When (PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)Then 'invalid [ExpirationDate]'
Else 'unknown error'
EndAs [ErrorMessage]
From
(
SelectPWDPermitConversionDataID,
CustomerNumber,
n As [RecordIndicator],
Casen
When 1Then PrevPermPref1
When 2Then PrevPermPref2
When 3Then PrevPermPref3
When 4Then PrevPermPref4
When 5Then PrevPermPref5
When 6Then PrevPermPref6
When 7Then PrevPermPref7
When 8Then PrevPermPref8
When 9Then PrevPermPref9
When 10Then PrevPermPref10
EndAs [PrevPermPref],
Casen
When 1Then PrevPermNum1
When 2Then PrevPermNum2
When 3Then PrevPermNum3
When 4Then PrevPermNum4
When 5Then PrevPermNum5
When 6Then PrevPermNum6
When 7Then PrevPermNum7
When 8Then PrevPermNum8
When 9Then PrevPermNum9
When 10Then PrevPermNum10
EndAs [PrevPermNum],
Casen
When 1Then PrevPermStatus1
When 2Then PrevPermStatus2
When 3Then PrevPermStatus3
When 4Then PrevPermStatus4
When 5Then PrevPermStatus5
When 6Then PrevPermStatus6
When 7Then PrevPermStatus7
When 8Then PrevPermStatus8
When 9Then PrevPermStatus9
When 10Then PrevPermStatus10
EndAs [PrevPermStatus],
Casen
When 1Then PrevPermReturn1
When 2Then PrevPermReturn2
When 3Then PrevPermReturn3
When 4Then PrevPermReturn4
When 5Then PrevPermReturn5
When 6Then PrevPermReturn6
When 7Then PrevPermReturn7
When 8Then PrevPermReturn8
When 9Then PrevPermReturn9
When 10Then PrevPermReturn10
EndAs [PrevPermReturn],
Casen
When 1Then PrevPermExpiration1
When 2Then PrevPermExpiration2
When 3Then PrevPermExpiration3
When 4Then PrevPermExpiration4
When 5Then PrevPermExpiration5
When 6Then PrevPermExpiration6
When 7Then PrevPermExpiration7
When 8Then PrevPermExpiration8
When 9Then PrevPermExpiration9
When 10Then PrevPermExpiration10
EndAs [PrevPermExpiration],
Casen
When 1Then ConversionStatusPrev1
When 2Then ConversionStatusPrev2
When 3Then ConversionStatusPrev3
When 4Then ConversionStatusPrev4
When 5Then ConversionStatusPrev5
When 6Then ConversionStatusPrev6
When 7Then ConversionStatusPrev7
When 8Then ConversionStatusPrev8
When 9Then ConversionStatusPrev9
When 10Then ConversionStatusPrev10
EndAs [ConversionStatusPrev]
FromPWDPermitConversionData2 p
Cross Apply
(
Selectnumber As [n]From master..spt_values Where [Type] = 'P' And number > 0 And number < 11
) tally
Wherep.CustomerNumber > 0
Andp.PermitPrefix = 'M'
)cte
WhereLen(PrevPermNum) > 0
And(ConversionStatusPrev IS NULL Or ConversionStatusPrev = 0)
And
(
PrevPermNum Like '%[^0-9]%'
OrPrevPermPref Not In ('M', 'V', 'S', '', Null)
Or(PrevPermStatus Not In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99') And PrevPermStatus Like '%[^0-9]%')
Or(PrevPermExpiration Not In ('NONE', '', 'NOE') And IsDate(PrevPermExpiration) = 0)
)
--------------------------------------------------------------------------------------------------------
-- Insert rows with failed test
--------------------------------------------------------------------------------------------------------
Insert Into IARTS..PWDPermitConversionErrors2
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
SelectPWDPermitConversionDataID,
RecordIndicator,
ErrorID,
ErrorMessage
From#inv_PrevPWDConvData
--------------------------------------------------------------------------------------------------------
-- Insert the primary record data inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
Insert Into IARTS..PWDPermit2
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate],
[PWDPermitConversionDataID],
[RecordIndicator]
)
Output
Inserted.PWDPermitConversionDataID,
Inserted.RecordIndicator
Into IARTS..PermitsInserted
SelectCast(PrevPermNum as int),
CustomerNumber,
CasePrevPermPref
When 'M'Then @PermitType_Ind
When 'V'Then @PermitType_Temp
When 'S'Then @PermitType_Sticker
Else Null -- To create an error
End,
CasePrevPermStatus
When 'RP'Then @PermitStatus_RU
Else Cast(PrevPermStatus as int)
End,
Case
When PrevPermPref = 'M' Or PrevPermPref = 'V'Then @PermitDeviceType_Placard
When PrevPermPref = 'S'Then @PermitDeviceType_Sticker
When PrevPermPref = 'D'Then Null -- To create an error because we shouldn't be converting orgs
Else @PermitDeviceType_Unknown
End,
'999',
'999',
'99',
Case
When PrevPermExpiration = 'NONE' Or PrevPermExpiration = '' Or PrevPermExpiration = 'NOE'Then @MinDate
When Cast(PrevPermExpiration as DateTime) > @TodayThen DateAdd(mm, -1, @Today)
Else Cast(PrevPermExpiration as DateTime)
End,
Case
When PrevPermExpiration = 'NONE' Or PrevPermExpiration = '' Or PrevPermExpiration = 'NOE'Then @MaxDate
Else Cast(PrevPermExpiration as DateTime)
End,
@False,
CasePrevPermReturn
When 'Y'Then @True
Else @False
End,
@User,
@Today,
PWDPermitConversionDataID,
RecordIndicator
From
(
SelectPWDPermitConversionDataID,
CustomerNumber,
n As [RecordIndicator],
Casen
When 1Then PrevPermPref1
When 2Then PrevPermPref2
When 3Then PrevPermPref3
When 4Then PrevPermPref4
When 5Then PrevPermPref5
When 6Then PrevPermPref6
When 7Then PrevPermPref7
When 8Then PrevPermPref8
When 9Then PrevPermPref9
When 10Then PrevPermPref10
EndAs [PrevPermPref],
Casen
When 1Then PrevPermNum1
When 2Then PrevPermNum2
When 3Then PrevPermNum3
When 4Then PrevPermNum4
When 5Then PrevPermNum5
When 6Then PrevPermNum6
When 7Then PrevPermNum7
When 8Then PrevPermNum8
When 9Then PrevPermNum9
When 10Then PrevPermNum10
EndAs [PrevPermNum],
Casen
When 1Then PrevPermStatus1
When 2Then PrevPermStatus2
When 3Then PrevPermStatus3
When 4Then PrevPermStatus4
When 5Then PrevPermStatus5
When 6Then PrevPermStatus6
When 7Then PrevPermStatus7
When 8Then PrevPermStatus8
When 9Then PrevPermStatus9
When 10Then PrevPermStatus10
EndAs [PrevPermStatus],
Casen
When 1Then PrevPermReturn1
When 2Then PrevPermReturn2
When 3Then PrevPermReturn3
When 4Then PrevPermReturn4
When 5Then PrevPermReturn5
When 6Then PrevPermReturn6
When 7Then PrevPermReturn7
When 8Then PrevPermReturn8
When 9Then PrevPermReturn9
When 10Then PrevPermReturn10
EndAs [PrevPermReturn],
Casen
When 1Then PrevPermExpiration1
When 2Then PrevPermExpiration2
When 3Then PrevPermExpiration3
When 4Then PrevPermExpiration4
When 5Then PrevPermExpiration5
When 6Then PrevPermExpiration6
When 7Then PrevPermExpiration7
When 8Then PrevPermExpiration8
When 9Then PrevPermExpiration9
When 10Then PrevPermExpiration10
EndAs [PrevPermExpiration],
Casen
When 1Then ConversionStatusPrev1
When 2Then ConversionStatusPrev2
When 3Then ConversionStatusPrev3
When 4Then ConversionStatusPrev4
When 5Then ConversionStatusPrev5
When 6Then ConversionStatusPrev6
When 7Then ConversionStatusPrev7
When 8Then ConversionStatusPrev8
When 9Then ConversionStatusPrev9
When 10Then ConversionStatusPrev10
EndAs [ConversionStatusPrev]
FromPWDPermitConversionData2 p
Cross Apply
(
Selectnumber As [n]From master..spt_values Where [Type] = 'P' And number > 0 And number < 11
) tally
Wherep.CustomerNumber > 0
Andp.PermitPrefix = 'M'
)cte
WhereNot Exists
(
Select1
From#inv_PrevPWDConvData i
Wherei.PWDPermitConversionDataID = cte.PWDPermitConversionDataID
)
AndCustomerNumber > 0
And(ConversionStatusPrev IS NULL Or ConversionStatusPrev = 0)
AndLen(PrevPermNum) > 0
AndPrevPermPref Not In ('P', 'H')
AndPrevPermPref In ('M', 'V', 'S')
AndPrevPermStatus In ('RP', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '98', '99')
And(PrevPermExpiration In ('NONE', '', 'NOE') Or IsDate(PrevPermExpiration) = 1)
End Try
Begin Catch
Print ''
Print 'Insert failed'
Print '----------------------------------------'
Print Cast(Error_Message() as varchar(max))
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the previous permit
--------------------------------------------------------------------------------------------------------
--UpdateIARTS..PWDPermitConversionData2
--Set
--ConversionStatusPrev1 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 1) = 1 Then 1 Else Null End,
--ConversionStatusPrev2 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 2) = 1 Then 1 Else Null End,
--ConversionStatusPrev3 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 3) = 1 Then 1 Else Null End,
--ConversionStatusPrev4 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 4) = 1 Then 1 Else Null End,
--ConversionStatusPrev5 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 5) = 1 Then 1 Else Null End,
--ConversionStatusPrev6 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 6) = 1 Then 1 Else Null End,
--ConversionStatusPrev7 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 7) = 1 Then 1 Else Null End,
--ConversionStatusPrev8 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 8) = 1 Then 1 Else Null End,
--ConversionStatusPrev9 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 9) = 1 Then 1 Else Null End,
--ConversionStatusPrev10 = Case When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 10) = 1 Then 1 Else Null End
--From IARTS..PWDPermitConversionData2 p
UpdateIARTS..PWDPermitConversionData2
Set
ConversionStatusPrev1 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 1) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 1) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 1)
Else Null
End,
ConversionErrorMessagePrev1 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 1) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 1)
Else Null
End,
ConversionStatusPrev2 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 2) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 2) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 2)
Else Null
End,
ConversionErrorMessagePrev2 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 2) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 2)
Else Null
End,
ConversionStatusPrev3 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 3) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 3) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 3)
Else Null
End,
ConversionErrorMessagePrev3 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 3) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 3)
Else Null
End,
ConversionStatusPrev4 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 4) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 4) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 4)
Else Null
End,
ConversionErrorMessagePrev4 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 4) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 4)
Else Null
End,
ConversionStatusPrev5 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 5) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 5) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 5)
Else Null
End,
ConversionErrorMessagePrev5 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 5) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 5)
Else Null
End,
ConversionStatusPrev6 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 6) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 6) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 6)
Else Null
End,
ConversionErrorMessagePrev6 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 6) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 6)
Else Null
End,
ConversionStatusPrev7 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 7) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 7) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 7)
Else Null
End,
ConversionErrorMessagePrev7 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 7) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 7)
Else Null
End,
ConversionStatusPrev8 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 8) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 8) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 8)
Else Null
End,
ConversionErrorMessagePrev8 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 8) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 8)
Else Null
End,
ConversionStatusPrev9 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 9) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 9) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 9)
Else Null
End,
ConversionErrorMessagePrev9 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 9) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 9)
Else Null
End,
ConversionStatusPrev10 =
Case
When (Select 1 From PermitsInserted p2 Where p2.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p2.RecordIndicator = 10) = 1 Then 1
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 10) = 1
Then (Select ErrorID From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 10)
Else Null
End,
ConversionErrorMessagePrev10 =
Case
When (Select 1 From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 10) = 1
Then (Select ErrorMessage From #inv_PrevPWDConvData p3 Where p3.PWDPermitConversionDataID = p.PWDPermitConversionDataID And p3.RecordIndicator = 10)
Else Null
End
FromIARTS..PWDPermitConversionData2 p
Drop Table #inv_PWDConvData
Drop Table #inv_PrevPWDConvData
Drop Table IARTS..PermitsInserted
April 16, 2010 at 5:44 pm
gregory.anderson (4/16/2010)
Lutz, before I read your last post, I updated the Update statement for the previous permits, this will do the successful inserts, as well as the failed inserts all in the same shot. I believe this works as I've been checking the data to make certain.By the way, this proc (which includes EVERYTHING from my crappy original) takes only 2 minutes and 34 seconds to run!!!!!
First: your original code was not "crappy", just not performing well enough 😉
Second: it seems like the only thing that was missing was some sample code and "hints" to present you a set-based solution. Based on that you did an overtopping job to adopt the concept. I've never seen somebody absorbing and implementing new stuff that fast!! Outstanding job, again!!
Finally: Almost one third of the processing time is used to do the final step, mostly because of the repetitive select statements. Let's see if I can find an alternative solution over the weekend. I think it's possible to bring it down from the 2.5 minutes to a one minute range. We'll see...
Side note: Are we talking about an improvement from 41hrs down to just a few minutes (so far)? Just being curious... Never participated in making such a huge step before....
April 16, 2010 at 6:25 pm
lmu92 (4/16/2010)
gregory.anderson (4/16/2010)
Lutz, before I read your last post, I updated the Update statement for the previous permits, this will do the successful inserts, as well as the failed inserts all in the same shot. I believe this works as I've been checking the data to make certain.By the way, this proc (which includes EVERYTHING from my crappy original) takes only 2 minutes and 34 seconds to run!!!!!
First: your original code was not "crappy", just not performing well enough 😉
Agreed
Second: it seems like the only thing that was missing was some sample code and "hints" to present you a set-based solution. Based on that you did an overtopping job to adopt the concept. I've never seen somebody absorbing and implementing new stuff that fast!! Outstanding job, again!!
Absolutely agree. It's seeing people like you jump on these "new-fangled" things, start using them, and seeing how great they are that will motivate many of us. And if Jeff is lurking out there, I'm sure he'll agree!
Finally: Almost one third of the processing time is used to do the final step, mostly because of the repetitive select statements. Let's see if I can find an alternative solution over the weekend. I think it's possible to bring it down from the 2.5 minutes to a one minute range. We'll see...
Side note: Are we talking about an improvement from 41hrs down to just a few minutes (so far)? Just being curious... Never participated in making such a huge step before....
So, it's still taking 2:34 to run? Geez, that seems really slow for < 1 mil records. Let's get it to run in under 0:45! 😉 :w00t:
@gregory: BTW, the subject of this thread is "How to tune/speed up a procedure". Think you know a few ways now?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 17, 2010 at 6:55 am
Ok, here's what I came up with:
I'm using a CTE again. The first one ("cte") is used to pivot the failed ConversionDataID's.
The second CTE ("cte_ri") pivot the inserted ConversionDataID's and defines the update value for inserted data ("1") at the same time.
Finally, I join those two subresults to the staging table. I provided just the first 3 out of the 10 prevRecords, because I know you get the concept based on the sample. 😉
Thinking about it, you might not even need to do the status update for the primary records as a separate run. If you'd include it in the code below we should not need to do it separately. So we do the status update once we're done, touching the staging table only once.
And te very best solution would be not to deal with primary records and previous permits in separate statements. If you look at the two code blocks you'll find that both are very similar. If you'd query master..spt_values for number >= 0 And number < 11, you could use the Zero value to assign the primary record columns.
You also might think about to insert error rows directly into PWDPermitConversionErrors instead of #inv_PWDConvData.
Then your code would consist of one statement to find and insert errors into PWDPermitConversionErrors , one to add the valid rows to PWDPermit and one to update the status fields in PWDPermitConversionData. Let's see how long that code will take to run...
@Wayne: we're getting closer to the 00:45 you've been talking about 😀
We probably will even be able to beat it!
;WITH cte AS
(
SELECT
PWDPermitConversionDataID,
MAX(CASE WHEN Recordindicator =1 THEN errorid ELSE NULL END) AS ConversionStatusPrev1,
MAX(CASE WHEN Recordindicator =1 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev1,
MAX(CASE WHEN Recordindicator =2 THEN errorid ELSE NULL END) AS ConversionStatusPrev2,
MAX(CASE WHEN Recordindicator =2 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev2,
MAX(CASE WHEN Recordindicator =3 THEN errorid ELSE NULL END) AS ConversionStatusPrev3,
MAX(CASE WHEN Recordindicator =3 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev3
FROM #inv_PrevPWDConvData
GROUP BY PWDPermitConversionDataID
),
cte_ri AS
(
SELECT
PWDPermitConversionDataID,
MAX(CASE WHEN Recordindicator =1 THEN 1 ELSE NULL END) AS ri1,
MAX(CASE WHEN Recordindicator =2 THEN 1 ELSE NULL END) AS ri2,
MAX(CASE WHEN Recordindicator =3 THEN 1 ELSE NULL END) AS ri3
FROM PermitsInserted
WHERE Recordindicator>0
GROUP BY PWDPermitConversionDataID
)
SELECT
p.PWDPermitConversionDataID,
COALESCE(cte.ConversionStatusPrev1,i.ri1) AS ConversionStatusPrev1,
cte.ConversionErrorMessagePrev1 AS ConversionErrorMessagePrev1,
COALESCE(cte.ConversionStatusPrev2,i.ri2) AS ConversionStatusPrev2,
cte.ConversionErrorMessagePrev2 AS ConversionErrorMessagePrev2,
COALESCE(cte.ConversionStatusPrev3,i.ri3) AS ConversionStatusPrev3,
cte.ConversionErrorMessagePrev3 AS ConversionErrorMessagePrev3
FROM PWDPermitConversionData p
LEFT OUTER JOIN cte
ON p.PWDPermitConversionDataID = cte.PWDPermitConversionDataID
LEFT OUTER JOIN cte_ri i
ON p.PWDPermitConversionDataID = i.PWDPermitConversionDataID
April 17, 2010 at 7:04 am
When we started with c.u.r.s.o.r. stuff I didn't notice how similar the handling is. Now, seeing the set based code, it becomes more obvious.
Therefore, I'd recommend the next step:
If you modify the structure of PermitsInserted to have one row per DataID and 11 columns to hold the related import status and also change #inv_PrevPWDConvData to match the same concept, we would not need to do the aggregation (pivot) for the status update which will speed this step up even more.
Once that is done we should look at proper indexing and you'd be amazed how much we still can squeeze out of this process.
April 17, 2010 at 7:07 am
lmu92 (4/17/2010)
@Wayne
: we're getting closer to the 00:45 you've been talking about 😀We probably will even be able to beat it!
That's what I'm talking about!
So, what the goal? 0:30? 0:15?
(41+ hrs to < 1 minute... absolutely fantastic!)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 17, 2010 at 7:23 am
WayneS (4/17/2010)
lmu92 (4/17/2010)
@Wayne
: we're getting closer to the 00:45 you've been talking about 😀We probably will even be able to beat it!
That's what I'm talking about!
So, what the goal? 0:30? 0:15?
(41+ hrs to < 1 minute... absolutely fantastic!)
We'll see... If I'm not overlooking something and we can really do everything in one path (for both, primary records and previous permits) then we should be able to do it in less than 30sec. But I actually doubt to go below 15...
April 17, 2010 at 9:45 am
Hey guys, thanks again for everything. Just to let you know, I haven't read through Lutz's cte version of that last update yet, just read the first part of the post. Don't have time to go through it right now...yesterday was my birthday, and right now I have to leave for my own bachelor party...needless to say I may not look at anything tomorrow either :). Talk to yall on Monday.
April 17, 2010 at 11:13 am
gregory.anderson (4/17/2010)
Hey guys, thanks again for everything. Just to let you know, I haven't read through Lutz's cte version of that last update yet, just read the first part of the post. Don't have time to go through it right now...yesterday was my birthday, and right now I have to leave for my own bachelor party...needless to say I may not look at anything tomorrow either :). Talk to yall on Monday.
First of all: Happy Birthday, Gregory!!!
And Congrats to your Bachelor degree, too!!!
I hope you'll enjoy the gift I have for you 🙂
April 17, 2010 at 5:11 pm
I have similar situation but it doesn't take 24 hours for my ETL process of converting 3+ M records to finish. It takes my ETL pross less than 2 hours. Here are my steps:
1. Load my source mainframe data in a flat stage table with no index, keys etc.
2. Exec an SP to truncate first and then load data from flat stage table to final table with all the bells and whisles(clustered index, keys etc.) this is where i do all my conversions using ASCII, PADINDEX etc.
ie: insert into FinalTable (this is just an example and may have syntax errors...)
insert into myFinalTable (col1, col2, col3, ...)
select case
when PADINDEX('%A-Z,a-z,0-9%', left(stagecol1,1) COLLATE Latin1_General_CS_AS = 1) then stagecol1 else null end
,when PADINDEX('%A-Z,a-z,0-9%', left(stagecol2,1) COLLATE Latin1_General_CS_AS = 1) then stagecol2 else null end
,when PADINDEX('%A-Z,a-z,0-9%', left(stagecol3,1) COLLATE Latin1_General_CS_AS = 1) then stagecol3 else null end
FROM stageTbl
WHERE (whatever...)
Questions for you:
Why do you need index on staging table?
Do you UPSERT in the final table or you TRUNCATE the final table and then insert?
Hope this helps.
April 17, 2010 at 6:45 pm
sagheeremail (4/17/2010)
I have similar situation but it doesn't take 24 hours for my ETL process of converting 3+ M records to finish. It takes my ETL pross less than 2 hours. Here are my steps:1. Load my source mainframe data in a flat stage table with no index, keys etc.
2. Exec an SP to truncate first and then load data from flat stage table to final table with all the bells and whisles(clustered index, keys etc.) this is where i do all my conversions using ASCII, PADINDEX etc.
ie: insert into FinalTable (this is just an example and may have syntax errors...)
insert into myFinalTable (col1, col2, col3, ...)
select case
when PADINDEX('%A-Z,a-z,0-9%', left(stagecol1,1) COLLATE Latin1_General_CS_AS = 1) then stagecol1 else null end
,when PADINDEX('%A-Z,a-z,0-9%', left(stagecol2,1) COLLATE Latin1_General_CS_AS = 1) then stagecol2 else null end
,when PADINDEX('%A-Z,a-z,0-9%', left(stagecol3,1) COLLATE Latin1_General_CS_AS = 1) then stagecol3 else null end
FROM stageTbl
WHERE (whatever...)
Questions for you:
Why do you need index on staging table?
Do you UPSERT in the final table or you TRUNCATE the final table and then insert?
Hope this helps.
Please open a separate thread. Other than having a long pcrocessing time, you issue is completely different. Also, if you read through this thread you'll figure that the most important part to get any sort of a valid answer is some ready to use code (table def, sample data and expected result). For details please see the first link in my signature.
Your code snippet and your question have nothing in common, as far as I can see. So, please be more specific.
April 20, 2010 at 6:40 am
lmu92 (4/17/2010)
Ok, here's what I came up with:I'm using a CTE again. The first one ("cte") is used to pivot the failed ConversionDataID's.
The second CTE ("cte_ri") pivot the inserted ConversionDataID's and defines the update value for inserted data ("1") at the same time.
Finally, I join those two subresults to the staging table. I provided just the first 3 out of the 10 prevRecords, because I know you get the concept based on the sample. 😉
Thinking about it, you might not even need to do the status update for the primary records as a separate run. If you'd include it in the code below we should not need to do it separately. So we do the status update once we're done, touching the staging table only once.
And te very best solution would be not to deal with primary records and previous permits in separate statements. If you look at the two code blocks you'll find that both are very similar. If you'd query master..spt_values for number >= 0 And number < 11, you could use the Zero value to assign the primary record columns.
You also might think about to insert error rows directly into PWDPermitConversionErrors instead of #inv_PWDConvData.
Then your code would consist of one statement to find and insert errors into PWDPermitConversionErrors , one to add the valid rows to PWDPermit and one to update the status fields in PWDPermitConversionData. Let's see how long that code will take to run...
@Wayne: we're getting closer to the 00:45 you've been talking about 😀
We probably will even be able to beat it!
;WITH cte AS(
SELECT
PWDPermitConversionDataID,
MAX(CASE WHEN Recordindicator =1 THEN errorid ELSE NULL END) AS ConversionStatusPrev1,
MAX(CASE WHEN Recordindicator =1 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev1,
MAX(CASE WHEN Recordindicator =2 THEN errorid ELSE NULL END) AS ConversionStatusPrev2,
MAX(CASE WHEN Recordindicator =2 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev2,
MAX(CASE WHEN Recordindicator =3 THEN errorid ELSE NULL END) AS ConversionStatusPrev3,
MAX(CASE WHEN Recordindicator =3 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev3
FROM #inv_PrevPWDConvData
GROUP BY PWDPermitConversionDataID
),
cte_ri AS
(
SELECT
PWDPermitConversionDataID,
MAX(CASE WHEN Recordindicator =1 THEN 1 ELSE NULL END) AS ri1,
MAX(CASE WHEN Recordindicator =2 THEN 1 ELSE NULL END) AS ri2,
MAX(CASE WHEN Recordindicator =3 THEN 1 ELSE NULL END) AS ri3
FROM PermitsInserted
WHERE Recordindicator>0
GROUP BY PWDPermitConversionDataID
)
SELECT
p.PWDPermitConversionDataID,
COALESCE(cte.ConversionStatusPrev1,i.ri1) AS ConversionStatusPrev1,
cte.ConversionErrorMessagePrev1 AS ConversionErrorMessagePrev1,
COALESCE(cte.ConversionStatusPrev2,i.ri2) AS ConversionStatusPrev2,
cte.ConversionErrorMessagePrev2 AS ConversionErrorMessagePrev2,
COALESCE(cte.ConversionStatusPrev3,i.ri3) AS ConversionStatusPrev3,
cte.ConversionErrorMessagePrev3 AS ConversionErrorMessagePrev3
FROM PWDPermitConversionData p
LEFT OUTER JOIN cte
ON p.PWDPermitConversionDataID = cte.PWDPermitConversionDataID
LEFT OUTER JOIN cte_ri i
ON p.PWDPermitConversionDataID = i.PWDPermitConversionDataID
Ok, I'm back from the birhtdya, bachelor party, and then a fun day of food poisoning (or something like that) yesterday. I'm on this post so I have quite a few to read/go through before I post again...
April 20, 2010 at 9:22 am
Ok, back to the fun...
How does this code block work (the MAX part) when it is going to return a non-integer value for the error mesage?
MAX(CASE WHEN Recordindicator =1 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev1,
I'm also still struggling with this first cte so I need to look at it and think about it more...edit (the group by part)
April 20, 2010 at 9:53 am
gregory.anderson (4/20/2010)
Ok, back to the fun...How does this code block work (the MAX part) when it is going to return a non-integer value for the error mesage?
MAX(CASE WHEN Recordindicator =1 THEN ErrorMessage ELSE NULL END) AS ConversionErrorMessagePrev1,
I'm also still struggling with this first cte so I need to look at it and think about it more...edit (the group by part)
Ok, after just doing a simple select statement with this instead of putting it in a CTE I know understand why it works, but the hard part for me, is being able to think about doing that in the future. I still don't know WHY the max works on a varchar field, I guess I just assumed it was only allowed to be done on an integer type field.
By the way, modifying the last select to make it the actual update statement, this is now running at 2:02.
Not sure if I did something wrong here, but I got this error message on the insert for the "previous" permits.
Warning: Null value is eliminated by an aggregate or other SET operation.
Nevermind, that's for the update statement...doing the max thing with a null value...
Oh, and one last thing that may make you guys happeir, I'm running this stuff on a wimpy virtual server that doesn't have anywhere close to the processing power, or RAM as the production server...so keep that in mind...maybe we can hit the 15 second mark.
Viewing 15 posts - 61 through 75 (of 113 total)
You must be logged in to reply to this topic. Login to reply