SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/17/2013 :  09:22:14  Show Profile  Reply with Quote
Hi everyone,
Please help me to get this

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

Like this there may be any number of columns in that wm_software table..
Purpose of this query is to get all updates of each column in wm_software table

Please help me
--
Chandu

Edited by - bandi on 01/17/2013 09:36:40

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/17/2013 :  09:42:57  Show Profile  Reply with Quote
Yes nameCol is Field name which value is changed during UPDATE..
There might be more than one column. Any column value may change
If Softwate_patch_id(another column) is changed, then output should contain that column name and also previous & current values of that column

Eg:

/****** 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

--
Chandu

Edited by - bandi on 01/17/2013 23:01:41
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/17/2013 :  23:03:15  Show Profile  Reply with Quote
Are you using SQL 2008 or above? If yes, you could use Change Data Capture to track changes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/17/2013 :  23:20:49  Show Profile  Reply with Quote
will it give which field is changed from above Log table?
If yes, please provide the direction

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/17/2013 :  23:31:12  Show Profile  Reply with Quote
quote:
Originally posted by bandi

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

--
Chandu


Oh ok
For that best way would be to use trigger and capture changed columns information using COLUMNS_UPDATED function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/17/2013 :  23:39:17  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/17/2013 :  23:42:11  Show Profile  Reply with Quote
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


ok..so do you've all the changed columns listed in log table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/17/2013 :  23:57:20  Show Profile  Reply with Quote
No. am getting the entire record with one or two field changes..... I don't have column name which is chaged....

Sample Data is:


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


--
Chandu

Edited by - bandi on 01/17/2013 23:58:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/18/2013 :  00:01:52  Show Profile  Reply with Quote
sorry you didnt get my point.
i was asking whether you've all columns which are part of data changes included in log table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/18/2013 :  00:07:32  Show Profile  Reply with Quote
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?


Yes

If Original Table has [request_id], [request_ts],[requestor_userid],[manager_userid],[originator_team_name],
[active_ind],[operating_system_txt] ,[operating_hardware_txt] ,[request_status_code] ,[last_change_userid] ,[last_change_ts] ,[wm_software_id] ,[open_system_support_ind] columns
then my log table also has all columns of original table and extra columns[log_ts],[log_change_type_cd]

--
Chandu

Edited by - bandi on 01/18/2013 00:11:13
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/18/2013 :  00:21:21  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/18/2013 :  00:56:07  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/18/2013 :  01:17:14  Show Profile  Reply with Quote
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


you should convert all of them to same datatype for unpivotting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/18/2013 :  04:27:17  Show Profile  Reply with Quote
Sorry for the late reply...
Yes i casted all unpivoted columns into same data type

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/18/2013 :  04:56:59  Show Profile  Reply with Quote
is it working as expected now?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/18/2013 :  05:50:41  Show Profile  Reply with Quote
yeah..
One more issue is:

Table: #temp ( with 5 columns) --> fieldName, PrevVal, CurVal, UpdatedBy, UpdatedTime
I am inserting result of one Procedure (usp_myProc) into #temp table
The problem is usp_myProc will give more than 5 columns data

How to limit number of columns of that Procedure?



--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/18/2013 :  06:07:02  Show Profile  Reply with Quote
Two options
1. Use an intermediate temporary table having structure same as sp result set to populate sp data. Then select required columns from it to #temp table
2. use distributed query option as below example 2

You should have turned on Adhoc distributed query option in your database using sp_configure before you use OPENROWSET as above

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 01/18/2013 :  06:47:11  Show Profile  Reply with Quote
Thanks Visakh...

Both ways are good. I have done that work in another way. I changed my prodecure to get required columns only by using dynamic Sql...

I will get back to u if there is any issues...


--
Chandu

Edited by - bandi on 01/18/2013 07:13:40
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/18/2013 :  13:10:29  Show Profile  Reply with Quote
ok...no problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000