SQL Server Forums - Query Help

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

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

New Topic

Reply to Topic

Printer Friendly

Author

Topic

keka3309
Starting Member

11 Posts

Posted - 03/11/2013 : 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
Flowing Fount of Yak Knowledge

India
1390 Posts

Posted - 03/11/2013 : 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 - 03/11/2013 : 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

India
47023 Posts

Posted - 03/11/2013 : 12:59:01

please dont open multiple threads for same issue

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

keka3309
Starting Member

11 Posts

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

Hi Vishakh,

This issue is not reposted

Thanks

bandi
Flowing Fount of Yak Knowledge

India
1390 Posts

Posted - 03/12/2013 : 04:37:08


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

--
Chandu

keka3309
Starting Member

11 Posts

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

Thanks Chandu

bandi
Flowing Fount of Yak Knowledge

India
1390 Posts

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