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

Question

Post reply

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

getsaby

Old Hand

Points: 332

June 13, 2020 at 3:42 pm

#3762846

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 ;

AUDIT_010_V : This table holds what all columns and tables name the report needs for Audit
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 4 years, 5 months ago by getsaby.

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

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.

getsaby Old Hand Points: 332 More actions · Answer 3

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

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.