Forums before death by AOL, social media and spammers... "We can't have nice things"
| comp.databases.ms-sqlserver | Notorious Rube Goldberg contraption | 19,505 messages |
[ << oldest | < older | list | newer > | newest >> ]
| Message 19,036 of 19,505 |
| Bal Govind to Deepak |
| Re: Need help in SQL query (1/2) |
| 11 Mar 14 05:27:01 |
From: balgovindjss@gmail.com
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','Nikk
','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','Niki
','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','vija
','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','sunn
','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,
>
[continued in next message]
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
(c) 1994, bbs@darkrealms.ca