Author |
Topic |
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-25 : 00:09:56
|
Please help on this.........INPUT:wm_id PreVal CurVal log_ts log_change_type_cd2 Mainframe,SAP,Java NULL 2013-01-24 16:38:57.900 D2 NULL ETL-Database 2013-01-24 16:38:58.900 I2 ETL-Database NULL 2013-01-24 16:39:22.130 D2 NULL ETL-Database,Java 2013-01-24 16:39:23.130 I2 ETL-Database,Java NULL 2013-01-24 16:41:18.493 D2 NULL Windows/.NET,Mainframe,Mac 2013-01-24 16:41:19.493 I2 Windows/.NET,Mainframe,Mac NULL 2013-01-24 16:46:42.277 D2 NULL Java,Unix/Linux 2013-01-24 16:46:43.277 IOUTPUT:wm_id PreVal CurVal log_ts 2 Mainframe,SAP,Java ETL-Database 2013-01-24 16:38:58.900 2 ETL-Database ETL-Database,Java 2013-01-24 16:39:23.130 2 ETL-Database,Java Windows/.NET,Mainframe,Mac 2013-01-24 16:41:19.493 2 Windows/.NET,Mainframe,Mac Java,Unix/Linux 2013-01-24 16:46:43.277 EDIT: Hi visakh, Thank You... If the log_ts is different for each D and I, then how to do? please help--Chandu |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 00:37:16
|
if data is exactly as shown above, you can do thisSELECT wm_id,MAX(CASE WHEN log_change_type_cd = 'D' THEN PreVal END) AS PreVal,MAX(CASE WHEN log_change_type_cd = 'I' THEN CurVal END) AS CurVal,log_tsFROM tableGROUP BY wm_id,log_ts ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 00:53:40
|
[code]SELECT t.wm_id,t.PreVal,t1.CurrVal,t1.log_tsFROM table tCROSS APPLY (SELECT TOP 1 CurrVal FROM table WHERE log_ts> t.log_ts AND wm_id = t.wm_id AND log_change_type_cd = 'I' ORDER BY log_ts ASC )t1WHERE t.log_change_type_cd = 'D'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-25 : 01:07:18
|
Hi visakh,For the above result, i did as following.....Is this better code? May you reduce this code? T-SQL is also OK for me..DECLARE @tab TABLE(wm_id int, lang_path int, log_ts datetime, log_change_type_cd char(1))insert into @tabSELECT 2, 4, '2013-01-24 16:38:57.900', 'D' union all SELECT 2, 7, '2013-01-24 16:38:57.900', 'D' union all SELECT 2, 9, '2013-01-24 16:38:57.900', 'D' union all SELECT 2, 6, '2013-01-24 16:38:57.900', 'I' union all SELECT 2, 6, '2013-01-24 16:39:22.130', 'D' union all SELECT 2, 6, '2013-01-24 16:39:22.130', 'I' union all SELECT 2, 9, '2013-01-24 16:39:22.130', 'I' union all SELECT 2, 6, '2013-01-24 16:41:18.493', 'D' union all SELECT 2, 9, '2013-01-24 16:41:18.493', 'D' union all SELECT 2, 2, '2013-01-24 16:41:18.493', 'I' union all SELECT 2, 4, '2013-01-24 16:41:18.493', 'I' union allSELECT 2, 5, '2013-01-24 16:41:18.493', 'I' union allSELECT 2, 2, '2013-01-24 16:46:42.277', 'D' union all SELECT 2, 4, '2013-01-24 16:46:42.277', 'D' union allSELECT 2, 5, '2013-01-24 16:46:42.277', 'D' union allSELECT 2, 9, '2013-01-24 16:46:42.277', 'I' union allSELECT 2, 3, '2013-01-24 16:46:42.277', 'I';WITH cte AS( SELECT wm_id, Language_path_name, log_ts, log_change_type_cd FROM @tab t JOIN dbo.Language_Path_Txt l ON t.lang_path = l.Language_Path_code) , cte1 AS(SELECT wm_id, STUFF((SELECT ','+Language_path_name FROM cte c2 WHERE c1.log_ts = c2.log_ts AND c2.log_change_type_cd ='D' AND c1.log_change_type_cd='D' FOR XML PATH('')),1,1,'') PreVal, STUFF((SELECT ','+Language_path_name FROM cte c2 WHERE c1.log_ts = c2.log_ts AND c2.log_change_type_cd ='I' AND c1.log_change_type_cd='I' FOR XML PATH('')),1,1,'') CurVal, log_ts, log_change_type_cdFROM cte c1 GROUP BY wm_id, log_ts, log_change_type_cd)SELECT wm_id,MAX(CASE WHEN log_change_type_cd = 'D' THEN PreVal END) AS PreVal,MAX(CASE WHEN log_change_type_cd = 'I' THEN CurVal END) AS CurVal,log_tsFROM cte1GROUP BY wm_id, log_ts --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 01:17:14
|
whats the issue with code i posted?why two CTEs?Are the columns existing in multiple tables?You told log_ts is different then how can you group on it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-25 : 01:46:07
|
We have composite primary key on the columns wm_id, lang_path and log_ts... There is possibility to have this kind of data.SELECT 2, 6, '2013-01-24 16:39:22.130', 'D' SELECT 2, 6, '2013-01-24 16:39:22.130', 'I'Then your post(Posted - 01/25/2013 : 00:53:40) is working for me... I am asking for alternatives to optimize that query--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 03:23:06
|
whats lang_path_name etc? didnt understand STUFF logic. can there be multiple for same log_ts value?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-25 : 04:14:39
|
Hi,language_path_name is description of lang_path.. That is the reason i joined with language_PAth_Txt table ( in above query)DECLARE @tab TABLE(wm_id int, lang_path int, log_ts datetime, log_change_type_cd char(1))insert into @tabSELECT 2, 4, '2013-01-24 16:38:57.900', 'D' union all SELECT 2, 7, '2013-01-24 16:38:57.900', 'D' union all SELECT 2, 9, '2013-01-24 16:38:57.900', 'D' union all SELECT 2, 6, '2013-01-24 16:38:58.900', 'I' union all ----Newly inserted lang_path_id is 6SELECT 2, 6, '2013-01-24 16:39:22.130', 'D' union all SELECT 2, 6, '2013-01-24 16:39:23.130', 'I' union all ----Newly inserted lang_path_id is 6,9 SELECT 2, 9, '2013-01-24 16:39:23.130', 'I' union all SELECT 2, 6, '2013-01-24 16:41:18.493', 'D' union all SELECT 2, 9, '2013-01-24 16:41:18.493', 'D' union all SELECT 2, 2, '2013-01-24 16:41:19.493', 'I' union all ----Newly inserted lang_path_id is 2,4,5 SELECT 2, 4, '2013-01-24 16:41:19.493', 'I' union allSELECT 2, 5, '2013-01-24 16:41:19.493', 'I' union allSELECT 2, 2, '2013-01-24 16:46:42.277', 'D' union all SELECT 2, 4, '2013-01-24 16:46:42.277', 'D' union allSELECT 2, 5, '2013-01-24 16:46:42.277', 'D' union allSELECT 2, 9, '2013-01-24 16:46:43.277', 'I' union all ----Newly inserted lang_path_id is 9,3SELECT 2, 3, '2013-01-24 16:46:43.277', 'I'/*output:id log_ts DeletedRecordIds InsertedIds2 '2013-01-24 16:38:58.900' 4,7,9 62 '2013-01-24 16:39:23.130' 6 6,92 '2013-01-24 16:43:18.493' 6,9 2,4,52 '2013-01-24 16:47:43.277' 2,4,5 9,3*/ --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 04:32:31
|
ok . Now I got reason for CTE.One more way to do it is thisDECLARE @tab TABLE(wm_id int, lang_path int, log_ts datetime, log_change_type_cd char(1))insert into @tabSELECT 2, 4, '2013-01-24 16:38:57.900', 'D' union all SELECT 2, 7, '2013-01-24 16:38:57.900', 'D' union all SELECT 2, 9, '2013-01-24 16:38:57.900', 'D' union all SELECT 2, 6, '2013-01-24 16:38:58.900', 'I' union all ----Newly inserted lang_path_id is 6SELECT 2, 6, '2013-01-24 16:39:22.130', 'D' union all SELECT 2, 6, '2013-01-24 16:39:23.130', 'I' union all ----Newly inserted lang_path_id is 6,9 SELECT 2, 9, '2013-01-24 16:39:23.130', 'I' union all SELECT 2, 6, '2013-01-24 16:41:18.493', 'D' union all SELECT 2, 9, '2013-01-24 16:41:18.493', 'D' union all SELECT 2, 2, '2013-01-24 16:41:19.493', 'I' union all ----Newly inserted lang_path_id is 2,4,5 SELECT 2, 4, '2013-01-24 16:41:19.493', 'I' union allSELECT 2, 5, '2013-01-24 16:41:19.493', 'I' union allSELECT 2, 2, '2013-01-24 16:46:42.277', 'D' union all SELECT 2, 4, '2013-01-24 16:46:42.277', 'D' union allSELECT 2, 5, '2013-01-24 16:46:42.277', 'D' union allSELECT 2, 9, '2013-01-24 16:46:43.277', 'I' union all ----Newly inserted lang_path_id is 9,3SELECT 2, 3, '2013-01-24 16:46:43.277', 'I';WIth CTEAS(SELECT DENSE_RANK() OVER (PARTITION BY wm_id,log_change_type_cd ORDER BY log_ts) AS Rnk,*FROM @tab)SELECT wm_id,log_ts,STUFF((SELECT ',' + CAST(lang_path AS varchar(5)) FROM CTE WHERE wm_id = t.wm_id AND Rnk=t.Rnk AND log_change_type_cd= 'D' FOR XML PATH('')),1,1,'') AS DeletedId,STUFF((SELECT ',' + CAST(lang_path AS varchar(5)) FROM CTE WHERE wm_id = t.wm_id AND Rnk=t.Rnk AND log_change_type_cd= 'I' FOR XML PATH('')),1,1,'') AS InsertedIdsFROM (SELECT DISTINCT wm_id,Rnk,MAX(log_ts) AS log_ts FROM CTE GROUP BY wm_id,Rnk) toutput------------------------------------------------wm_id log_ts DeletedId InsertedIds------------------------------------------------2 2013-01-24 16:38:58.900 4,7,9 62 2013-01-24 16:39:23.130 6 6,92 2013-01-24 16:41:19.493 6,9 2,4,52 2013-01-24 16:46:43.277 2,4,5 9,3 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-25 : 05:38:27
|
Thank You visakh--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 05:50:24
|
You're welcome ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|