1

I am working on a SQL query which performs some calculations and returns difference of two columns that belongs to two different rows of single table when certain values in the other columns are not equal

For Example I have the following data in a table

id      Market  Grade       Term     Bid     Offer  CP
1       Heavy   ABC         Jun14   -19.5     -17   BA
2       Heavy   ABC         Jul14   -20      -17.5  BB
3       Sour    XYZ         Jun14   -30       -17   NULL
4       Sour    XYZ         Jul14   -32       -27   NULL
5       Sweet   XY          Jun14   -30       -17   PV
6       Sweet   XY          Jul14   -32       -27   PV

Now, I want the following results

(AS Market and Grade are same and CP are not same for Id=1,2 So, it should calculate

Bid of Id=1 - Offer of Id=2
Offer of Id=1- Bid of Id=2

(AS Market and Grade are same for Id=3,4 and also their CP are both NULL logically but I still want to calculate as I did in the previous case 

Bid of Id=3 - Offer of Id=4
Offer of Id=3- Bid of Id=4

And, Finally I dont want to calculate anything for record with Ids 5 and 6 as their CPs are same

Something Like the following should be the result

Market     Term          Bid                   Offer
Heavy/ABC  Jun14/Jul14   (-19.5-(-17.5))=-2    (-17-(-20))=3
Sour/XYZ  Jun14/Jul14    (-30-(-27))=-3        (-17-(-32))=15

I was able to figure out most of this except the case when CPs are two records are NULL as it is treating them as equal which is obvious

;with numbered as
(
    select id, market, grade, term, bid, offer, row_number() OVER (Partition BY Market, Grade ORDER BY Bid desc) i
    from things
)
--select * from numbered
select r1.market + '/' + r1.grade as Market, r1.term + '/' + r2.term as Term, r1.Bid - r2.Offer [Bid], r1.Offer - r2.Bid [Offer] 
from numbered r1 
join numbered r2 on r1.market = r2.market and r1.grade = r2.grade and r1.i < r2.i and r1.CP!=r2.CP

How can I treat both NULLs as not equal.

DoIt
  • 3,270
  • 9
  • 51
  • 103

3 Answers3

3

Can you not just change:

and r1.CP!=r2.CP

to:

and ISNULL(r1.CP, 'X') != ISNULL(r2.CP, 'Y')

Edit. If you want to be really safe and live a little dangerously you could even do this:

and ISNULL(r1.CP, CONVERT(VARCHAR(36), NEWID())) != ISNULL(r2.CP, CONVERT(VARCHAR(36), NEWID()))
Richard Hansell
  • 5,315
  • 1
  • 16
  • 35
  • 1
    I like how clever this is, however, you are using letters that can potentially be added as CPs in the future. Would it not make more sense to use `''` and `null` instead of `'X'` and `'Y'`? In sql, `'' = null` returns false – Jenn Jul 31 '14 at 14:39
  • 1
    Agreed, there is still a little bit of work for the OP to do as they need to determine two different values that are safe to assume will never appear in their data. Even your suggestion would fail if there were blank values as CPs as it could potentially match a NULL to a legitimate empty string :D – Richard Hansell Jul 31 '14 at 14:55
  • @RichardHansell didn't even think about using the `NewID()` function. That's a really fun way to make sure it doesn't match. – Elias Jul 31 '14 at 15:41
  • @Elias fun, until you get collision anyway (but how likely is that to happen?) :D – Richard Hansell Jul 31 '14 at 15:43
  • @RichardHansell `For a 1% chance of collision, you'd need to generate about 2,600,000,000,000,000,000 GUIDs.` http://stackoverflow.com/q/184869/1504882 – Elias Jul 31 '14 at 15:46
1

I'm not quite throwing this out as an answer because it is an awful solution, but you could replace NULLS with a stand-in value with the ISNULL function. 

;with numbered as
(
 select id, market, grade, term, bid, offer, row_number() 
 OVER (Partition BY Market,     Grade ORDER BY Bid desc) i
from things
)
--select * from numbered
select r1.market + '/' + r1.grade as Market, r1.term + '/' + r2.term as Term, 
       r1.Bid -      r2.Offer [Bid], r1.Offer - r2.Bid [Offer] 
from numbered r1 
join numbered r2 on r1.market = r2.market and r1.grade = r2.grade and r1.i < r2.i 
and  ISNULL(r1.CP, 1) != ISNULL(r2.CP,2)
Elias
  • 2,602
  • 5
  • 28
  • 57
0

You couldn't treat both NULLs as equal, even if you wanted to. NULL is the absence of value; you can't compare nothing with nothing, that makes no sense.

VACN
  • 75
  • 8