Experts,
I've sample tables with sample records as:-
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Entity]') AND type in (N'U'))
DROP TABLE [dbo].[Entity]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EntityOnlyChanges]') AND type in (N'U'))
DROP TABLE [dbo].[EntityOnlyChanges]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND type in (N'U'))
DROP TABLE [dbo].[Address]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddressOnlyChanges]') AND type in (N'U'))
DROP TABLE [dbo].[AddressOnlyChanges]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Claims]') AND type in (N'U'))
DROP TABLE [dbo].[Claims]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClaimsOnlyChanges]') AND type in (N'U'))
DROP TABLE [dbo].[ClaimsOnlyChanges]
GO
CREATE TABLE [dbo].[Entity]
(
BatchID INT,
ID INT,
FNAME VARCHAR(10),
LNAME VARCHAR(10),
GENDER VARCHAR(1),
FUNCTION_FLAG VARCHAR(1)
)
CREATE TABLE [dbo].[EntityOnlyChanges]
(
BatchID INT,
ID INT,
FNAME VARCHAR(10),
LNAME VARCHAR(10),
GENDER VARCHAR(1),
FUNCTION_FLAG VARCHAR(1)
)
INSERT INTO [dbo].[Entity] VALUES ( 1, 121, 'Mike', 'Ty', 'F', '' )
INSERT INTO [dbo].[Entity] VALUES ( 1, 222, 'Lance', 'May', 'M', '' )
INSERT INTO [dbo].[Entity] VALUES ( 1, 601, 'Will', 'Portz', 'M', '' )
INSERT INTO [dbo].[Entity] VALUES ( 1, 636, 'Abc', 'XYZ', 'M', '' )
INSERT INTO [dbo].[Entity] VALUES ( 2, 121, 'Mike', 'Ty', 'F', '' )
INSERT INTO [dbo].[Entity] VALUES ( 2, 222, 'Lance', 'May', 'M', '' )
INSERT INTO [dbo].[Entity] VALUES ( 2, 601, 'Will', 'Portz', 'F', '' )
INSERT INTO [dbo].[Entity] VALUES ( 2, 636, 'Abc', 'XYZ', 'M', '' )
CREATE TABLE [dbo].[Address]
(
BatchID INT,
ID INT,
AID INT,
ADD1 VARCHAR(35),
ST VARCHAR(2),
FUNCTION_FLAG VARCHAR(1)
)
INSERT INTO [dbo].[Address] VALUES ( 1, 121, 1210, '28th St', 'MO', '' )
INSERT INTO [dbo].[Address] VALUES ( 1, 222, 2220, '56th St', 'IL', '' )
INSERT INTO [dbo].[Address] VALUES ( 1, 222, 2221, '76 Bvld', 'IL', '' )
INSERT INTO [dbo].[Address] VALUES ( 1, 601, 6010, '601 Main Rd', 'NJ', '' )
INSERT INTO [dbo].[Address] VALUES ( 1, 636, 6360, '45th St', 'CA', '' )
INSERT INTO [dbo].[Address] VALUES ( 1, 636, 6361, '5th St', 'CA', '' )
INSERT INTO [dbo].[Address] VALUES ( 2, 121, 1210, '28th St', 'MO', '' )
INSERT INTO [dbo].[Address] VALUES ( 2, 222, 2220, '56th St', 'IL', '' )
INSERT INTO [dbo].[Address] VALUES ( 2, 222, 2221, '86 Bvld', 'IL', '' )
INSERT INTO [dbo].[Address] VALUES ( 2, 601, 6010, '601 Main Rd', 'NJ', '' )
INSERT INTO [dbo].[Address] VALUES ( 2, 636, 6360, '45th St', 'CA', '' )
INSERT INTO [dbo].[Address] VALUES ( 2, 636, 6361, '6th St', 'CA', '' )
CREATE TABLE [dbo].[AddressOnlyChanges]
(
BatchID INT,
ID INT,
AID INT,
ADD1 VARCHAR(35),
ST VARCHAR(2),
FUNCTION_FLAG VARCHAR(1)
)
CREATE TABLE [dbo].[Claims]
(
BatchID INT,
ID INT,
AID INT,
Phone VARCHAR(10),
Ext VARCHAR(5),
FUNCTION_FLAG VARCHAR(1)
)
INSERT INTO [dbo].[Claims] VALUES ( 2, 121, 1210, '1234567890', '10095', '' )
INSERT INTO [dbo].[Claims] VALUES ( 2, 636, 6360, '5420567890', '14298', '' )
INSERT INTO [dbo].[Claims] VALUES ( 2, 636, 6361, '5558567890', '22232', '' )
INSERT INTO [dbo].[Claims] VALUES ( 1, 601, 6010, '5420151890', '22246', '' )
INSERT INTO [dbo].[Claims] VALUES ( 2, 121, 1210, '1234567890', '15295', '' )
INSERT INTO [dbo].[Claims] VALUES ( 2, 636, 6360, '5420567890', '14298', '' )
INSERT INTO [dbo].[Claims] VALUES ( 2, 636, 6361, '5558567890', '19532', '' )
INSERT INTO [dbo].[Claims] VALUES ( 2, 601, 6010, '5420151890', '15846', '' )
CREATE TABLE [dbo].[ClaimsOnlyChanges]
(
BatchID INT,
ID INT,
AID INT,
Phone VARCHAR(10),
Ext VARCHAR(5),
FUNCTION_FLAG VARCHAR(1)
)
SELECT *
FROM [dbo].[Entity]
ORDER BY BatchID, ID
SELECT *
FROM [dbo].[Address]
ORDER BY BatchID, ID
SELECT *
FROM [dbo].[Claims]
ORDER BY BatchID, ID
INSERT INTO [dbo].[EntityOnlyChanges] VALUES ( 2, 601, 'Will', 'Portz', 'F', 'C' )
INSERT INTO [dbo].[AddressOnlyChanges] VALUES ( 2, 222, 2221, '86 Bvld', 'IL', 'C' )
INSERT INTO [dbo].[AddressOnlyChanges] VALUES ( 2, 636, 6361, '6th St', 'CA', 'C' )
INSERT INTO [dbo].[ClaimsOnlyChanges] VALUES ( 2, 121, 1210, '1234567890', '15295', 'C' )
INSERT INTO [dbo].[ClaimsOnlyChanges] VALUES ( 2, 636, 6360, '5420567890', '14298', 'C' )
INSERT INTO [dbo].[ClaimsOnlyChanges] VALUES ( 2, 636, 6361, '5558567890', '19532', 'C' )
SELECT *
FROM [dbo].[EntityOnlyChanges]
ORDER BY BatchID, ID
SELECT *
FROM [dbo].[AddressOnlyChanges]
ORDER BY BatchID, ID
SELECT *
FROM [dbo].[ClaimsOnlyChanges]
ORDER BY BatchID, ID
EXEC [dbo].[procGetChanges_Temp1] 'Entity,Address, Claims ', 'ID', 'AID', 2
SELECT *
FROM [dbo].[EntityOnlyChanges]
ORDER BY CASE WHEN FUNCTION_FLAG = 'C' THEN 1 ELSE 2 END, ID
SELECT *
FROM [dbo].[AddressOnlyChanges]
ORDER BY CASE WHEN FUNCTION_FLAG = 'C' THEN 1 ELSE 2 END, ID
SELECT *
FROM [dbo].[ClaimsOnlyChanges]
ORDER BY CASE WHEN FUNCTION_FLAG = 'C' THEN 1 ELSE 2 END, ID
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Entity]') AND type in (N'U'))
DROP TABLE [dbo].[Entity]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EntityOnlyChanges]') AND type in (N'U'))
DROP TABLE [dbo].[EntityOnlyChanges]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND type in (N'U'))
DROP TABLE [dbo].[Address]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddressOnlyChanges]') AND type in (N'U'))
DROP TABLE [dbo].[AddressOnlyChanges]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Claims]') AND type in (N'U'))
DROP TABLE [dbo].[Claims]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClaimsOnlyChanges]') AND type in (N'U'))
DROP TABLE [dbo].[ClaimsOnlyChanges]
GO
Stored Procedure to get "Same" (S) values is here,
CREATE PROCEDURE [dbo].[procGetChanges_Temp1]
(
@TableNames VARCHAR(MAX),
@PrimaryKeyColumn VARCHAR(255),
@PrimaryKeyColumn1 VARCHAR(255),
@LatestIsReadyImportImportBatchID INT
) AS
BEGIN
DECLARE @TblName VARCHAR(MAX) = @TableNames,
@Split CHAR(1) = ',',
@TblList XML
SELECT @TblList = CONVERT(XML,' <root> <s>' + REPLACE(@TblName,@Split,'</s> <s>') + '</s> </root> ')
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS ID,
[TablesList] = LTRIM(RTRIM(T.c.value('.','VARCHAR(30)')))
FROM @TblList.nodes('/root/s') T(c)
INSERT INTO [dbo].[EntityOnlyChanges] ( BatchID, ID, FNAME, LNAME, GENDER, FUNCTION_FLAG )
SELECT BatchID, ID, FNAME, LNAME, GENDER, FUNCTION_FLAG
FROM
(
SELECT BatchID, ID, FNAME, LNAME, GENDER, 'S' AS FUNCTION_FLAG
FROM [dbo].[Entity]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND ID IN
(
SELECT ID
FROM [dbo].[AddressOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND FUNCTION_FLAG = 'C'
AND ID NOT IN
(
SELECT ID
FROM [dbo].[EntityOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
)
)
UNION ALL
SELECT BatchID, ID, FNAME, LNAME, GENDER, 'S' AS FUNCTION_FLAG
FROM [dbo].[Entity]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND ID IN
(
SELECT ID
FROM [dbo].[ClaimsOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND FUNCTION_FLAG = 'C'
AND ID NOT IN
(
SELECT ID
FROM [dbo].[EntityOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
)
)
) EntityOnlyChanges
GROUP BY BatchID, ID, FNAME, LNAME, GENDER, FUNCTION_FLAG
ORDER BY BatchID, ID
INSERT INTO [dbo].[AddressOnlyChanges] ( BatchID, ID, AID, ADD1, ST, FUNCTION_FLAG )
SELECT BatchID, ID, AID, ADD1, ST, FUNCTION_FLAG
FROM
(
SELECT BatchID, ID, AID, ADD1, ST, 'S' AS FUNCTION_FLAG
FROM [dbo].[Address]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND ID IN
(
SELECT ID
FROM [dbo].[EntityOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND FUNCTION_FLAG = 'C'
AND ID NOT IN
(
SELECT ID
FROM [dbo].[AddressOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
)
)
UNION ALL
SELECT BatchID, ID, AID, ADD1, ST, 'S' AS FUNCTION_FLAG
FROM [dbo].[Address]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND AID IN
(
SELECT AID
FROM [dbo].[ClaimsOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND FUNCTION_FLAG = 'C'
AND AID NOT IN
(
SELECT AID
FROM [dbo].[AddressOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
)
)
) AddressOnlyChanges
GROUP BY BatchID, ID, AID, ADD1, ST, FUNCTION_FLAG
ORDER BY BatchID, ID
INSERT INTO [dbo].[ClaimsOnlyChanges] ( BatchID, ID, AID, Phone, Ext, FUNCTION_FLAG )
SELECT BatchID, ID, AID, Phone, Ext, FUNCTION_FLAG
FROM
(
SELECT BatchID, ID, AID, Phone, Ext, 'S' AS FUNCTION_FLAG
FROM [dbo].[Claims]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND ID IN
(
SELECT ID
FROM [dbo].[EntityOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND FUNCTION_FLAG = 'C'
AND ID NOT IN
(
SELECT ID
FROM [dbo].[ClaimsOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
)
)
UNION ALL
SELECT BatchID, ID, AID, ADD1, ST, 'S' AS FUNCTION_FLAG
FROM [dbo].[Address]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND AID IN
(
SELECT AID
FROM [dbo].[AddressOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
AND FUNCTION_FLAG = 'C'
AND AID NOT IN
(
SELECT AID
FROM [dbo].[ClaimsOnlyChanges]
WHERE BatchID = @LatestIsReadyImportImportBatchID
)
)
) AddressOnlyChanges
GROUP BY BatchID, ID, AID, Phone, Ext, FUNCTION_FLAG
ORDER BY BatchID, ID
END
GO
I'm able to get my expected result set with the above SP, but I want to make the above SP dynamic so that if number of tables get increase I don't have to modify the SP currently (above) SP is very much static with can handle only 3 tables.
Can anybody please help me out?
Please let me know if you have any questions for me.
Note :-
Entity table - One record per individual
Address table - Each individual can have multiple address
Claims table - If multiple address per individual multiple claims entries as well
Thanks in advance
Regards,
Kumar
Please do let us know your feedback. Thank You - KG, MCTS
No comments:
Post a Comment