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 please
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 01/24/2013 :  06:47:55  Show Profile  Reply with Quote

DECLARE @tab TABLE(wm_id	int, lang_path int,	log_ts	datetime, log_change_type_cd char(1))
insert into @tab
SELECT 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        CurrSet
2	'2013-01-24 16:38:58.900'	4,7,9   	6
2	'2013-01-24 16:39:23.130'	6		6,9
*/


--
Chandu

Edited by - bandi on 01/24/2013 06:49:01

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/24/2013 :  08:33:08  Show Profile  Reply with Quote
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.sets
FROM
	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;
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 01/24/2013 :  09:28:52  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/24/2013 :  09:33:08  Show Profile  Reply with Quote
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?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 01/24/2013 :  09:54:35  Show Profile  Reply with Quote
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 @tab
SELECT 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 all
SELECT 2,	5,	'2013-01-24 16:43:18.493',	'I' union all

SELECT 2,	2,	'2013-01-24 16:46:42.277',	'D' union all	
SELECT 2,	4,	'2013-01-24 16:46:42.277',	'D' union all
SELECT 2,	5,	'2013-01-24 16:46:42.277',	'D' union all
SELECT 2,	9,	'2013-01-24 16:46:43.277',	'I' union all
SELECT 2,	3,	'2013-01-24 16:47:43.277',	'I' 

/*output:
2	'2013-01-24 16:38:58.900'	4,7,9	6
2	'2013-01-24 16:39:23.130'	6		6,9
2	'2013-01-24 16:43:18.493'	6,9		2,4,5
2	'2013-01-24 16:47:43.277'	2,4,5	9,3
*/	



--
Chandu
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/24/2013 :  11:09:07  Show Profile  Reply with Quote
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.sets
FROM
	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;
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 01/25/2013 :  05:45:04  Show Profile  Reply with Quote
Hi James,

Your solution is Working....
I have tried to optimize that query....

Alternatives Methods are:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=182416

Thank so much for your valuable time James........

--
Chandu
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/25/2013 :  07:57:36  Show Profile  Reply with Quote
You are welcome - glad to be of help; and thank you for that link.
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.06 seconds. Powered By: Snitz Forums 2000