Table: wm_Software
wm_software_id	nameCol
1		muni
2		chandu
3		abhi


Table: Software_Log
------------------
wm_software_id	log_ts	change_type	nameCol
1		2.00PM	I		muni
1		2.14PM	U		muni1
1		2.50PM	U		muni2
2		2.10PM	I		chandu
3		2.40PM	I		abhi


Output:
*********		
S.No	wm_software_id	FieldName	Previous_val	Current_Val	change_timestamp
1	1		nameCol		muni		muni1		2.14pm
2	1		nameCol		muni1		muni2		2.50pm

My query is:

;with cte as (SELECT *, ROW_NUMBER() OVER(partition by wm_software_id order by log_ts) rn FROM Software_Log)
SELECT c1.*, c2.nameCol 
FROM cte c1 JOIN cte c2 ON c1.rn = c2.rn+1 AND c1.wm_software_id = c2.wm_software_id

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [request_id]
      ,[log_ts]
      ,[log_change_type_cd]
      ,[request_ts]
      ,[requestor_userid]
      ,[manager_userid]
      ,[originator_team_name]
      ,[active_ind]
      ,[operating_system_txt]
      ,[operating_hardware_txt]
      ,[request_status_code]
      ,[create_userid]
      ,[create_ts]
      ,[last_change_userid]
      ,[last_change_ts]
      ,[business_case_txt]
      ,[unique_benefit_txt]
      ,[wm_software_id]
      ,[open_system_support_ind]
      ,[current_workflow_step_id]
      ,[request_comment_txt]
      ,[estimated_cost_type_code]
      ,[interoperability_rating_nbr]
      ,[multi_lang_path_ind]
      ,[process_rating_nbr]
      ,[isd_suitability_rating_nbr]
      ,[requestor_name]
      ,[requestor_email_addr_txt]
      ,[manager_name]
      ,[manager_email_addr_txt]
      ,[review_reqmt_ind]
  FROM dbo].[Procurement_Request_Log]
  
request_id	log_ts	log_change_type_cd	request_ts	requestor_userid	manager_userid	originator_team_name	active_ind	operating_system_txt	operating_hardware_txt	request_status_code	create_userid	create_ts	last_change_userid	last_change_ts	business_case_txt	unique_benefit_txt	wm_software_id	open_system_support_ind	current_workflow_step_id	request_comment_txt	estimated_cost_type_code	interoperability_rating_nbr	multi_lang_path_ind	process_rating_nbr	isd_suitability_rating_nbr	requestor_name	requestor_email_addr_txt	manager_name	manager_email_addr_txt	review_reqmt_ind
134	2013-01-17 17:02:38.413	I	2012-12-14 23:22:55.000	kavitha 	deepak  	Engineering	Y	asdf	asdf	7	kavitha 	2012-12-14 23:21:29.000	sasi    	2013-01-17 17:02:37.000	asdf	asdf	160	U	6	NULL	5	3	N	3	3	Kavitha V	kavitha_v@xxxx.com	Deepak RC	deepak_rc@xxx.com	NULL
134	2013-01-17 17:03:30.497	U	2012-12-14 23:22:55.000	kavitha 	deepak  	Engineering	Y	asdf123	asdf12	7	kavitha 	2012-12-14 23:21:29.000	sasi    	2013-01-17 17:03:29.000	asdf	asdf	160	U	6	NULL	5	3	N	3	3	Kavitha V	kavitha_v@xxxx.com	Deepak RC	deepak_rc@xxx.com	NULL
134	2013-01-17 19:32:41.440	U	2012-12-14 23:22:55.000	kavitha 	deepak  	Engineering	N	asdf123	asdf12	7	kavitha 	2012-12-14 23:21:29.000	sasi    	2013-01-17 18:02:01.000	asdf	asdf	160	U	6	NULL	5	3	N	3	3	Kavitha V	kavitha_v@xxx.com	Deepak RC	deepak_rc@xxx.com	NULL
214	2013-01-17 18:02:41.390	I	2013-01-17 18:02:41.000	shwetham	kavitha 	Software Testing	Y			1	sasi    	2013-01-17 11:49:22.000	sasi    	2013-01-17 18:02:41.000	dfgdfgdf		240	U	1		1	-1	U	-1	-1	shwetha M	shwetha_m@xxx.com	Kavitha V	kavitha_v@xxx.com	NULL


Output should be:
sno	request_id	fieldname	previousvalue	currentValue	log_ts
1	134		operating_system_txt	asdf	asdf123		2013-01-17 17:03:30.497
2	134		operating_hardware_txt	asdf	asdf12		2013-01-17 17:03:30.497
3	134		actve_ind		Y	N		2013-01-17 19:32:41.440

quote:

---

Originally posted by bandi

will it give which field is changed from above Log table?
If yes, please provide the direction

--
Chandu

---

quote:

---

Originally posted by bandi

Yes we are auditing DML operations(U, I) using Triggers....
From that tracked records i have to display previous and current values of each column in that log table

--
Chandu

---

DECLARE @tab TABLE (req_id int, log_ts varchar(10),  c_type char(1), active_ind char(1), os_text varchar(10))
insert into @tab
SELECT 13, '2:00am', 'I', 'N', 'asdf' union all
SELECT 13, '2.34am', 'u', 'y', 'asdf' union all
SELECT 13, '2.50am', 'u', 'y', 'asdf123' union all
select 14, '1:10am', 'I', 'y', 'aaa' 

SELECT * FROM @tab

OUTPUT:
sno	fieldName	preVal	CurVal	log_ts
1	active_ind	N	Y		2.34am
2	Os_text		asdf	asdf123	2,50am

quote:

---

Originally posted by visakh16

sorry you didnt get my point.
i was asking whether you've all columns which are part of data changes included in log table?

---

DECLARE @tab TABLE (req_id int, log_ts varchar(10),  c_type char(1), active_ind char(1), os_text varchar(10))
insert into @tab
SELECT 13, '2:00am', 'I', 'N', 'asdf' union all
SELECT 13, '2:34am', 'u', 'y', 'asdf' union all
SELECT 13, '2:50am', 'u', 'y', 'asdf123' union all
select 14, '1:10am', 'I', 'y', 'aaa' 

;With Log_CTE
AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY req_id,Col ORDER BY CAST(log_ts As time) ) AS seq
FROM (select req_id,log_ts,c_type,cast(active_ind as varchar(10)) AS active_ind,os_text from @tab)t
UNPIVOT (Val FOR Col IN (os_text,active_ind))u
)

select c1.req_id,c1.COl,c2.Val AS PreVal,c1.Val AS CurrVal,c1.log_ts
from Log_CTE c1
INNER JOIN Log_CTE c2
ON c2.seq=c1.seq-1
AND c2.req_id = c1.req_id
and c2.Col=c1.COl
AND c2.Val<> c1.Val



output
---------------------------------------------
req_id	COl	PreVal	CurrVal	log_ts
---------------------------------------------
13	active_ind	N	y	2:34am
13	os_text	asdf	asdf123	2:50am

quote:

---

Originally posted by bandi

Hi visakh, am getting this error for my original table...
url_txt is of type NVARCHAR(500)

Msg 8167, Level 16, State 1, Line 13
The type of column "url_txt" conflicts with the type of other columns specified in the UNPIVOT list.


--
Chandu

---