Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Help

Author  Topic 

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-17 : 09:22:14
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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-17 : 09:42:57
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 23:03:15
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-17 : 23:20:49
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

52326 Posts

Posted - 2013-01-17 : 23:31:12
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-17 : 23:39:17
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

52326 Posts

Posted - 2013-01-17 : 23:42:11
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-17 : 23:57:20
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 00:01:52
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-18 : 00:07:32
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 00:21:21
[code]
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

[/code]

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

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-18 : 00:56:07
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

52326 Posts

Posted - 2013-01-18 : 01:17:14
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-18 : 04:27:17
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

52326 Posts

Posted - 2013-01-18 : 04:56:59
is it working as expected now?

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

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-18 : 05:50:41
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

52326 Posts

Posted - 2013-01-18 : 06:07:02
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-18 : 06:47:11
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 13:10:29
ok...no problem
you're welcome

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

Go to Top of Page
   

- Advertisement -