July 8, 2008 at 6:23 pm
/*
I have an Import process.
I am working with two tables, tblImportConfig and ImportHistory. In addition, I have two Temp tables #LatestImportHistory (last import logged) and #LatestImportConfig which gets the latest import.
tblImportConfig is static; it hold all the imports that need to be executed.
Three columns decide which package should excute next (importid, sequenceNumber, and packageName).
In my query, I left joined tblImportConfig with #latestImportHistory to return the imports that have not been executed as null. If an import has been executed, it will be logged and will easily show in #LatestImportConfig.
Meaning the import will be logged with a StartTime, EndTime, and Status in #latestImportConfig.
I am trying to write a query that returns the next importid, sequenceNumber, and packageName that should be executed.
The business rule is that if an import is successful it should continue with the next associated sequenceNumber; However, if it fails, it should skip all the associated SequenceNumbers, and go to the next importId (group)
I have 3 questions:
1.I need to increment the BatchNumber by 1 (+1) per Import group.
Not per sequenceNumber...per import for example:
BatchNumber ImportID SequenceNumber
100 1 1
100 1 2
100 1 3
101 2 1
101 2 2
101 2 3
102 3 1
102 3 2
2. If an import execution status is success it should continue executing the associated
sequenceNumbers per each import. But if it fails, it should skip to the next import group.
BatchNumber ImportID SequenceNumber Status
100 1 1 Success
100 1 2 Fail
100 1 3 skip
101 2 1 Fail
101 2 2 skip
101 2 3 skip
102 3 1 success
102 3 2 success
102 3 3 success
3. Once all the imports have been executed it should execute all the failed and skipped
packages.
*/
CREATE TABLE [dbo].[ImportHistory](
[ImportLogID] [int] IDENTITY(1,1) NOT NULL,
[ImportID] [int] NOT NULL,
[SequenceNumber] [int] NOT NULL,
[Start_Time] [datetime] NULL,
[End_Time] [datetime] NULL,
[Status] [varchar](20) NULL,
[BatchNumber] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblImportConfig](
[importConfigID] [int] NULL,
[ImportID] [int] NULL,
[SequenceNumber] [int] NULL,
[PackageName] [varchar](30) NULL,
[active flag] [int] NULL
) ON [PRIMARY]
Go
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(1,1,'pkgMaster.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(1,2,'pkg1.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(1,3,'pkg2.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(2,1,'pkg3.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(2,2,'pkg4.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(2,3,'pkg5.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(3,1,'pkg6.dtsx',1)
insert into dbo.tblImportConfig (ImportId, SequenceNumber, PackageName, [Active Flag])
values(3,2,'pkg7dtsx',1)
insert into dbo.ImportHistory(ImportID, SequenceNumber, Start_Time, End_Time, [Status], [BatchNumber])
values(1,1,GetDate(),GetDate(),'Success', '100')
insert into dbo.ImportHistory(ImportID, SequenceNumber, Start_Time, End_Time, [Status], [BatchNumber])
values(1,2,GetDate(),GetDate(),'success', '100')
insert into dbo.ImportHistory(ImportID, SequenceNumber, Start_Time, End_Time, [Status], [BatchNumber])
drop table #LatestImportHistory, #LatestImportConfig
Create Table #LatestImportHistory (
ImportID INT,
SequenceNumber INT,
ImportLogID INT,
Status varchar(25) )
Create Table #LatestImportConfig (
ImportConfigID INT,
BatchNumber INT,
ImportID INT,
SequenceNumber INT,
PackageName VARCHAR(100),
ActiveFlag INT,
StartTime DATETIME,
EndTime DATETIME,
[Status] VARCHAR(25) )
--the last import logged
INSERT INTO #LatestImportHistory
SELECT
ImportID,
SequenceNumber,
MAX(ImportLogID) ImportLogID,
Status
FROM dbo.ImportHistory
GROUP BY ImportID, SequenceNumber, status
ORDER BY ImportID, SequenceNumber, status
--
INSERT INTO #LatestImportConfig
SELECT
IC.ImportConfigID,
IH.BatchNumber,
IC.ImportID,
IC.SequenceNumber,
IC.PackageName,
IC.[Active Flag],
IH.Start_Time,
IH.End_Time,
IH.Status
FROM dbo.tblImportConfig IC
LEFT JOIN #LatestImportHistory LIH
ON LIH.ImportID = IC.ImportID
AND LIH.SequenceNumber = IC.SequenceNumber
LEFT JOIN dbo.ImportHistory IH
ON IH.ImportLogID = LIH.ImportLogID
order by IC.ImportID, IC.SequenceNumber
--here if ImportHistory is empty it will return the min(importid) and min(SequenceNumber)
IF not Exists(Select * from #LatestImportConfig where [Status] like '%fail%' or Status like '%success%')
Begin
SELECT
min(ImportID),
min(SequenceNumber)
FROM #LatestImportConfig
WHERE ActiveFlag = 1
AND EndTime is null
AND StartTime is null
AND Status is null
AND ImportID in (
select
ImportID
from #LatestImportConfig)
End
--Else
If Not Exists (Select * from #LatestImportConfig where Status like '%fail%')
Begin
SELECT
min(ImportID),
min(SequenceNumber)
FROM #LatestImportConfig
WHERE ActiveFlag = 1
AND EndTime is null
AND StartTime is null
AND Status is null
AND ImportID in (select ImportID from #LatestImportConfig where EndTime
in (select max(EndTime) from #LatestImportConfig where status is null
or Status like '%success%'
and status not like '%fail%'
)
)
AND StartTime is null
AND EndTime IS NULL
AND Status is null
AND ActiveFlag = 1
End
Else
--select * from #LatestImportConfig
--here I am stuck...really stuck...
--it should skip import return the next import if there is a specific fail for an import
If Exists (Select * from #LatestImportConfig where Status like '%fail%')
Begin
SELECT
min(ImportID),
min(SequenceNumber)
FROM #LatestImportConfig lic
Where ImportID in(select ImportID from #LatestImportConfig where status not like '%fail%'
and EndTime in (select max(EndTime) from #LatestImportConfig where status is null
or Status like '%success%' and status not like '%fail%'
)
)
and lic.importid not in (select importid from #LatestImportHistory where status like '%fail%' or status like '%success%')
-- and not exists (select * from #LatestImportHistory where status like '%fail%' or status like '%success%')
and endtime is null
and starttime is null
and status is null
End
SELECT * FROM #LatestImportConfig
select * from #LatestImportHistory
--to begin the import again:
If not Exists (Select * from #LatestImportConfig where Status like '%fail%' and status is not null)
Begin
SELECT
min(lic.ImportID),
min(lic.SequenceNumber)
FROM #LatestImportConfig lic
WHERE (
Not Exists
(
Select * from #LatestImportHistory lih
Where lic.importid = lih.ImportID and lic.SequenceNumber = lih.SequenceNumber
)
or
Exists
(
Select * from #LatestImportHistory lih
Where lic.importid = lih.importid and lih.status like '%success%'
)
)
AND ActiveFlag = 1
End
Select * from #LatestImportHistory
July 9, 2008 at 3:11 am
Hi,
Can you put your expected Results for the given sample datas at the end of each conditional select Statement for the given sample datas
Rajesh
July 9, 2008 at 10:39 am
If you create the two tables I included and insert the two rows and then run the script you should be getting this data. I am trying to write a query that meats all the conditions. Meaning every time a row is inserted into importHistory. It should know which importId, SequenceNumber should run next...also, I want to assign a batchNumber to each import group...incrementing by 1. thanks
Here it is result set:
Select * from #latestImportConfig
importConfigID BatchNum ImportId SequenceN PackageName Active Flag StartTime EndTime Status
1 100 1 1 MasterPackage.dtsx 1 30:01.2 30:01.2 Success
2 100 1 2 pkgAssignCompanies.dtsx 1 30:05.3 30:05.3 success
3 NULL 1 3 pkgAssignServices.dtsx 1 NULL NULL NULL
4 NULL 1 4 pkgAssignServiceDate.dtsx 1 NULL NULL NULL
5 NULL 2 1 pkgAssignLostCompanies.dtsx1 NULL NULL NULL
6 NULL 2 2 pkgAssignHomework.dtsx 1 NULL NULL NULL
7 NULL 2 3 pkgAssignWork.dtsx 1 NULL NULL NULL
8 NULL 2 4 pkgAssignDate.dtsx 1 NULL NULL NULL
9 NULL 3 1 pkgAssignThree1 1 NULL NULL NULL
10 NULL 3 2 pkgAssignThree2 1 NULL NULL NULL
11 NULL 3 2 pkgAssignThree3 1 NULL NULL NULL
12 NULL 3 3 pkg7dtsx 1 NULL NULL NULL
Select * from #LatestImportHistory
Importid SequenceNumber ImportConfigID status
1 1 1 Success
1 2 2 success
--here if ImportHistory is empty it will return the min(importid) and min(SequenceNumber)
IF not Exists(Select * from #LatestImportConfig where [Status] like '%fail%' or Status like '%success%')
Begin
SELECT
min(ImportID),
min(SequenceNumber)
FROM #LatestImportConfig
WHERE ActiveFlag = 1
AND EndTime is null
AND StartTime is null
AND Status is null
AND ImportID in (
select
ImportID
from #LatestImportConfig)
End
---Notice that I have the importid and SequenceNumber...I want to generate a batchnumber per import group as well
ImportId SequeceNumber
1 1
--Else
If Not Exists (Select * from #LatestImportConfig where Status like '%fail%')
Begin
SELECT
min(ImportID),
min(SequenceNumber)
FROM #LatestImportConfig
WHERE ActiveFlag = 1
AND EndTime is null
AND StartTime is null
AND Status is null
AND ImportID in (select ImportID from #LatestImportConfig where EndTime
in (select max(EndTime) from #LatestImportConfig where status is null
or Status like '%success%'
and status not like '%fail%'
)
)
AND StartTime is null
AND EndTime IS NULL
AND Status is null
AND ActiveFlag = 1
End
--result set---
Importid SequcneNumber
1 3
Else
--select * from #LatestImportConfig
--here I am stuck...really stuck...
--it should skip import return the next import if there is a specific fail for an import
If Exists (Select * from #LatestImportConfig where Status like '%fail%')
Begin
SELECT
min(ImportID),
min(SequenceNumber)
FROM #LatestImportConfig lic
Where ImportID in(select ImportID from #LatestImportConfig where status not like '%fail%'
and EndTime in (select max(EndTime) from #LatestImportConfig where status is null
or Status like '%success%' and status not like '%fail%'
)
)
and lic.importid not in (select importid from #LatestImportHistory where status like '%fail%' or status like '%success%')
-- and not exists (select * from #LatestImportHistory where status like '%fail%' or status like '%success%')
and endtime is null
and starttime is null
and status is null
End
importid SequenceNumber
null null
---result set
SELECT * FROM #LatestImportConfig
select * from #LatestImportHistory
--to begin the import again:
If not Exists (Select * from #LatestImportConfig where Status like '%fail%' and status is not null)
Begin
SELECT
min(lic.ImportID),
min(lic.SequenceNumber)
FROM #LatestImportConfig lic
WHERE (
Not Exists
(
Select * from #LatestImportHistory lih
Where lic.importid = lih.ImportID and lic.SequenceNumber = lih.SequenceNumber
)
or
Exists
(
Select * from #LatestImportHistory lih
Where lic.importid = lih.importid and lih.status like '%success%'
)
)
AND ActiveFlag = 1
End
Select * from #LatestImportHistory
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply