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

bandi
Flowing Fount of Yak Knowledge

India
1390 Posts
Posted - 01/25/2013 :  00:09:56  Show Profile  Reply with Quote
Please help on this.........

INPUT:
wm_id	PreVal	                CurVal	        log_ts 	                 log_change_type_cd
2	Mainframe,SAP,Java	NULL	        2013-01-24 16:38:57.900	    D
2	NULL	                ETL-Database	2013-01-24 16:38:58.900	    I
2	ETL-Database	        NULL	        2013-01-24 16:39:22.130	    D
2	NULL	                ETL-Database,Java 2013-01-24 16:39:23.130	    I
2	ETL-Database,Java	NULL	          2013-01-24 16:41:18.493	    D
2	NULL	                Windows/.NET,Mainframe,Mac 2013-01-24 16:41:19.493	    I
2   Windows/.NET,Mainframe,Mac	NULL	          2013-01-24 16:46:42.277	    D
2	NULL	                Java,Unix/Linux	  2013-01-24 16:46:43.277	    I

OUTPUT:
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
Edited by - bandi on 01/25/2013 00:47:16

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 01/25/2013 :  00:37:16  Show Profile  Reply with Quote
if data is exactly as shown above, you can do this

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_ts
FROM table
GROUP BY wm_id,
log_ts


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 01/25/2013 :  00:53:40  Show Profile  Reply with Quote 

SELECT t.wm_id,
t.PreVal,
t1.CurrVal,
t1.log_ts
FROM table t
CROSS 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
             )t1
WHERE t.log_change_type_cd = 'D'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1390 Posts
Posted - 01/25/2013 :  01:07:18  Show Profile  Reply with Quote
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 @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: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 all
SELECT 2,	5,	'2013-01-24 16:41: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:42.277',	'I' union all
SELECT 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_cd
FROM 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_ts
FROM cte1
GROUP BY wm_id, log_ts




--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 01/25/2013 :  01:17:14  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1390 Posts
Posted - 01/25/2013 :  01:46:07  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 01/25/2013 :  03:23:06  Show Profile  Reply with Quote
whats lang_path_name etc? didnt understand STUFF logic. can there be multiple for same log_ts value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1390 Posts
Posted - 01/25/2013 :  04:14:39  Show Profile  Reply with Quote
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 @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  ----Newly inserted lang_path_id is 6

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 ----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 all
SELECT 2,	5,	'2013-01-24 16:41:19.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 ----Newly inserted lang_path_id is 9,3
SELECT 2,	3,	'2013-01-24 16:46:43.277',	'I'

/*output:
id	log_ts				DeletedRecordIds	InsertedIds
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
Edited by - bandi on 01/25/2013 04:20:52
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 01/25/2013 :  04:32:31  Show Profile  Reply with Quote
ok . Now I got reason for CTE.
One more way to do it is this


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  ----Newly inserted lang_path_id is 6

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 ----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 all
SELECT 2,	5,	'2013-01-24 16:41:19.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 ----Newly inserted lang_path_id is 9,3
SELECT 2,	3,	'2013-01-24 16:46:43.277',	'I'


;WIth CTE
AS
(
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 InsertedIds
FROM (SELECT DISTINCT wm_id,Rnk,MAX(log_ts) AS log_ts FROM CTE GROUP BY wm_id,Rnk) t


output
------------------------------------------------
wm_id	log_ts	DeletedId	InsertedIds
------------------------------------------------
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:41:19.493	6,9	2,4,5
2	2013-01-24 16:46:43.277	2,4,5	9,3


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1390 Posts
Posted - 01/25/2013 :  05:38:27  Show Profile  Reply with Quote
Thank You visakh

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 01/25/2013 :  05:50:24  Show Profile  Reply with Quote
You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.09 seconds. Powered By: Snitz Forums 2000