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-11 : 10:01:46
Hi All,

Need some help in writing a query for the below sample data

Column A,B,C are my tables columns
and D ad E need to be derived from A,B and C.

A B C D E
2 14 13.5 N 14
2 14 13.5 N 14
2 14 14 CM 14
2 14 16 N 14

3 14 13.5 N 14
3 14 13.5 N 14
3 14 13.5 N 14
3 14 13.5 N 14
3 14 14 CM 14
3 14 14 CM 14
3 14 16 N 14
3 14 16 N 14

6 14 13.5 N 14
6 14 13.5 N 14
6 14 14 CM 14
6 14 16 N 14

I have to calculate close match for column B by comapring the values in C and populate D as CM(Close match) and display the close match value in E.

For Ex: for A=2 the value for B is 14. The close match for this in column C is 14 so i have to populate CM in D and in E display the value 14 for A=2


Your help is much appreciated

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

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

SELECT A, B, C, CASE WHEN MIN(ABS( B-C)) OVER( PARTITION BY A) = ABS( B-C) THEN 'CM' ELSE 'N' END AS D
FROM @t

--
Chandu
Go to Top of Page

keka3309
Starting Member

11 Posts

Posted - 2013-03-11 : 11:20:04
Thanks Chandu for the quick response now im able to get the column D but how should i display the value in column E
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-11 : 12:59:01
please dont open multiple threads for same issue

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

Go to Top of Page

keka3309
Starting Member

11 Posts

Posted - 2013-03-11 : 13:37:27
Hi Vishakh,

This issue is not reposted

Thanks
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 04:37:08
[code]
SELECT A, B, C,
CASE WHEN Rn=1 THEN 'CM' ELSE 'N' END AS D,
MAX( CASE WHEN Rn = 1 THEN B END) OVER(PARTITION BY A) E
FROM (SELECT A, B, C, DENSE_RANK() OVER(PARTITION BY A ORDER BY ABS(B-C)) RN
FROM @t) temp[/code]

--
Chandu
Go to Top of Page

keka3309
Starting Member

11 Posts

Posted - 2013-03-12 : 06:01:51
Thanks Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 07:02:46
quote:
Originally posted by keka3309

Thanks Chandu

Welcome

--
Chandu
Go to Top of Page
   

- Advertisement -