Groups > comp.databases.ms-sqlserver > #1270

Need help in SQL query

Newsgroups	comp.databases.ms-sqlserver
Date	2012-09-11 06:41 -0700
Message-ID	<9c4cab96-dd12-4947-94ff-f1ed31b2baee@googlegroups.com> (permalink)
Subject	Need help in SQL query
From	Dinesh <dinesht15@gmail.com>

Show all headers | View raw

Hi,

I am working on SQL queries to tarck the chnages in cutomer names and their details if any. Changes are stored in separte table "Cutomer". CHnages can be done through application.

Here is the create table script

CREATE TABLE [dbo].[customers](
	[LOGID] [bigint] IDENTITY(1,1) NOT NULL,
	[ACTN] [varchar](20) NOT NULL,
	[CUST_ID] [numeric](18, 0) NOT NULL,
	[FRST_NM] [varchar](50) NULL,
	[LAST_NM] [varchar](50) NULL,
	[CRTE_DT] [datetime] NULL,
	[CRTE_BY] [varchar](32) NULL,
	[UPD_DT] [datetime] NULL,
	[UPD_BY] [varchar](32) NULL,
)

Below is the insert script.

insert into customers values('119','INSERT','100','Niki','Boe','NULL','41163.4274312153','tvijay','NULL','NULL');
insert into customers values('120','BEFORE UPDATE','100','Niki','Boe','NULL','41163.4274312153','tvijay','NULL','NULL');
insert into customers values('121','AFTER UPDATE','100','Niki','Boe','NULL','41163.4274312153','tvijay','41163.4281475347','tvijay');
insert into customers values('122','BEFORE UPDATE','100','Niki','Boe','NULL','41163.4274312153','tvijay','41163.4281475347','tvijay');
insert into customers values('123','AFTER UPDATE','100','Nikki','Boe','NULL','41163.4274312153','tvijay','41163.4349319444','tvijay');
insert into customers values('124','BEFORE UPDATE','100','Nikki','Boe','NULL','41163.4274312153','tvijay','41163.4349319444','tvijay');
insert into customers values('125','AFTER UPDATE','100','Nikk','Boe','NULL','41163.4274312153','tvijay','41163.4352435532','tvijay');
insert into customers values('126','BEFORE UPDATE','100','Nikk','Boe','NULL','41163.4274312153','tvijay','41163.4352435532','tvijay');
insert into customers values('127','AFTER UPDATE','100','Niki','Boe','NULL','41163.4274312153','tvijay','41163.4355618403','tvijay');
insert into customers values('128','BEFORE UPDATE','100','Niki','Boe','NULL','41163.4274312153','tvijay','41163.4355618403','tvijay');
insert into customers values('129','AFTER UPDATE','100','Niki','Boe','33862','41163.4274312153','tvijay','41163.4772462153','tvijay');
insert into customers values('130','BEFORE UPDATE','100','Niki','Boe','33862','41163.4274312153','tvijay','41163.4772462153','tvijay');
insert into customers values('131','AFTER UPDATE','100','Niki','Boe','33862','41163.4274312153','tvijay','41163.4912398148','tvijay');
insert into customers values('132','BEFORE UPDATE','100','Niki','Boe','33862','41163.4274312153','tvijay','41163.4912398148','tvijay');
insert into customers values('133','AFTER UPDATE','100','Nikie','Boe','33862','41163.4274312153','tvijay','41163.5009896991','tvijay');
insert into customers values('134','BEFORE UPDATE','100','Nikie','Boe','33862','41163.4274312153','tvijay','41163.5009896991','tvijay');
insert into customers values('135','AFTER UPDATE','100','Niki','Boe','33862','41163.4274312153','tvijay','41163.5012021991','tvijay');
insert into customers values('136','BEFORE UPDATE','100','Niki','Boe','33862','41163.4274312153','tvijay','41163.5012021991','tvijay');
insert into customers values('137','AFTER UPDATE','100','ajay','Boe','33862','41163.4274312153','tvijay','41163.5190398495','tvijay');
insert into customers values('138','BEFORE UPDATE','100','ajay','Boe','33862','41163.4274312153','tvijay','41163.5190398495','tvijay');
insert into customers values('139','AFTER UPDATE','100','vijay','Boe','33862','41163.4274312153','tvijay','41163.519125544','tvijay');
insert into customers values('140','BEFORE UPDATE','100','vijay','Boe','33862','41163.4274312153','tvijay','41163.519125544','tvijay');
insert into customers values('141','AFTER UPDATE','100','sunny','Boe','33862','41163.4274312153','tvijay','41163.5192062153','tvijay');
insert into customers values('142','BEFORE UPDATE','100','sunny','Boe','33862','41163.4274312153','tvijay','41163.5192062153','tvijay');
insert into customers values('143','AFTER UPDATE','100','niki','Boe','33862','41163.4274312153','tvijay','41163.5192796296','tvijay');


I have written below stored procedure to get the data

create PROCEDURE cutomer_audit_report(@c_custid Numeric(18,0))  
AS  
BEGIN  
DECLARE @v_cmd VARCHAR(MAX) = ';WITH DATA  AS('  
IF EXISTS(SELECT 1 FROM sys.objects WHERE Name = 'TEMP' and TYPE = 'U')  
 DROP TABLE TEMP  
  
--Customer First , Last and Birth Day details  
 
 SELECT @v_cmd = @v_cmd + 'SELECT ROW_NUMBER() OVER(partition by CUST_ID ORDER BY UPD_DT) AS ID,CUST_ID,''LAST_NAME'' as FLD, 
 LAST_NM AS TYPE , UPD_DT,UPD_BY,CRTE_DT,CRTE_BY,1 AS ORD,ACTN AS [ACTIONS] 
 FROM customers WHERE CUST_ID =  '+ Convert(Varchar(20),@c_custid)+ ' AND ACTN <> ''AFTER UPDATE'''  

 SELECT @v_cmd = @v_cmd + ' UNION ALL SELECT ROW_NUMBER() OVER(partition by PM.CUST_ID ORDER BY PM.UPD_DT) AS ID,PM.CUST_ID,''LAST_NAME'' as FLD, 
 PM.LAST_NM AS TYPE , PM.UPD_DT,PM.UPD_BY,PM.CRTE_DT,PM.CRTE_BY,1 AS ORD,PM.ACTN AS [ACTIONS] 
 FROM customers PM INNER JOIN 
(
	SELECT CUST_ID, MAX(LOGID) AS MaxLOGID 
    FROM customers 
    WHERE ACTN = ''AFTER UPDATE''
    GROUP BY CUST_ID
) MAXLOGID ON PM.CUST_ID = MAXLOGID.CUST_ID AND PM.LOGID = MAXLOGID.MaxLOGID
WHERE PM.CUST_ID =  '+ Convert(Varchar(20),@c_custid)  
 
 SELECT @v_cmd = @v_cmd + ' UNION ALL SELECT ROW_NUMBER() OVER(partition by CUST_ID ORDER BY UPD_DT) AS ID,CUST_ID,''FIRST_NAME'' as FLD, 
 FRST_NM AS TYPE , UPD_DT,UPD_BY,CRTE_DT,CRTE_BY,2 AS ORD,ACTN AS [ACTIONS] 
 FROM customers  WHERE CUST_ID =  '+ Convert(Varchar(20),@c_custid) + ' AND ACTN <> ''AFTER UPDATE'''  
 
 SELECT @v_cmd = @v_cmd + ' UNION ALL SELECT ROW_NUMBER() OVER(partition by PM.CUST_ID ORDER BY PM.UPD_DT) AS ID,PM.CUST_ID,''FIRST_NAME'' as FLD, 
 PM.FRST_NM AS TYPE , PM.UPD_DT,PM.UPD_BY,PM.CRTE_DT,PM.CRTE_BY,2 AS ORD,PM.ACTN AS [ACTIONS] 
 FROM customers PM INNER JOIN 
(
	SELECT CUST_ID, MAX(LOGID) AS MaxLOGID 
    FROM customers 
    WHERE ACTN = ''AFTER UPDATE''
    GROUP BY CUST_ID
) MAXLOGID ON PM.CUST_ID = MAXLOGID.CUST_ID AND PM.LOGID = MAXLOGID.MaxLOGID 
WHERE PM.CUST_ID =  '+ Convert(Varchar(20),@c_custid)   
 
 SELECT @v_cmd = @v_cmd +' UNION ALL SELECT ROW_NUMBER() OVER(partition by CUST_ID ORDER BY UPD_DT) AS ID,CUST_ID,''BIRTH_DATE'' as FLD, 
 CONVERT(VARCHAR(30), BRTH_DT, 120)  AS TYPE, UPD_DT,UPD_BY,CRTE_DT,CRTE_BY ,3 AS ORD,ACTN AS [ACTIONS] 
 FROM customers WHERE CUST_ID =  '+ Convert(Varchar(20),@c_custid)+ ' AND ACTN <> ''AFTER UPDATE'''  
 
 SELECT @v_cmd = @v_cmd +' UNION ALL SELECT ROW_NUMBER() OVER(partition by PM.CUST_ID ORDER BY UPD_DT) AS ID,PM.CUST_ID,''BIRTH_DATE'' as FLD, 
 CONVERT(VARCHAR(30), PM.BRTH_DT, 120)  AS TYPE, PM.UPD_DT,PM.UPD_BY,PM.CRTE_DT,PM.CRTE_BY ,3 AS ORD,PM.ACTN AS [ACTIONS] 
 FROM customers PM INNER JOIN 
(
	SELECT CUST_ID, MAX(LOGID) AS MaxLOGID 
    FROM customers 
    WHERE ACTN = ''AFTER UPDATE''
    GROUP BY CUST_ID
) MAXLOGID ON PM.CUST_ID = MAXLOGID.CUST_ID AND PM.LOGID = MAXLOGID.MaxLOGID 
WHERE PM.CUST_ID =  '+ Convert(Varchar(20),@c_custid) 

SELECT @v_cmd = @v_cmd + ') SELECT ORD,(ROW_NUMBER() OVER(partition by CUST_ID ORDER BY ORD,UPD_DT)+ CUST_ID)AS ID,CUST_ID,FLD,TYPE,UPD_DT,
UPD_BY,CRTE_DT,CRTE_BY,[ACTIONS] INTO TEMP FROM DATA ORDER BY ORD,CUST_ID,UPD_DT ASC'  
print @v_cmd  
EXEC  (@v_cmd )  
  
 
SELECT  T1.CUST_ID,T1.FLD, CASE WHEN T1.ACTIONS ='INSERT' THEN NULL ELSE T1.[TYPE] END AS Previous_Value,ISNULL(T2.[TYPE] ,T1.[TYPE]) AS New_Value,   
 CASE WHEN T1.[TYPE] <> T2.[TYPE] THEN 'Changed' ELSE 'Unchanged' END AS [Status],  
 CASE WHEN T1.ACTIONS ='INSERT' THEN T1.CRTE_DT WHEN T1.[TYPE] <> T2.[TYPE]  THEN ISNULL(T2.UPD_DT,T2.CRTE_DT) ELSE NULL END AS UPD_DT,  
 CASE WHEN T1.ACTIONS ='INSERT' THEN T1.CRTE_BY WHEN T1.[TYPE] <> T2.[TYPE]  THEN ISNULL(T2.UPD_BY,T2.CRTE_BY) ELSE NULL END AS UPD_BY , T1.ID,T1.ORD  
INTO #TBL FROM TEMP T1 LEFT JOIN TEMP T2 ON T2.ID = (T1.ID+1) AND T2.FLD = T1.FLD  
ORDER BY T1.ORD,T1.CUST_ID,T1.ID  


 
SELECT A.RID,ID,CUST_ID,FLD,CASE WHEN Previous_Value = '' THEN NULL ELSE Previous_Value END AS Previous_Value ,CASE WHEN New_Value = '' THEN NULL ELSE New_Value END AS New_Value,[Status],UPD_DT ,UPD_BY INTO #TEMP1  
FROM (SELECT ROW_NUMBER() OVER(partition by FLD,Previous_Value,New_Value  ORDER BY ORD, ID  ) AS  RID, CUST_ID,FLD,Previous_Value,New_Value, [Status],  
 UPD_DT ,UPD_BY,ID,ORD FROM #TBL  
) AS A WHERE A.RID = 1 AND(Previous_Value <> '' OR New_Value <> '') 
ORDER BY A.ORD,A.ID,UPD_DT DESC  
  
DELETE FROM #TEMP1 WHERE ID IN (SELECT MAX(ID) FROM #TEMP1 GROUP BY FLD HAVING COUNT(1)>1)  --AND Previous_Value = New_Value
SELECT CUST_ID,FLD,PREVIOUS_VALUE,NEW_VALUE,STATUS,UPD_DT,UPD_BY FROM #TEMP1 ORDER BY ID  
  
DROP TABLE #TBL  
 
END  

exec [cutomer_audit_report] @c_custid = 100

But when I am executing the SP it is not returning the correct number of name chnages. I have chnaged first name from Niki to Nikki and then Nikki to Nikk and Nikk to Niki... Vijay to Sunny and Sunny to Niki.

But here Sunny to Niki change is not appearing in my query result. It is returning Niki to Niki as unchnaged row, which is not correct.

Any help will be apprictaed. 

Thanks
Dinesh

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Need help in SQL query Dinesh <dinesht15@gmail.com> - 2012-09-11 06:41 -0700
  Re: Need help in SQL query Erland Sommarskog <esquel@sommarskog.se> - 2012-09-11 23:24 +0200
  Re: Need help in SQL query Bal Govind <balgovindjss@gmail.com> - 2014-03-11 05:27 -0700

csiph-web