Selecting latest record with datetime and if not exists select some default value

Question

This is my query

SELECT lbi.ItemGrpId,igm.ItemGrpName,lbi.Qty,
'BidAmt' = CASE 
 WHEN lbi.ItemGrpId IN
 (
  SELECT DISTINCT vbd.ItemGrpId
  FROM tbVebdorBidDetails vbd
  WHERE vbd.BidID=139 AND vbd.VendorEmailID='[email protected]'
  AND vbd.UpdatedDateTime IN 
  (
   SELECT MAX(UpdatedDateTime) AS [MAXMIN] 
   FROM tbVebdorBidDetails WHERE BidID=139
   AND VendorEmailID='[email protected]'
   GROUP BY ItemGrpID
  )
 )
 THEN
  MIN(cast(cast(vbd.BidAmt as decimal) / cast (vbd.CurrencyExchangeRate as decimal) as decimal(18,2))) 
 ELSE
  0.0  
 END
FROM tbLnkBidItemGrp lbi   
INNER JOIN tbVebdorBidDetails vbd ON lbi.BidID=vbd.BidID
INNER JOIN    
(    
 SELECT ItemGrpId,
 MAX(UpdatedDateTime) AS [MAXMIN] 
 FROM tbVebdorBidDetails WHERE BidID=139
 AND VendorEmailID='[email protected]'
 GROUP BY ItemGrpID   
) t2     
ON vbd.ItemGrpId=t2.ItemGrpId 
AND vbd.UpdatedDateTime= t2.[MAXMIN] 
INNER JOIN tbItemGrpMaster igm ON igm.ItemGrpId=lbi.ItemGrpID    
WHERE vbd.BidID=139 AND vbd.VendorEmailID='[email protected]'
GROUP BY lbi.ItemGrpId,vbd.BidAmt,igm.ItemGrpName,lbi.Qty

I want to select BidAmt For each distinct ItemGrpId But here it repeats BidAmt with each ItemGrpId

The result is like this

ItemGrpId    ItemGrpName    Qty       BidAmt 
70           Screw          700       12
70           Screw          700       16
80           NutBolt        1000      12
80           NutBolt        1000      16

I should be

ItemGrpId    ItemGrpName    Qty       BidAmt 
70           Screw          700       12
80           NutBolt        1000      16

Answer 1

Your join between tbVebdorBidDetails and tbLnkBidItemGrp is only using BidId, while apparently each bid can contain multiple ItemGrpId values. So you're getting back multiple rows in that join. To ensure you get results when there are no matches in the tbVebdorBidDetails table, you'll need to use a LEFT JOIN, which ensures the final result set contains a row from the leftmost table in the join, even if there's no match from the rightmost table. (the columns of the join from the rightmost table will be populated with NULLs).

Anyway, you probably want to change your join to this:

FROM tbLnkBidItemGrp lbi   
    INNER JOIN tbItemGrpMaster igm ON igm.ItemGrpId=lbi.ItemGrpID  
    LEFT JOIN tbVebdorBidDetails vbd ON lbi.BidID=vbd.BidID
               AND lbi.ItemGrpId = vbd.ItemGrpID

Note that (like I showed in the snippet above) I moved your INNER JOIN on tbItemGrpMaster to earlier in the query, so that it will still get executed even if there are no matches in the tbItemGrpMaster table. You may need to make more changes in order for your query to work-- I'm not at my desk now so can't test the changes.

BTW, your query is much more complex and inefficient than it needs to be. Here's one possible way to simplify it (assuming you're running on SQL 2005 so can use ROW_NUMBER())-- you can simply pick out the first row in each group, ordered descending by date:

SELECT ItemGrpId, 
 (SELECT ItemGrpName FROM tbItemGrpMaster igm WHERE igm.ItemGrpId=vbd.ItemGrpID) as ItemGrpName,
 Qty, 
 BidAmt
FROM (
 SELECT lbi.ItemGrpId,
  lbi.Qty,
  ISNULL (cast(cast(vbd.BidAmt as decimal) / cast (vbd.CurrencyExchangeRate as decimal) as decimal(18,2)), 0.0) as BidAmt,
  ROW_NUMBER() OVER (PARTITION BY lbi.ItemGrpId ORDER BY UpdatedDateTime DESC) as RowNum
 FROM tbLnkBidItemGrp lbi   
  LEFT JOIN tbVebdorBidDetails vbd ON lbi.BidID=vbd.BidID AND lbi.ItemGrpId = vbd.ItemGrpID
 WHERE vbd.BidID=139 AND vbd.VendorEmailID='[email protected]'
) vbd
WHERE RowNum = 1;

BTW, here's the schema I was using:

IF EXISTS(SELECT name FROM sysobjects WHERE name = N'tbVebdorBidDetails' AND xtype='U')
 DROP TABLE tbVebdorBidDetails;
CREATE TABLE tbVebdorBidDetails
(
 BidID int,
 VendorEmailID varchar(100),
 UpdatedDateTime datetime,
 ItemGrpID int,
 BidAmt decimal,
 CurrencyExchangeRate decimal
);

INSERT INTO tbVebdorBidDetails (BidId, VendorEmailId, UpdatedDateTime, ItemGrpId, BidAmt, CurrencyExchangeRate)
 VALUES (139, '[email protected]', '1/1/2009', 1, 100, 1);
INSERT INTO tbVebdorBidDetails (BidId, VendorEmailId, UpdatedDateTime, ItemGrpId, BidAmt, CurrencyExchangeRate)
 VALUES (139, '[email protected]', '2/1/2009', 1, 200, 1);
INSERT INTO tbVebdorBidDetails (BidId, VendorEmailId, UpdatedDateTime, ItemGrpId, BidAmt, CurrencyExchangeRate)
 VALUES (139, '[email protected]', '3/1/2009', 1, 300, 1);
INSERT INTO tbVebdorBidDetails (BidId, VendorEmailId, UpdatedDateTime, ItemGrpId, BidAmt, CurrencyExchangeRate)
 VALUES (139, '[email protected]', '1/1/2009', 2, 1000, 1);
INSERT INTO tbVebdorBidDetails (BidId, VendorEmailId, UpdatedDateTime, ItemGrpId, BidAmt, CurrencyExchangeRate)
 VALUES (139, '[email protected]', '2/1/2009', 2, 2000, 1);
INSERT INTO tbVebdorBidDetails (BidId, VendorEmailId, UpdatedDateTime, ItemGrpId, BidAmt, CurrencyExchangeRate)
 VALUES (139, '[email protected]', '3/1/2009', 2, 3000, 1);

IF EXISTS(SELECT name FROM sysobjects WHERE name = N'tbLnkBidItemGrp' AND xtype='U')
 DROP TABLE tbLnkBidItemGrp;
CREATE TABLE tbLnkBidItemGrp
(
 BidId int,
 ItemGrpId int,
 Qty int
);
INSERT INTO tbLnkBidItemGrp (BidId, ItemGrpId, Qty) VALUES (139, 1, 100)
INSERT INTO tbLnkBidItemGrp (BidId, ItemGrpId, Qty) VALUES (139, 2, 200)

IF EXISTS(SELECT name FROM sysobjects WHERE name = N'tbItemGrpMaster' AND xtype='U')
 DROP TABLE tbItemGrpMaster;
CREATE TABLE tbItemGrpMaster
(
 ItemGrpId int,
 ItemGrpName varchar(100)
);
INSERT INTO tbItemGrpMaster (ItemGrpId, ItemGrpName) VALUES (1, 'Screw')
INSERT INTO tbItemGrpMaster (ItemGrpId, ItemGrpName) VALUES (2, 'NutBolt')