Author |
Topic |
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-24 : 06:47:55
|
[code]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', 'U' 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', 'U' union all SELECT 2, 9, '2013-01-24 16:39:23.130', 'U'/*output:wm_id Log_ts PrevSet CurrSet2 '2013-01-24 16:38:58.900' 4,7,9 62 '2013-01-24 16:39:23.130' 6 6,9*/[/code]--Chandu |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-24 : 08:33:08
|
Here is something to get you started - may not be the simplest or most concise. If this does not seem to work or is too sluggish, reply? ;WITH cte AS( SELECT *, ROW_NUMBER() OVER (ORDER BY log_ts ASC) -ROW_NUMBER() OVER (PARTITION BY wm_id, log_change_type_cd,log_change_type_cd ORDER BY log_ts ASC) N FROM @tab),cte2 AS( SELECT wm_id, log_change_type_cd, MAX(log_ts) log_ts, c2.sets, ROW_NUMBER() OVER (ORDER BY MAX(log_ts)) AS N FROM cte c1 OUTER APPLY ( SELECT ',' AS [text()],lang_path AS [text()] FROM cte c2 WHERE c2.N = c1.N FOR XML PATH('') )c2(Sets) GROUP BY N, log_change_type_cd, wm_id, c2.sets) SELECT a.wm_id, a.log_ts, b.sets, a.setsFROM cte2 a INNER JOIN cte2 b ON a.wm_id = b.wm_id AND a.log_change_type_cd = 'U' AND b.log_change_type_cd = 'd' AND a.N = b.N+1; |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-24 : 09:28:52
|
Hi, Your solution is somewhat fine... but i may have different wm_ids and also many number of sets. This is not working for me...--Chandu |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-24 : 09:33:08
|
I figured you might have more than one wm_id; thought I had taken care of that, but didn't test that aspect of it. Can you post a sample which has more than wm_id that is giving the incorrect results? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-01-24 : 09:54:35
|
Hi James,Please provide me solution for this... I can pass WM_id as input... no problem... For single wm_id also, it is giving incorrect results. please help me on this.Here I means Insert D means Deleted..There are four sets... 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 SELECT 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 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:43:18.493', 'I' union all SELECT 2, 4, '2013-01-24 16:43:18.493', 'I' union allSELECT 2, 5, '2013-01-24 16:43: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:43.277', 'I' union allSELECT 2, 3, '2013-01-24 16:47:43.277', 'I' /*output: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:43:18.493' 6,9 2,4,52 '2013-01-24 16:47:43.277' 2,4,5 9,3*/ --Chandu |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-24 : 11:09:07
|
Sorry about that Chandu, I had missed to join on the change type code also. Do you have three codes 'U','I' and 'D'?;WITH cte AS( SELECT *, ROW_NUMBER() OVER (ORDER BY log_ts ASC) -ROW_NUMBER() OVER (PARTITION BY wm_id, log_change_type_cd,log_change_type_cd ORDER BY log_ts ASC) N FROM @tab),cte2 AS( SELECT wm_id, log_change_type_cd, MAX(log_ts) log_ts, c2.sets, ROW_NUMBER() OVER (ORDER BY MAX(log_ts)) AS N FROM cte c1 OUTER APPLY ( SELECT ',' AS [text()],lang_path AS [text()] FROM cte c2 WHERE c2.N = c1.N AND c2.log_change_type_cd = c1.log_change_type_cd FOR XML PATH('') )c2(Sets) GROUP BY N, log_change_type_cd, wm_id, c2.sets) SELECT a.wm_id, a.log_ts, b.sets, a.setsFROM cte2 a INNER JOIN cte2 b ON a.wm_id = b.wm_id AND a.log_change_type_cd = 'I' AND b.log_change_type_cd = 'd' AND a.N = b.N+1; |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-25 : 07:57:36
|
You are welcome - glad to be of help; and thank you for that link. |
|
|
|
|
|