Query 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

SQL Server 2012 Forums

Transact-SQL (2012)

Query Help

Author

Topic

keka3309
Starting Member

11 Posts

Posted - 2013-03-05 : 10:54:19

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-05 : 11:26:12

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.

keka3309
Starting Member

11 Posts

Posted - 2013-03-05 : 11:52:18

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-05 : 12:16:26

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;

keka3309
Starting Member

11 Posts

Posted - 2013-03-11 : 09:41:48

Thanks James this worked for me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources