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 please

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 @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
*/[/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.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
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
Go to Top of Page

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?
Go to Top of Page

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 @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
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.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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-25 : 05:45:04
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
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.
Go to Top of Page
   

- Advertisement -