| Author |
 Topic  |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
 Posted - 01/24/2013 : 06:47:55
|
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
1515 Posts |
Posted - 01/24/2013 : 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; |
 |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 01/24/2013 : 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
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/24/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 01/24/2013 : 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 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/24/2013 : 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; |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 01/25/2013 : 07:57:36
|
| You are welcome - glad to be of help; and thank you for that link. |
|
|
| |
Topic |
|
|
|
Query Help please
