SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
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 Previous Topic Topic Next Topic  

keka3309
Starting Member

11 Posts

Posted - 03/11/2013 :  10:01:46  Show Profile  Reply with Quote
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
2202 Posts

Posted - 03/11/2013 :  10:19:54  Show Profile  Reply with Quote

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 - 03/11/2013 :  11:20:04  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/11/2013 :  12:59:01  Show Profile  Reply with Quote
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 - 03/11/2013 :  13:37:27  Show Profile  Reply with Quote
Hi Vishakh,

This issue is not reposted

Thanks
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/12/2013 :  04:37:08  Show Profile  Reply with Quote

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

keka3309
Starting Member

11 Posts

Posted - 03/12/2013 :  06:01:51  Show Profile  Reply with Quote
Thanks Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/12/2013 :  07:02:46  Show Profile  Reply with Quote
quote:
Originally posted by keka3309

Thanks Chandu

Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.44 seconds. Powered By: Snitz Forums 2000