Author |
Topic |
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-17 : 09:22:14
|
Hi everyone,Please help me to get thisTable: wm_Softwarewm_software_id nameCol1 muni2 chandu3 abhiTable: Software_Log------------------wm_software_id log_ts change_type nameCol1 2.00PM I muni1 2.14PM U muni11 2.50PM U muni22 2.10PM I chandu3 2.40PM I abhiOutput:********* S.No wm_software_id FieldName Previous_val Current_Val change_timestamp1 1 nameCol muni muni1 2.14pm2 1 nameCol muni1 muni2 2.50pmMy 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 tablePlease 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 changeIf Softwate_patch_id(another column) is changed, then output should contain that column name and also previous & current values of that columnEg:/****** 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_ind134 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 NULL134 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 NULL134 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 NULL214 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 NULLOutput should be:sno request_id fieldname previousvalue currentValue log_ts1 134 operating_system_txt asdf asdf123 2013-01-17 17:03:30.4972 134 operating_hardware_txt asdf asdf12 2013-01-17 17:03:30.4973 134 actve_ind Y N 2013-01-17 19:32:41.440 --Chandu |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 okFor that best way would be to use trigger and capture changed columns information using COLUMNS_UPDATED function------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 @tabSELECT 13, '2:00am', 'I', 'N', 'asdf' union allSELECT 13, '2.34am', 'u', 'y', 'asdf' union allSELECT 13, '2.50am', 'u', 'y', 'asdf123' union allselect 14, '1:10am', 'I', 'y', 'aaa' SELECT * FROM @tabOUTPUT:sno fieldName preVal CurVal log_ts1 active_ind N Y 2.34am2 Os_text asdf asdf123 2,50am --Chandu |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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?
YesIf 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 |
|
|
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 @tabSELECT 13, '2:00am', 'I', 'N', 'asdf' union allSELECT 13, '2:34am', 'u', 'y', 'asdf' union allSELECT 13, '2:50am', 'u', 'y', 'asdf123' union allselect 14, '1:10am', 'I', 'y', 'aaa' ;With Log_CTEAS(SELECT *,ROW_NUMBER() OVER (PARTITION BY req_id,Col ORDER BY CAST(log_ts As time) ) AS seqFROM (select req_id,log_ts,c_type,cast(active_ind as varchar(10)) AS active_ind,os_text from @tab)tUNPIVOT (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_tsfrom Log_CTE c1INNER JOIN Log_CTE c2ON c2.seq=c1.seq-1AND c2.req_id = c1.req_idand c2.Col=c1.COlAND c2.Val<> c1.Valoutput---------------------------------------------req_id COl PreVal CurrVal log_ts---------------------------------------------13 active_ind N y 2:34am13 os_text asdf asdf123 2:50am[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 13The type of column "url_txt" conflicts with the type of other columns specified in the UNPIVOT list.--Chandu |
|
|
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 13The 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-18 : 04:56:59
|
is it working as expected now?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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, UpdatedTimeI am inserting result of one Procedure (usp_myProc) into #temp tableThe problem is usp_myProc will give more than 5 columns dataHow to limit number of columns of that Procedure?--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-18 : 13:10:29
|
ok...no problemyou're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|