HELP - SQL Server Forums

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

HELP

Author

Topic

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-25 : 00:09:56

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 00:37:16

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 00:53:40

[code]
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'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-25 : 01:07:18

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 01:17:14

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/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-25 : 01:46:07

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 03:23:06

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/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-25 : 04:14:39

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 04:32:31

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