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.
Go to Top of Page

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
Go to Top of Page

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;
Go to Top of Page

keka3309
Starting Member

11 Posts

Posted - 2013-03-11 : 09:41:48
Thanks James this worked for me
Go to Top of Page
   

- Advertisement -