Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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

3873 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  
 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.08 seconds. Powered By: Snitz Forums 2000