SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) 
 Sep  7 2018 01:37:51 
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor)



/* DEPENDENCY: RegexFind
USE [M1_MA]
GO

/****** Object:  UserDefinedFunction [dbo].[RegexFind]    Script Date: 5/4/2023 10:16:11 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

    
CREATE FUNCTION [dbo].[RegexFind](
    @pattern VARCHAR(255),
    @matchstring VARCHAR(MAX),
    @global BIT = 1,
   @Multiline bit =1)
RETURNS
    @result TABLE
        (
        Match_ID INT,
          FirstIndex INT ,
          length INT ,
          Value VARCHAR(2000),
          Submatch_ID INT,
          SubmatchValue VARCHAR(2000),
         Error VARCHAR(255)
        )
 
AS -- columns returned by the function
   BEGIN
    DECLARE @objRegexExp INT,
        @objErrorObject INT,
        @objMatch INT,
        @objSubMatches INT,
        @strErrorMessage VARCHAR(255),
       @error VARCHAR(255),
        @Substituted VARCHAR(8000),
        @hr INT,
        @matchcount INT,
        @SubmatchCount INT,
        @ii INT,
        @jj INT,
        @FirstIndex INT,
        @length INT,
        @Value VARCHAR(2000),
        @SubmatchValue VARCHAR(2000),
        @objSubmatchValue INT,
        @command VARCHAR(8000),
        @Match_ID INT
        
    DECLARE @match TABLE
        (
          Match_ID INT IDENTITY(1, 1)
                       NOT NULL,
          FirstIndex INT NOT NULL,
          length INT NOT NULL,
          Value VARCHAR(2000)
        )    
    DECLARE @Submatch TABLE
        (
          Submatch_ID INT IDENTITY(1, 1),
          match_ID INT NOT NULL,
          SubmatchNo INT NOT NULL,
          SubmatchValue VARCHAR(2000)
        )
    
    SELECT  @strErrorMessage = 'creating a regex object',@error=''
    EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
    IF @hr = 0
        SELECT  @strErrorMessage = 'Setting the Regex pattern',
                @objErrorObject = @objRegexExp
    IF @hr = 0
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
    IF @hr = 0
        SELECT  @strErrorMessage = 'Specifying a case-insensitive match'
    IF @hr = 0
        EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
    IF @hr = 0
        EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
    IF @hr = 0
        EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
    IF @hr = 0
        SELECT  @strErrorMessage = 'Doing a match'
    IF @hr = 0
        EXEC @hr= sp_OAMethod @objRegexExp, 'execute', @objMatch OUT,
            @matchstring
    IF @hr = 0
        SELECT  @strErrorMessage = 'Getting the number of matches'    
    IF @hr = 0
        EXEC @hr= sp_OAGetProperty @objmatch, 'count', @matchcount OUT
    SELECT  @ii = 0
    WHILE @hr = 0
        AND @ii < @Matchcount
        BEGIN
/*
    
The Match object has four read-only properties.
The FirstIndex property indicates the number of characters in the string to the left of the match.
The Length property of the Match object indicates the number of characters in the match.
The Value property returns the text that was matched.
 
*/
    
            SELECT  @strErrorMessage = 'Getting the FirstIndex property',
                    @command = 'item(' + CAST(@ii AS VARCHAR) + ').FirstIndex'    
            IF @hr = 0
                EXEC @hr= sp_OAGetProperty @objmatch, @command,
                    @Firstindex OUT
            IF @hr = 0
                SELECT  @strErrorMessage = 'Getting the length property',
                        @command = 'item(' + CAST(@ii AS VARCHAR) + ').Length'    
            IF @hr = 0
                EXEC @hr= sp_OAGetProperty @objmatch, @command, @Length OUT
            IF @hr = 0
                SELECT  @strErrorMessage = 'Getting the value property',
                        @command = 'item(' + CAST(@ii AS VARCHAR) + ').Value'    
            IF @hr = 0
                EXEC @hr= sp_OAGetProperty @objmatch, @command, @Value OUT
            INSERT  INTO @match
                    (
                      Firstindex,
                      [Length],
                      [Value]
                    )
                    SELECT  @firstindex + 1,
                            @Length,
                            @Value
            SELECT  @Match_ID = @@Identity        
    
/*The SubMatches property of the Match object is a collection of strings. It will only hold values if your regular expression has capturing groups. The collection will hold one string for each capturing group. The Count property (returned as SubmatchCount) indicates the number of string in the collection. The Item property takes an index parameter, and returns the text matched by the capturing group.
    
*/
    
            IF @hr = 0
                SELECT  @strErrorMessage = 'Getting the SubMatches collection',
                        @command = 'item(' + CAST(@ii AS VARCHAR)
                        + ').SubMatches'    
            IF @hr = 0
                SELECT  @strErrorMessage = 'Getting the number of submatches'    
            IF @hr = 0
                EXEC @hr= sp_OAGetProperty @objSubmatches, 'count',
                    @submatchCount OUT
            SELECT  @jj = 0
            WHILE @hr = 0
                AND @jj < @submatchCount
                BEGIN
                    IF @hr = 0
                        SELECT  @strErrorMessage = 'Getting the submatch value property',
                                @command = 'item(' + CAST(@jj AS VARCHAR)
                                + ')' ,@submatchValue=NULL  
                    IF @hr = 0
                        EXEC @hr= sp_OAGetProperty @objSubmatches, @command,
                            @SubmatchValue OUT
                    INSERT  INTO @Submatch
                            (
                              Match_ID,
                              SubmatchNo,
                              SubmatchValue
                            )
                            SELECT  @Match_ID,
                                    @jj+1,
                                    @SubmatchValue
                    SELECT  @jj = @jj + 1
                END  
                EXEC @hr= sp_OAGetProperty @objmatch, @command,
                    @objSubmatches OUT    
            SELECT  @ii = @ii + 1
        END
    IF @hr <> 0
        BEGIN
            DECLARE @Source VARCHAR(255),
                @Description VARCHAR(255),
                @Helpfile VARCHAR(255),
                @HelpID INT
  
            EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
                @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
            SELECT  @Error = 'Error whilst '
                    + COALESCE(@strErrorMessage, 'doing something') + ', '
                    + COALESCE(@Description, '')
        END
    EXEC sp_OADestroy @objRegexExp
     EXEC sp_OADestroy        @objMatch
     EXEC sp_OADestroy        @objSubMatches
 
INSERT INTO @result
          (Match_ID,
          FirstIndex,
          [length],
          [Value],
          Submatch_ID,
          SubmatchValue,
         error)
 
 
    SELECT  m.[Match_ID],
           [FirstIndex],
           [length],
           [Value],[SubmatchNo],
           [SubmatchValue],@error
  FROM    @match m
    LEFT OUTER JOIN   @submatch s
    ON m.match_ID=s.match_ID    
IF @@rowcount=0 AND LEN(@error) > 0
INSERT INTO @result(error) SELECT @error
RETURN
END
GO

*/
-- pseudo results of outer apply of function RegexFind (hidden above)

CREATE TABLE ChangeLog(
  JobID nvarchar(20) NOT NULL,
  JobAssemblyID int NOT NULL,
  JobOperationID int NOT NULL,
  FirstDate [datetime] NULL,
  ChangeUserID [nvarchar](20) NOT NULL,
  ChangeLogID [int]  NOT NULL, 
  Match_ID int,
  FirstIndex int,
  FirstValue [nvarchar](max) NULL,
) 

;
INSERT INTO ChangeLog
    (JobID, JobAssemblyID, JobOperationID, FirstDate
    , ChangeUserID, ChangeLogID, Match_ID, FirstIndex, FirstValue)
VALUES
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 4, 68, 'jmoWorkCenterID = TRUCK'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 4, 68, 'jmoWorkCenterID = TRUCK'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 5, 92, 'jmoProcessID = DELIV'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 6, 113, 'jmoProcessShortDescription = Delivery to Job'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 6, 113, 'jmoProcessShortDescription = Delivery to Job'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 7, 158, 'jmoQuantityPerAssembly = 1.000000'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 8, 192, 'jmoProductionStandard = 3.0000'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 8, 192, 'jmoProductionStandard = 3.0000'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 9, 223, 'jmoStandardFactor = TH'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 10, 246, 'jmoSetupRate = 25.00'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 10, 246, 'jmoSetupRate = 25.00'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 11, 267, 'jmoProductionRate = 25.00'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 12, 293, 'jmoOperationQuantity = 1.00000'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 12, 293, 'jmoOperationQuantity = 1.00000'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 13, 324, 'jmoMachineType = 1'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 14, 343, 'jmoUnitOfMeasure = EA'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 14, 343, 'jmoUnitOfMeasure = EA'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 15, 365, 'jmoFirm = 1'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 16, 377, 'jmoEstimatedProductionHours = 3.00'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 16, 377, 'jmoEstimatedProductionHours = 3.00'),
    ('29127-01-01', 0, 30, '2023-05-03 08:12:11.583', 'PAMNEW', 502294, 17, 412, 'jmoMachinesToSchedule = 1'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 4, 69, 'jmoWorkCenterID = IWIND'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 4, 69, 'jmoWorkCenterID = IWIND'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 5, 93, 'jmoProcessID = IWIND'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 6, 114, 'jmoProcessShortDescription = Window and Door Install'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 6, 114, 'jmoProcessShortDescription = Window and Door Install'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 7, 167, 'jmoQuantityPerAssembly = 1.000000'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 8, 201, 'jmoSetupHours = 0.50'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 8, 201, 'jmoSetupHours = 0.50'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 9, 222, 'jmoStandardFactor = TH'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 10, 245, 'jmoSetupRate = 25.00'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 10, 245, 'jmoSetupRate = 25.00'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 11, 266, 'jmoProductionRate = 25.00'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 12, 292, 'jmoOperationQuantity = 1.00000'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 12, 292, 'jmoOperationQuantity = 1.00000'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 13, 323, 'jmoMachineType = 1'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 14, 342, 'jmoUnitOfMeasure = EA'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 14, 342, 'jmoUnitOfMeasure = EA'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 15, 364, 'jmoFirm = 1'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 16, 376, 'jmoMachinesToSchedule = 1'),
    ('29127-01-01', 0, 510, '2023-05-03 08:12:11.583', 'PAMNEW', 502295, 16, 376, 'jmoMachinesToSchedule = 1'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 4, 69, 'jmoWorkCenterID = CRANE'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 4, 69, 'jmoWorkCenterID = CRANE'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 5, 93, 'jmoProcessID = CRANE'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 6, 114, 'jmoProcessShortDescription = Crane Operator'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 6, 114, 'jmoProcessShortDescription = Crane Operator'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 7, 158, 'jmoQuantityPerAssembly = 1.000000'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 8, 192, 'jmoStandardFactor = MP'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 8, 192, 'jmoStandardFactor = MP'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 9, 215, 'jmoOperationQuantity = 1.00000'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 10, 246, 'jmoMachineType = 1'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 10, 246, 'jmoMachineType = 1'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 11, 265, 'jmoUnitOfMeasure = EA'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 12, 287, 'jmoFirm = 1'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 12, 287, 'jmoFirm = 1'),
    ('29127-01-01', 0, 520, '2023-05-03 08:12:11.583', 'PAMNEW', 502296, 13, 299, 'jmoMachinesToSchedule = 1'),
    ('29128-01-01', 0, 10, '2023-05-03 08:36:21.977', 'ZACHUT', 502370, 4, 68, 'jmoWorkCenterID = ENGIN'),
    ('29128-01-01', 0, 10, '2023-05-03 08:36:21.977', 'ZACHUT', 502370, 4, 68, 'jmoWorkCenterID = ENGIN'),
    ('29128-01-01', 0, 10, '2023-05-03 08:36:21.977', 'ZACHUT', 502370, 5, 92, 'jmoProcessID = PERMI'),
    ('29128-01-01', 0, 10, '2023-05-03 08:36:21.977', 'ZACHUT', 502370, 6, 113, 'jmoProcessShortDescription = Permitting'),
    ('29128-01-01', 0, 10, '2023-05-03 08:36:21.977', 'ZACHUT', 502370, 6, 113, 'jmoProcessShortDescription = Permitting'),
    ('29128-01-01', 0, 10, '2023-05-03 08:36:21.977', 'ZACHUT', 502370, 7, 153, 'jmoQuantityPerAssembly = 1.000000'),
    ('29128-01-01', 0, 10, '2023-05-03 08:36:21.977', 'ZACHUT', 502370, 8, 187, 'jmoProductionStandard = 1.0000')
  ;
63 rows affected

/*  above are simulated results from:

; WITH JobOperationsChangeLog as (
SELECT --DISTINCT 
keys.JobID  JobID
, keys.JobAssemblyID JobAssemblyID
, keys.JobOperationID   JobOperationID

, xagChangeDate ChangeDate, xagChangeUserID ChangeUserID, ChangeLogID 

, xagChangeType --[U]pdate [I]nsert, etc
, xagTableKeyValues
, xagTableOldValues

, xagTableNewValues
FROM ChangeLog 
JOIN (
  SELECT distinct
  xagChangeLogID ChangeLogID, xagChangeDate ChangeDate

  , REPLACE(JobID.Value,'jmoJobID = ','') JobID
  , REPLACE(JobAssemblyID.Value,'jmoJobAssemblyID = ','') JobAssemblyID
  , REPLACE(JobOperationID.Value, 'jmoJobOperationID = ','') JobOperationID
  
  FROM ChangeLog

  OUTER APPLY M1_MA.dbo.RegexFind (
	@key_value_pair_regex_pat
	,xagTableKeyValues
	,1
	,1) JobID 

  OUTER APPLY M1_MA.dbo.RegexFind (
	@key_value_pair_regex_pat
	,xagTableKeyValues
	,1
	,1) JobAssemblyID 

  OUTER APPLY M1_MA.dbo.RegexFind (
	@key_value_pair_regex_pat
	,xagTableKeyValues
	,1
	,1) JobOperationID 

    ) keys on ChangeLogID=xagChangeLogID and ChangeDate=xagChangeDate
    WHERE xagTableName='JobOperations'
    AND (Keys.JobID NOT LIKE 'jmo%' and Keys.JobOperationID NOT LIKE 'jmo%' and Keys.JobAssemblyID NOT LIKE 'jmo%') -- eliminate non-value rows
    AND xagChangeUserID <> 'SQLAGENT - TSQL JOBS'
)
, inserts_BASE as (
    SELECT  
    JobID, JobAssemblyID, JobOperationID
    ,  ChangeDate FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    --,  old.Error , old.FirstIndex , old.Match_ID , old.Submatch_ID , old.SubmatchValue , old.[Value]
    ,  new.Match_ID     MatchID
    ,  new.FirstIndex   new_FirstIndex
    ,  new.Submatch_ID  new_SubmatchID  
    ,  new.[Value]      
    --,  new.SubmatchValue -- omit for distinct rows
    FirstValue
    FROM JobOperationsChangeLog
/*
    OUTER APPLY M1_MA.dbo.RegexFind (
        @key_value_pair_regex_pat
        ,xagTableOldValues
        ,1
        ,1) old 
*/
    OUTER APPLY M1_MA.dbo.RegexFind (
        @key_value_pair_regex_pat
        ,xagTableNewValues
        ,1
        ,1) new 
    
    WHERE xagChangeType='I'
    AND Match_ID NOT IN (1,2,3) -- eliminate rows matching key fields
)

*/
; WITH inserts_DISTINCT as (
    SELECT DISTINCT  -- returns 0 rows!
       JobID, JobAssemblyID, JobOperationID

    ,  FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    ,  Match_ID MatchID

    ,  FirstValue

    FROM ChangeLog -- inserts_BASE
)
, inserts_GROUP as (  -- returns 0 rows!
    SELECT          
        JobID, JobAssemblyID, JobOperationID
    ,  FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    ,  MIN(  FirstValue  )
    FirstValue
    ,  Match_ID MatchID
    -- ,  new_FirstIndex
    FROM ChangeLog -- inserts_BASE
    GROUP BY JobID, JobAssemblyID, JobOperationID
    , FirstDate, ChangeUserID, ChangeLogID, Match_ID -- MatchID
)
, inserts_OVER_PARTITION as (  -- returns 0 rows!
    SELECT          
        JobID, JobAssemblyID, JobOperationID
    ,  FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    ,  Match_ID MatchID

    ,  FIRST_VALUE (FirstValue) OVER( 
                PARTITION BY 
                  JobID, JobAssemblyID, JobOperationID
                 , FirstDate, ChangeUserID
                 , ChangeLogID, Match_ID -- MatchID
  
                ORDER BY ChangeLogID, Match_ID -- MatchID
        )
    AS FirstValue
    -- ,  new_FirstIndex
    FROM ChangeLog -- inserts_BASE
)

  /* not possible here, SubmatchID omitted from simulated results
  
, inserts_SUBMATCH as (
    SELECT  JobID, JobAssemblyID, JobOperationID
    , FirstDate, ChangeUserID, ChangeLogID
    -- , MatchID , new_FirstIndex , new_SubmatchID
    , FirstValue
    FROM ChangeLog -- inserts_BASE
    WHERE ISNULL(new_SubmatchID , 1 ) = 1 
    -- ^ only way to yeild distinct partition over JobID, JobAssemblyID, JobOperationID, ChangeDate, ChangeUserID, ChangeLogID, MatchID
)  
*/
  
/* TESTS */

-- SELECT * FROM ChangeLog

SELECT * FROM inserts_DISTINCT 
-- ^ works here but not in SQL Server WITH JobOperationsChangeLog > , inserts_BASE (empty resultset)

-- SELECT * from inserts_GROUP           
-- ^ works here but not in SQL Server WITH JobOperationsChangeLog > , inserts_BASE (empty resultset)

-- SELECT * from inserts_OVER_PARTITION  
-- does eliminate duplicates here! returns zero rows in SQL Server WITH JobOperationsChangeLog > , inserts_BASE (empty resultset)

-- SELECT * from inserts_SUBMATCH          
-- regex exploit that works but cannot be tested here due to missing SubmatchID in base table


JobID
JobAssemblyID
JobOperationID
FirstDate
ChangeUserID
ChangeLogID
MatchID
FirstValue


29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
4
jmoWorkCenterID = TRUCK

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
5
jmoProcessID = DELIV

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
6
jmoProcessShortDescription = Delivery to Job

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
7
jmoQuantityPerAssembly = 1.000000

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
8
jmoProductionStandard = 3.0000

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
9
jmoStandardFactor = TH

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
10
jmoSetupRate = 25.00

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
11
jmoProductionRate = 25.00

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
12
jmoOperationQuantity = 1.00000

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
13
jmoMachineType = 1

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
14
jmoUnitOfMeasure = EA

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
15
jmoFirm = 1

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
16
jmoEstimatedProductionHours = 3.00

29127-01-01
0
30
2023-05-03 08:12:11.583
PAMNEW
502294
17
jmoMachinesToSchedule = 1

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
4
jmoWorkCenterID = IWIND

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
5
jmoProcessID = IWIND

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
6
jmoProcessShortDescription = Window and Door Install

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
7
jmoQuantityPerAssembly = 1.000000

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
8
jmoSetupHours = 0.50

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
9
jmoStandardFactor = TH

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
10
jmoSetupRate = 25.00

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
11
jmoProductionRate = 25.00

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
12
jmoOperationQuantity = 1.00000

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
13
jmoMachineType = 1

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
14
jmoUnitOfMeasure = EA

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
15
jmoFirm = 1

29127-01-01
0
510
2023-05-03 08:12:11.583
PAMNEW
502295
16
jmoMachinesToSchedule = 1

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
4
jmoWorkCenterID = CRANE

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
5
jmoProcessID = CRANE

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
6
jmoProcessShortDescription = Crane Operator

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
7
jmoQuantityPerAssembly = 1.000000

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
8
jmoStandardFactor = MP

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
9
jmoOperationQuantity = 1.00000

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
10
jmoMachineType = 1

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
11
jmoUnitOfMeasure = EA

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
12
jmoFirm = 1

29127-01-01
0
520
2023-05-03 08:12:11.583
PAMNEW
502296
13
jmoMachinesToSchedule = 1

29128-01-01
0
10
2023-05-03 08:36:21.977
ZACHUT
502370
4
jmoWorkCenterID = ENGIN

29128-01-01
0
10
2023-05-03 08:36:21.977
ZACHUT
502370
5
jmoProcessID = PERMI

29128-01-01
0
10
2023-05-03 08:36:21.977
ZACHUT
502370
6
jmoProcessShortDescription = Permitting

29128-01-01
0
10
2023-05-03 08:36:21.977
ZACHUT
502370
7
jmoQuantityPerAssembly = 1.000000

29128-01-01
0
10
2023-05-03 08:36:21.977
ZACHUT
502370
8
jmoProductionStandard = 1.0000

JobID	JobOperationID	FirstDate	ChangeUserID	ChangeLogID	MatchID	FirstValue
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	4	jmoWorkCenterID = TRUCK
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	5	jmoProcessID = DELIV
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	6	jmoProcessShortDescription = Delivery to Job
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	7	jmoQuantityPerAssembly = 1.000000
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	8	jmoProductionStandard = 3.0000
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	9	jmoStandardFactor = TH
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	10	jmoSetupRate = 25.00
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	11	jmoProductionRate = 25.00
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	12	jmoOperationQuantity = 1.00000
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	13	jmoMachineType = 1
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	14	jmoUnitOfMeasure = EA
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	15	jmoFirm = 1
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	16	jmoEstimatedProductionHours = 3.00
29127-01-01	30	2023-05-03 08:12:11.583	PAMNEW	502294	17	jmoMachinesToSchedule = 1
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	4	jmoWorkCenterID = IWIND
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	5	jmoProcessID = IWIND
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	6	jmoProcessShortDescription = Window and Door Install
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	7	jmoQuantityPerAssembly = 1.000000
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	8	jmoSetupHours = 0.50
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	9	jmoStandardFactor = TH
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	10	jmoSetupRate = 25.00
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	11	jmoProductionRate = 25.00
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	12	jmoOperationQuantity = 1.00000
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	13	jmoMachineType = 1
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	14	jmoUnitOfMeasure = EA
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	15	jmoFirm = 1
29127-01-01	510	2023-05-03 08:12:11.583	PAMNEW	502295	16	jmoMachinesToSchedule = 1
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	4	jmoWorkCenterID = CRANE
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	5	jmoProcessID = CRANE
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	6	jmoProcessShortDescription = Crane Operator
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	7	jmoQuantityPerAssembly = 1.000000
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	8	jmoStandardFactor = MP
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	9	jmoOperationQuantity = 1.00000
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	10	jmoMachineType = 1
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	11	jmoUnitOfMeasure = EA
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	12	jmoFirm = 1
29127-01-01	520	2023-05-03 08:12:11.583	PAMNEW	502296	13	jmoMachinesToSchedule = 1
29128-01-01	10	2023-05-03 08:36:21.977	ZACHUT	502370	4	jmoWorkCenterID = ENGIN
29128-01-01	10	2023-05-03 08:36:21.977	ZACHUT	502370	5	jmoProcessID = PERMI
29128-01-01	10	2023-05-03 08:36:21.977	ZACHUT	502370	6	jmoProcessShortDescription = Permitting
29128-01-01	10	2023-05-03 08:36:21.977	ZACHUT	502370	7	jmoQuantityPerAssembly = 1.000000
29128-01-01	10	2023-05-03 08:36:21.977	ZACHUT	502370	8	jmoProductionStandard = 1.0000