Using a PARTITION BY JOIN to Fill In The Gaps in Sparse Data

  • Hello

    Trying to convert an Oracle SQL which uses "partition" in Left outer join into SQL server as shown below.

    select v.table_name 
    , v.program_no
    , v.column_name
    , a.creation_timestamp
    , a.record_key
    , a.before_value
    , a.after_value
    from audit_010_v v
    left outer join audit_data a partition by (table_name, creation_timestamp, record_key)
    on a.table_name = v.table_name
    and a.column_name = v.column_name ;

    1. AUDIT_010_V : This table holds what all columns and tables name the report needs for Audit
    2. AUDIT_DATA. : This table holds all the DML operation happen on a table column, basically table and column name and BEFORE and AFTER value for that column with timestamp

    DDL is as follow:

    CREATE TABLE AUDIT_010_V 
    (
    TABLE_NAME nvarchar(100),
    PROGRAM_NO nvarchar(100),
    COLUMN_NAME nvarchar(100),
    SEQ_NO nvarchar(100)
    );

    INSERT INTO AUDIT_010_V VALUES ('MSF203','A','BRANCH_CODE','1');
    INSERT INTO AUDIT_010_V VALUES ('MSF203','A','BANK_ACCT_NO','2');
    INSERT INTO AUDIT_010_V VALUES ('MSF203','B','STMT_CLOSE_DAY','3');
    INSERT INTO AUDIT_010_V VALUES ('MSF203','B','NO_OF_DAYS_PAY','4');
    INSERT INTO AUDIT_010_V VALUES ('MSF203','B','INV_STMT_IND','5');
    INSERT INTO AUDIT_010_V VALUES ('MSF203','B','SETTLE_DISC','6');
    INSERT INTO AUDIT_010_V VALUES ('MSF203','B','SETTLE_DAYS','7');
    INSERT INTO AUDIT_010_V VALUES ('MSF203','B','SET_DISC_FLAG','8');
    INSERT INTO AUDIT_010_V VALUES ('MSF203','B','FRT_DEFAULT','9');


    CREATE TABLE AUDIT_DATA
    (
    TABLE_NAME nvarchar(100),
    COLUMN_NAME nvarchar(100),
    CREATION_TIMESTAMP nvarchar(100),
    RECORD_KEY nvarchar(100),
    BEFORE_VALUE nvarchar(100),
    AFTER_VALUE nvarchar(100)

    );

    INSERT INTO AUDIT_DATA VALUES
    ('MSF203','NO_OF_DAYS_PAY','09-JAN-2020 10.26.00','ARKL062784','30','60');


    INSERT INTO AUDIT_DATA VALUES
    ('MSF203','SET_DISC_FLAG','09-JUN-2020 11.06.00','MTNL020823',NULL,'R');

    INSERT INTO AUDIT_DATA VALUES
    ('MSF203','SETTLE_DAYS','09-JUN-2020 11.06.00','MTNL020823','0','20');

    INSERT INTO AUDIT_DATA VALUES
    ('MSF203','SETTLE_DISC','09-JUN-2020 11.06.00','MTNL020823','0','2');

    So for a given table name and a Record Key, display all Columns List, populate Before/After a value for the one which exists in AUDIT_DATA table else leave NULL.

    Also for the record where an entry doesn't exist in the AUDIT_DATA table, use CREATION_TIMESTAMP for backfill. On all the Grey cell columns there was no DML.

    Output needed is as below:

    1Capture

    • This topic was modified 3 years, 9 months ago by  getsaby.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • So, natively I don't think SQL Server has anything like this.  That being said, getting the result you list is not an impossible task in SQL Server.  Quick bit of thinking on this and came up with this (probably not that efficient) query:

    WITH [cte]
    AS
    (
    SELECT
    [v].[TABLE_NAME]
    , [v].[PROGRAM_NO]
    , [v].[COLUMN_NAME]
    , [a].[CREATION_TIMESTAMP]
    , [a].[RECORD_KEY]
    , .[BEFORE_VALUE]
    , .[AFTER_VALUE]
    FROM[AUDIT_010_V] AS [v]
    -- get timestamp and record key
    LEFT OUTER JOIN[dbo].[AUDIT_DATA] AS [a]
    ON [a].[TABLE_NAME] = [v].[TABLE_NAME]
    -- get before value and after value
    LEFT OUTER JOIN[dbo].[AUDIT_DATA] AS
    ON .[TABLE_NAME] = [v].[TABLE_NAME]
    AND .[COLUMN_NAME] = [v].[COLUMN_NAME]
    AND .[CREATION_TIMESTAMP] = [a].[CREATION_TIMESTAMP]
    )
    SELECTDISTINCT
    [cte].[TABLE_NAME]
    , [cte].[PROGRAM_NO]
    , [cte].[COLUMN_NAME]
    , [cte].[CREATION_TIMESTAMP]
    , [cte].[RECORD_KEY]
    , [cte].[BEFORE_VALUE]
    , [cte].[AFTER_VALUE]
    FROM[cte]
    ORDER BY[cte].[CREATION_TIMESTAMP]
    , [cte].[TABLE_NAME]
    , [cte].[COLUMN_NAME];

    Which, from a quick eyeball, looks like it is giving the same results as your query, just a slightly different ordering but the values are the same.

    Does that help?

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Thanks Mr. Gale it works somewhat, the final result displays each column defined in [AUDIT_010_V] by 9. so lots of DUPS.

  • Do you have some sample data where there are duplicates?  With the sample data you provided, the results appear identical to what you had in the screenshot.

    If I know some data that causes it to give you duplicates, I can work on fixing it.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply