Invalid column name when same DateDiff is used before without problem

Question

SELECT 
    firstname, lastname, datetake, DateReturn,
    DATEDIFF(DAY, DateTake, DateReturn) AS Delay
FROM 
    dbo.ReaderCard, dbo.Orders, dbo.Literature
WHERE 
    dbo.ReaderCard.CardID = dbo.Orders.CardID
    AND dbo.Literature.ItemID = dbo.Orders.ItemID

IF DATEDIFF(DAY, DateTake, DateReturn) > 60
    SELECT (DATEDIFF(DAY, DateTake, DateReturn) * 1.5) AS FineSum
    FROM dbo.Orders

I keep getting an error "invalid column name" on datetake and datereturn in the IF (not the select in the if) and I can't figure out how to fix it. Please give me something...

Answer 1

I think you're just after a CASE expression in the SELECT, which you'll need to do in the same statement because SQL Server has no memory in the IF statement what you might have referenced in some other statement:

SELECT firstname, lastname, datetake, DateReturn
    , Delay = CASE WHEN DATEDIFF(DAY,DateTake,DateReturn) > 60
THEN 1.5 ELSE 1.0 END * DATEDIFF(DAY,DateTake,DateReturn)
FROM dbo.ReaderCard, dbo.Orders, dbo.Literature
WHERE dbo.ReaderCard.CardID = dbo.Orders.CardID
AND dbo.Literature.ItemID = dbo.Orders.ItemID;

But also you should be using proper INNER JOIN syntax, this FROM tbl, tbl, tbl form was out of style in the 90s, for good reason:

-- Prefix all these columns with the alias!
SELECT firstname, lastname, datetake, DateReturn
    , Delay = CASE WHEN DATEDIFF(DAY,DateTake,DateReturn) > 60
THEN 1.5 ELSE 1.0 END * DATEDIFF(DAY,DateTake,DateReturn)
FROM dbo.ReaderCard AS rc
  INNER JOIN dbo.Orders AS o
          ON rc.CardID = o.CardID
  INNER JOIN dbo.Literature AS l
          ON l.ItemID = o.ItemID;