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

Re: Need help in SQL query

Newsgroups	comp.databases.ms-sqlserver
Date	2014-03-11 05:27 -0700
References	<9c4cab96-dd12-4947-94ff-f1ed31b2baee@googlegroups.com>
Message-ID	<10f859de-eb1b-4693-958b-a96953fb8b36@googlegroups.com> (permalink)
Subject	Re: Need help in SQL query
From	Bal Govind <balgovindjss@gmail.com>

Show all headers | View raw

On Tuesday, September 11, 2012 7:11:34 PM UTC+5:30, Deepak wrote:
> 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


Try using Trigger on the table instead of this lengthy procedure :)

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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