SQL Server Performance

Check whether no record on join condion

Discussion in 'SQL Server 2008 General Developer Questions' started by baburk, Mar 20, 2009.

  1. baburk New Member

    Hi,

    Myrequirement is that if there is no record in Log Table for the BatchTable Join Condition Log.ID = Batch.ID AND Log.LogDT = Batch.BatchDT,Then we have to get the Batch.BatchValue when

    COUNT(Log.totalHrs) = 0
    We have to check whether this is null or empty i.e. there is no record for the condition in Log table. This is thing I need.

    else if there is record for Id and the BatchDT we get the min of Log.LogValue

    totalHrs DataType is Time


    SELECT Batch.BatchHrs,
    Hrs =
    CASE
    WHEN COUNT(Log.totalHrs) = 0
    OR Log.TotalHrs = '00:00:00.0000000'
    THEN Batch.BatchValue
    ELSE MIN(Log.LogValue)
    END
    FROM Batch
    LEFT JOIN Log
    ON Log.ID = Batch.ID
    AND Log.LogDT = Batch.BatchDT
    WHERE Batch.ID = '01'
    AND Batch.BatchDT = '3/20/2009'
    GROUP BY Batch.BatchHr

    Thanks,
    Babu Kumarasamy
  2. dhamu294 New Member

    have a if exists check before the query.

Share This Page