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-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

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

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/

keka3309
Starting Member

11 Posts

Posted - 2013-03-11 : 13:37:27

Hi Vishakh,

This issue is not reposted

Thanks

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

keka3309
Starting Member

11 Posts

Posted - 2013-03-12 : 06:01:51

Thanks Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 07:02:46