问题描述:

I am working on a query which compare two tables and returns the best values if it is in the first table.

I have everything on a SQL Fiddle

But for some reasons it is returning null values in BID and OFFER Columns which should not

pon running the SQL query on the above two tables the result should be like

╔════╦════════╦═════════════╦═══════╦══════╦═══════╦═════════╗

║ Id ║ Market ║ Term ║ BidCP ║ Bid ║ Offer ║ OfferCP ║

╠════╬════════╬═════════════╬═══════╬══════╬═══════╬═════════╣

║ 14 ║ abc ║ Q4 14/Q1 15 ║ begrt ║ -425 ║ NULL ║ vf ║

║ 1 ║ C1 ║ Sep14/Oct14 ║ punt ║ -50 ║ NULL ║ vgc ║

╚════╩════════╩═════════════╩═══════╩══════╩═══════╩═════════╝

The above results can be explained as:

For Product C1 in first table has no row with term Sep14/Oct14 so

the row with highest value of bid and then lowest value of offer is

returned. Similarly for Product abc and term Q4 14/Q1 15. But for

product abc and term Sep14/Oct14 in the first table when compared

with the second table same product and term, the second table has

better values of bid and offer so, the row is not returned in the

results

Is there a better way to do this?

网友答案:

Looks to me like your query and syntax are confused by the >, <, MAX(), and MIN() functions. You are dealing, as far as I can tell, with negative numbers, and expecting the largest absolute value: so -500 is a bigger bid than -400. However, telling SQL to handle MAX and MIN is looking for the real value of the column, and in mathematical terms -400 is bigger than -500.

Try replacing your MAX() with MIN(), and the other way around. Alternatively, try using the ABS() function to get the absolute value of the data (e.g., MAX(ABS(Bid))

网友答案:

Okay, I don't understand what you are trying to achieve with this but I can see a number of problems:

  • you are collating a maximum bid/ minimum offer for each "thing" and then joining these back to the original tables. But this won't work unless a single row has the maximum bid AND the minimum offer;
  • some of your logic for when to display bids/ offers means that you will have NULLs in the output and so you should expect to see them;
  • your comparison between values seems confused as you are using negative values (and negative NULLs!!).

I threw this into a slightly amended version of your query that might help you to pick apart what is going wrong?

DECLARE @InferredBids TABLE (Market VARCHAR(10), Term VARCHAR(20), BidCP VARCHAR(10), Bid FLOAT, Offer FLOAT, OfferCP VARCHAR(10));
INSERT INTO @InferredBids VALUES('C1', 'Sep14/Oct14', 'Nothing', -60, -40, '');
INSERT INTO @InferredBids VALUES('C1', 'Sep14/Oct14', 'punt', -50, NULL, 'vgc');
INSERT INTO @InferredBids VALUES('abc', 'Sep14/Oct14', 'disc', -390, -285, 'fvfvf');
INSERT INTO @InferredBids VALUES('abc', 'Sep14/Oct14', 'vgc', -415, -185, 'vfvfv');
INSERT INTO @InferredBids VALUES('abc', 'Q4 14/Q1 15', 'begrt', -425, NULL , 'vf');
DECLARE @CanadianCrudes TABLE (Product VARCHAR(10), Term VARCHAR(20), BidCP VARCHAR(10), Bid FLOAT, Offer FLOAT, OfferCP VARCHAR(10));
INSERT INTO @CanadianCrudes VALUES('C1','Sep14', 'dddddddd', -975, NULL,'xoom');
INSERT INTO @CanadianCrudes VALUES('C1','Sep14', 'efrt', -985, NULL, NULL);
INSERT INTO @CanadianCrudes VALUES('C1', 'Sep14', 'BPl', NULL, NULL, 'jjjj');
INSERT INTO @CanadianCrudes VALUES('abc', 'Sep14/Oct14', 'CVXvg', -350 , -300, 'Shl');

WITH t1 AS (
SELECT 
    Market,
    Term,
    MAX(Bid) AS MaxBid, 
    MIN(Offer) AS MinOffer           
FROM     
    @InferredBids 
GROUP BY 
    Market,
    Term),
t2 AS (
SELECT 
    Product,
    Term, 
    MAX(Bid) AS Bid, 
    MIN(Offer) AS Offer
FROM 
    @CanadianCrudes 
GROUP BY 
    Product,
    Term)
SELECT 
    t1.Market,
    t1.Term,
    ib.BidCP,
    ib.Bid AS InferredBid,
    cc.Bid AS CrudeBid,
    CASE WHEN ib.Bid >= ISNULL(cc.Bid, 0) THEN t1.MaxBid ELSE NULL END AS CalculatedBid,
    t1.MaxBid,
    ib.Offer AS InferredOffer,
    cc.Offer AS CrudeOffer,
    CASE WHEN ib.Offer <= ISNULL(cc.Offer, 0) THEN t1.MinOffer ELSE NULL END AS CalculatedOffer,
    ib.OfferCP
FROM
    t1 
    LEFT JOIN t2 ON t1.Market = t2.Product
    LEFT JOIN @InferredBids ib ON ib.Market = t1.Market AND ib.Term = t1.Term AND ib.Bid = t1.MaxBid --AND ib.offer = t1.minoffer
    LEFT JOIN @CanadianCrudes cc ON cc.Product = t2.Product AND cc.Term = t2.Term AND cc.Bid = t2.Bid; --AND cc.offer = t2.offer

If you run this then you will get something like this:

Market  Term        BidCP   InferredBid CrudeBid    CalculatedBid   MaxBid  InferredOffer   CrudeOffer  CalculatedOffer OfferCP
abc     Q4 14/Q1 15 begrt   -425        -350        NULL            -425    NULL            -300        NULL        vf
abc     Sep14/Oct14 disc    -390        -350        NULL            -390    -285            -300        NULL        fvfvf
C1      Sep14/Oct14 punt    -50         -975        -50             -50     NULL            NULL        NULL        vgc
相关阅读:
Top