Database

select * from SAW_raw.[EOC].[EoCFalloutSolicitationResponseErrorImportStaging] a
inner join [EOC].[FalloutSolicitationResponseErrorDetail] b
on b.CoveredID=a.[0 Covered ID]
    and b.CoveredindividualsInsuranceIDorCardnumber=a.[1 Covered individuals Insurance ID/card number]
    and b.UserDefinedField2=a.[2 User Defined field 2]
    and b.SSNorITIN=a.[3 SSN or ITIN]
    and b.FirstName=a.[4 First Name]
    and b.MiddleName=a.[5 Middle Name]
    and b.LastName=a.[6 Last Name]
    and b.Suffix=a.[7 Suffix]
    and b.DateofBirth=a.[8 Date of Birth]
    and b.UserDefinedField1=a.[9 User Defined field 1]
    and b.UserDefinedField3=a.[10 User Defined field 3]
    and b.CoveredUserDefinedField1=a.[11 Covered User Defined field 1]
    and b.CoveredUserDefinedField2=a.[12 Covered User Defined field 2]
    and b.CoveredUserDefinedField3=a.[13 Covered User Defined field 3]
    and b.CoveredUserDefinedField4=a.[14 Covered User Defined field 4]
    and b.Comments=a.[15 Comments]
    and b.SolicitationRequiredIndicator=a.[16 Solicitation Required indicator]
    and b.ErrorMessage=a.[17 Error Message]

ALTER procedure [EOC].[UpdateFalloutSolicitationResponseErrorDetail] (@FullFileName nvarchar(max)='')
AS 
BEGIN
    TRUNCATE TABLE Saw_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
    TRUNCATE TABLE Saw_raw.[EOC].[WorkInserted]

    DECLARE @TeamId bigint =0, @MaxInstanceIdFromSaw_2 bigint=0,  @ActivityId bigint=0
    , @BatchId bigint=0, @StatusId int=0, @CreatedbyAccountId int=0, @DepartmentId int=1, @PieceCount int=1, @RecordCount int =0;

    SELECT top 1 @TeamId=Id FROM SAW_2.[dbo].[Team] WHERE TeamName='MEP - Small Group' AND DepartmentId=@DepartmentId
    SELECT top 1 @ActivityId=Id FROM SAW_2.[dbo].Activity WHERE ActivityName='EoC – Facets Inbound Error Report' AND TeamId=@TeamId
    SELECT top 1 @BatchId=Id FROM SAW_2.[dbo].Batch WHERE BatchName ='EoC Fallout Error Reports'
    SELECT top 1 @CreatedbyAccountId=Id FROM SAW_2.[dbo].Account WHERE LanId ='SYSTEM'
    select @StatusId=Id from SAW_2.[dbo].[Status] WHERE StatusName='New'

    IF  EXISTS (
    SELECT * FROM sys.key_constraints
    WHERE type = 'PK'
    AND OBJECT_NAME(parent_object_id) = 'FalloutSolicitationResponseErrorDetail'
    )
    BEGIN<br />
        ALTER TABLE [EOC].[FalloutSolicitationResponseErrorDetail]
        DROP CONSTRAINT PK_FalloutSolicitationResponseErrorDetail_InstanceId
    END
    IF EXISTS (SELECT name FROM sys.indexes<br />
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredID')
    BEGIN
        DROP INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredID ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
    END
    IF EXISTS (SELECT name FROM sys.indexes<br />
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber')
    BEGIN
        DROP INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
    END
    IF EXISTS (SELECT name FROM sys.indexes<br />
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_SSNorITIN')
    BEGIN
        DROP INDEX IX_FalloutSolicitationResponseErrorDetail_SSNorITIN ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
    END
    IF EXISTS (SELECT name FROM sys.indexes<br />
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_LastName')
    BEGIN
        DROP INDEX IX_FalloutSolicitationResponseErrorDetail_LastName ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
    END
    IF EXISTS (SELECT name FROM sys.indexes<br />
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_DateofBirth')
    BEGIN
        DROP INDEX IX_FalloutSolicitationResponseErrorDetail_DateofBirth ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
    END
    IF EXISTS (SELECT name FROM sys.indexes<br />
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_IsActive')
    BEGIN
        DROP INDEX IX_FalloutSolicitationResponseErrorDetail_IsActive ON [EOC].[FalloutSolicitationResponseErrorDetail];<br />
    END

    MERGE INTO
      EOC.FalloutSolicitationResponseErrorDetail a
    USING
      SAW_raw.[EOC].[EoCFalloutSolicitationResponseErrorImportStaging] b
    ON
      --a.InstanceId = b.Id
        LTRIM(RTRIM(ISNULL(a.CoveredID, '')))=LTRIM(RTRIM(ISNULL(b.[0 Covered ID],'')))
        AND LTRIM(RTRIM(ISNULL(a.CoveredindividualsInsuranceIDorCardnumber, '')))=LTRIM(RTRIM(ISNULL(b.[1 Covered individuals Insurance ID/card number],'')))
        AND LTRIM(RTRIM(ISNULL(a.UserDefinedField2, '')))=LTRIM(RTRIM(ISNULL(b.[2 User Defined field 2],'')))
        AND LTRIM(RTRIM(ISNULL(a.SSNorITIN, '')))=LTRIM(RTRIM(ISNULL(b.[3 SSN or ITIN],'')))
        AND LTRIM(RTRIM(ISNULL(a.FirstName, '')))=LTRIM(RTRIM(ISNULL(b.[4 First Name],'')))
        AND LTRIM(RTRIM(ISNULL(a.MiddleName, '')))=LTRIM(RTRIM(ISNULL(b.[5 Middle Name],'')))
        AND LTRIM(RTRIM(ISNULL(a.LastName, '')))=LTRIM(RTRIM(ISNULL(b.[6 Last Name],'')))
        AND LTRIM(RTRIM(ISNULL(a.Suffix, '')))=LTRIM(RTRIM(ISNULL(b.[7 Suffix],'')))
        AND convert(nvarchar(MAX), a.DateofBirth, 110) = convert(nvarchar(MAX), Cast(b.[8 Date of Birth] as Datetime), 110)
        AND LTRIM(RTRIM(ISNULL(a.UserDefinedField1, '')))=LTRIM(RTRIM(ISNULL(b.[9 User Defined field 1],'')))
        AND LTRIM(RTRIM(ISNULL(a.UserDefinedField3, '')))=LTRIM(RTRIM(ISNULL(b.[10 User Defined field 3],'')))
        AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField1, '')))=LTRIM(RTRIM(ISNULL(b.[11 Covered User Defined field 1],'')))
        AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField2, '')))=LTRIM(RTRIM(ISNULL(b.[12 Covered User Defined field 2],'')))
        AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField3, '')))=LTRIM(RTRIM(ISNULL(b.[13 Covered User Defined field 3],'')))
        AND LTRIM(RTRIM(ISNULL(a.CoveredUserDefinedField4, '')))=LTRIM(RTRIM(ISNULL(b.[14 Covered User Defined field 4],'')))
        AND LTRIM(RTRIM(ISNULL(a.Comments, '')))=LTRIM(RTRIM(ISNULL(b.[15 Comments],'')))
        AND LTRIM(RTRIM(ISNULL(a.SolicitationRequiredIndicator, '')))=LTRIM(RTRIM(ISNULL(b.[16 Solicitation Required indicator],'')))
        AND LTRIM(RTRIM(ISNULL(a.ErrorMessage, '')))=LTRIM(RTRIM(ISNULL(b.[17 Error Message],'')))
    WHEN MATCHED THEN
      UPDATE SET
        a.CoveredID=b.[0 Covered ID]
        ,a.CoveredindividualsInsuranceIDorCardnumber=b.[1 Covered individuals Insurance ID/card number]
        ,a.UserDefinedField2=b.[2 User Defined field 2]
        ,a.SSNorITIN=b.[3 SSN or ITIN]
        ,a.FirstName=b.[4 First Name]
        ,a.MiddleName=b.[5 Middle Name]
        ,a.LastName=b.[6 Last Name]
        ,a.Suffix=b.[7 Suffix]
        ,a.DateofBirth=b.[8 Date of Birth]
        ,a.UserDefinedField1=b.[9 User Defined field 1]
        ,a.UserDefinedField3=b.[10 User Defined field 3]
        ,a.CoveredUserDefinedField1=b.[11 Covered User Defined field 1]
        ,a.CoveredUserDefinedField2=b.[12 Covered User Defined field 2]
        ,a.CoveredUserDefinedField3=b.[13 Covered User Defined field 3]
        ,a.CoveredUserDefinedField4=b.[14 Covered User Defined field 4]
        ,a.Comments=b.[15 Comments]
        ,a.SolicitationRequiredIndicator=b.[16 Solicitation Required indicator]
        ,a.ErrorMessage=b.[17 Error Message]
        ,a.ModifiedDate=getdate()
        ,a.ModifiedBy=user_name()
    WHEN NOT MATCHED THEN
      INSERT
            (
            --InstanceId,
            CoveredID
            ,CoveredindividualsInsuranceIDorCardnumber
            ,UserDefinedField2
            ,SSNorITIN
            ,FirstName
            ,MiddleName
            ,LastName
            ,Suffix
            ,DateofBirth
            ,UserDefinedField1
            ,UserDefinedField3
            ,CoveredUserDefinedField1
            ,CoveredUserDefinedField2
            ,CoveredUserDefinedField3
            ,CoveredUserDefinedField4
            ,Comments
            ,SolicitationRequiredIndicator
            ,ErrorMessage
            ,CreatedDate
            ,CreatedBy
            ,ModifiedDate
            ,ModifiedBy
            )
      VALUES
            (
            --b.[Id],
            b.[0 Covered ID]
            ,b.[1 Covered individuals Insurance ID/card number]
            ,b.[2 User Defined field 2]
            ,b.[3 SSN or ITIN]
            ,b.[4 First Name]
            ,b.[5 Middle Name]
            ,b.[6 Last Name]
            ,b.[7 Suffix]
            ,b.[8 Date of Birth]
            ,b.[9 User Defined field 1]
            ,b.[10 User Defined field 3]
            ,b.[11 Covered User Defined field 1]
            ,b.[12 Covered User Defined field 2]
            ,b.[13 Covered User Defined field 3]
            ,b.[14 Covered User Defined field 4]
            ,b.[15 Comments]
            ,b.[16 Solicitation Required indicator]
            ,b.[17 Error Message]
            ,GetDate()
            ,CURRENT_USER
            ,GetDate()
            ,CURRENT_USER
        )
        OUTPUT 
        Inserted.InstanceId
        ,Inserted.CoveredID
        ,Inserted.CoveredindividualsInsuranceIDorCardnumber
        ,Inserted.UserDefinedField2
        ,Inserted.SSNorITIN
        ,Inserted.FirstName
        ,Inserted.MiddleName
        ,Inserted.LastName
        ,Inserted.Suffix
        ,Inserted.DateofBirth
        ,Inserted.UserDefinedField1
        ,Inserted.UserDefinedField3
        ,Inserted.CoveredUserDefinedField1
        ,Inserted.CoveredUserDefinedField2
        ,Inserted.CoveredUserDefinedField3
        ,Inserted.CoveredUserDefinedField4
        ,Inserted.Comments
        ,Inserted.SolicitationRequiredIndicator
        ,Inserted.ErrorMessage
        ,Inserted.CreatedDate
        ,Inserted.CreatedBy
        ,Inserted.ModifiedDate
        ,Inserted.ModifiedBy
        INTO
        SAW_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
        (
            InstanceId
            ,CoveredID
            ,CoveredindividualsInsuranceIDorCardnumber
            ,UserDefinedField2
            ,SSNorITIN
            ,FirstName
            ,MiddleName
            ,LastName
            ,Suffix
            ,DateofBirth
            ,UserDefinedField1
            ,UserDefinedField3
            ,CoveredUserDefinedField1
            ,CoveredUserDefinedField2
            ,CoveredUserDefinedField3
            ,CoveredUserDefinedField4
            ,Comments
            ,SolicitationRequiredIndicator
            ,ErrorMessage
            ,CreatedDate
            ,CreatedBy
            ,ModifiedDate
            ,ModifiedBy
        );

    INSERT INTO Saw_2.[dbo].[Work]
    (<br />
        [BatchId]
        ,[StatusId]
        ,[ActivityId]
        ,[CreatedbyAccountId]<br />
        ,[PieceCount]<br />
        ,[BscReceiptDateTs]
        ,[WorkflowCreatedTs]<br />
        ,[InstanceId]<br />
    )
    OUTPUT 
        inserted.Id
        , inserted.BatchId
        ,inserted.StatusId
        ,inserted.ActivityId
        ,inserted.CreatedbyAccountId<br />
        ,inserted.PieceCount<br />
        ,inserted.BscReceiptDateTs
        ,inserted.WorkflowCreatedTs<br />
        ,inserted.InstanceId
        INTO Saw_raw.[EOC].[EOCWorkInserted]
        (
        Id
        , BatchId
        ,StatusId
        ,ActivityId
        ,CreatedbyAccountId<br />
        ,PieceCount<br />
        ,BscReceiptDateTs
        ,WorkflowCreatedTs<br />
        ,InstanceId
        )
    SELECT @BatchId, @StatusId, @ActivityId, @CreatedbyAccountId, @PieceCount,getdate() ,getdate(), InstanceId
    FROM SAW_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]
    --FROM SAW_raw.[EOC].[EoCFalloutSolicitationResponseErrorImportStaging] WHERE Id NOT IN
    --(
    --  SELECT InstanceId FROM Saw_2.[dbo].[Work] WHERE ActivityId=@ActivityId
    --);

    SELECT @RecordCount=Count(*) FROM Saw_raw.[EOC].[FalloutSolicitationResponseErrorDetail_InsertedRecords]

    UPDATE [Saw_2].[dbo].[FileLoad] SET [RecordCount]=@RecordCount 
    WHERE rtrim(ltrim([FullFileName]))=rtrim(ltrim(@FullFileName))

    IF  NOT EXISTS (
        SELECT * FROM sys.key_constraints
        WHERE type = 'PK'
        AND OBJECT_NAME(parent_object_id) = 'FalloutSolicitationResponseErrorDetail'
        )
        BEGIN
            ALTER TABLE [EOC].[FalloutSolicitationResponseErrorDetail]   
            ADD CONSTRAINT PK_FalloutSolicitationResponseErrorDetail_InstanceId PRIMARY KEY CLUSTERED (InstanceId); 
        END
    IF NOT EXISTS (SELECT name FROM sys.indexes  
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredID')
    BEGIN
        CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredID   
        ON [EOC].[FalloutSolicitationResponseErrorDetail](CoveredID);      
    END
    IF NOT EXISTS (SELECT name FROM sys.indexes  
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber')
    BEGIN
        CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_CoveredindividualsInsuranceIDorCardnumber   
        ON [EOC].[FalloutSolicitationResponseErrorDetail](CoveredindividualsInsuranceIDorCardnumber);      
    END
    IF NOT EXISTS (SELECT name FROM sys.indexes  
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_SSNorITIN')
    BEGIN
        CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_SSNorITIN   
        ON [EOC].[FalloutSolicitationResponseErrorDetail](SSNorITIN);      
    END
    IF NOT EXISTS (SELECT name FROM sys.indexes  
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_LastName')
    BEGIN
        CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_LastName   
        ON [EOC].[FalloutSolicitationResponseErrorDetail](LastName);      
    END
    IF NOT EXISTS (SELECT name FROM sys.indexes  
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_DateofBirth')
    BEGIN
        CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_DateofBirth   
        ON [EOC].[FalloutSolicitationResponseErrorDetail](DateofBirth);      
    END
    IF NOT EXISTS (SELECT name FROM sys.indexes  
            WHERE name = N'IX_FalloutSolicitationResponseErrorDetail_IsActive')
    BEGIN
        CREATE NONCLUSTERED INDEX IX_FalloutSolicitationResponseErrorDetail_IsActive   
        ON [EOC].[FalloutSolicitationResponseErrorDetail](IsActive);      
    END     
END

0x01AA wrote:

The query itself returns the result in about 0.03 seconds which is pretty much nice I think.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

0x01AA wrote:

Using a temp table, then it takes nearly 0 seconds, but temp table with Interbase is very uncomfortable compared to MSSQL.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

DECLARE @StartDate datetime2(0) = '2016-01-04 02:19:45.000';
DECLARE @EndDate datetime2(0) = '2016-08-31 07:57:37.000';
DECLARE @EndDay date = CAST(@EndDate As date);

WITH CW (StartDate, EndDate) As
(
    SELECT
        @StartDate,
        DateAdd(wk, 1, CAST(CAST(@StartDate As date) As datetime2(0)))

    UNION ALL

    SELECT
        EndDate,
        CASE
            WHEN DateAdd(wk, 1, EndDate) < @EndDay THEN DateAdd(wk, 1, EndDate)
            ELSE @EndDate
        END
    FROM
        CW
    WHERE
        EndDate < @EndDay
)
SELECT
    StartDate,
    EndDate
FROM 
    CW
;

StartDate               EndDate
-------------------     -------------------
2016-01-04 02:19:45     2016-01-11 00:00:00
2016-01-11 00:00:00     2016-01-18 00:00:00
...
2016-08-22 00:00:00     2016-08-29 00:00:00
2016-08-29 00:00:00     2016-08-31 07:57:37

MachineID    ProcessID    StartDate              EndDate
---------    ---------    ---------              -------
50           1            2016-01-04 02:19:45    2016-06-08 07:57:37
50           1            2016-10-01 02:19:45    2016-12-31 07:57:37

WITH CW (ProcessID, StartDate, EndDate, RunEnd, RunEndDay) As
(
    SELECT
        ProcessID,
        StartDate,
        DateAdd(wk, 1, CAST(CAST(StartDate As date) As datetime2(0))),
        EndDate,
        CAST(EndDate As date)
    FROM
        YourTable
    WHERE
        MachineID = 50
 
    UNION ALL
 
    SELECT
        ProcessID,
        EndDate,
        CASE
            WHEN DateAdd(wk, 1, EndDate) < RunEndDay THEN DateAdd(wk, 1, EndDate)
            ELSE RunEnd
        END,
        RunEnd,
        RunEndDay
    FROM
        CW
    WHERE
        EndDate < RunEndDay
)
SELECT
    ProcessID,
    StartDate,
    EndDate
FROM 
    CW
ORDER BY
    ProcessID,
    StartDate
;

ProcessID    StartDate              EndDate
---------    ---------              -------
1            2016-01-04 02:19:45    2016-01-11 00:00:00
1            2016-01-11 00:00:00    2016-01-18 00:00:00
...
1            2016-05-30 00:00:00    2016-06-06 00:00:00
1            2016-06-06 00:00:00    2016-06-08 07:57:37

2            2016-10-01 02:19:45    2016-10-08 00:00:00
2            2016-10-08 00:00:00    2016-10-15 00:00:00
...
2            2016-12-17 00:00:00    2016-12-24 00:00:00
2            2016-12-24 00:00:00    2016-12-31 07:57:37

var ds = new DataSet();

using (var connection = new SqlConnection("..."))
{
    var da = new SqlDataAdapter("SELECT ...", connection);
    da.SelectCommand.Parameters.AddWithValue(...);
    da.Fill(ds, "FirstTable");
}

using (var connection = new OleDbConnection("..."))
{
    var da = new OleDbDataAdapter("SELECT ...", connection);
    da.SelectCommand.Parameters.AddWithValue(...);
    da.Fill(ds, "SecondTable");
}