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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query Help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

keka3309
Starting Member

11 Posts
Posted - 03/05/2013 :  10:54:19  Show Profile  Reply with Quote
Hello Everyone

using sqlserver 2005
I got the data in below format. I need to write sql to derive another new ID based on existing data.
can anyone please help

ID, ID2 ID1, Type (ID3 which i Need to Derive based on ID2 and Type)

100 1000 1 D 1000
100 1050 2 A 1000
100 1050 2 B 1000
100 1050 2 D 1050
100 1071 3 A 1050
100 1071 3 B 1050
100 1071 3 D 1071

using sqlserver 2005

James K
Flowing Fount of Yak Knowledge

1490 Posts
Posted - 03/05/2013 :  11:26:12  Show Profile  Reply with Quote
What is the rule that you are following to derive ID3? I looked at it for a few minutes, but I was not able to figure out the rule.
Go to Top of Page

keka3309
Starting Member

11 Posts
Posted - 03/05/2013 :  11:52:18  Show Profile  Reply with Quote
HI James K

Thanks for the response

We have to derive ID3 based on ID2 and Type.

The data in Type D,A,B are constants

Inorder to derive ID3 where ever there is A and B in Type we have to take the ID2 of D (Previous one)

for ex in the data below if you look at the ID3 column for type A and B(2nd and 3rd row) the value is set to 1000 which is ID of Type D

ID, ID2 ID1, Type (ID3 )

100 1000 1 D 1000
100 1050 2 A 1000
100 1050 2 B 1000
100 1050 2 D 1050
100 1071 3 A 1050
100 1071 3 B 1050
100 1071 3 D 1071
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1490 Posts
Posted - 03/05/2013 :  12:16:26  Show Profile  Reply with Quote
See if this example will work for you?
CREATE TABLE #tmp (ID2 INT, TYPE CHAR(1));
INSERT INTO #tmp VALUES (1000,'D'),(1050,'A'),(1050,'B'),(1050,'D'),
(1071,'A'),(1071,'B'),(1071,'D');

SELECT 
	a.*,
	CASE WHEN a.Type = 'D' THEN a.ID2 ELSE b.ID2 END AS ID3 
FROM 
	#tmp a
	OUTER APPLY
	(
		SELECT TOP (1) b.ID2
		FROM #tmp b
		WHERE b.ID2 < a.ID2
		ORDER BY ID2 DESC
	) b;
	
DROP TABLE #tmp;
Go to Top of Page

keka3309
Starting Member

11 Posts
Posted - 03/11/2013 :  09:41:48  Show Profile  Reply with Quote
Thanks James this worked for me
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.05 seconds. Powered By: Snitz Forums 2000