Store values in select as a temp variable and reuse them

I am trying to clean up a query that is ugly but does the job. I am wondering if it is possible to create a temp variable inside of a SELECT statement. I have Googled and not found any help. Basically what I am doing with the query is comparing a DateTime field against the current time to see if the date is in the past or the future. Maybe I have over complicated something simple but from looking around on the internet this seems to be the only way to compare a DateTime field.

I want to be able to reuse the following repetitive parts of the query because I have to list them twice to get the floor value:

(CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT)

AND

(CAST(GETDATE() AS FLOAT)

Here is the query:

SELECT
          bq.quoteID
        , bq.unsentNumber
        , bq.sendSubsequent
        , bq.unsentDelay
        , sr.contractorID
        , CASE 
            WHEN (CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT)  - FLOOR(CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT))) <= (CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) 
                THEN 1
                ELSE 0
            END AS 'Add Bid'
        , DATEADD(n, bq.markupDelay, sr.lastviewed) AS 'Send Time'
        , GETDATE() AS 'Time Now'  
        , e.email
        , e.name
    FROM tblCustomerBidQueue bq
    INNER JOIN tblServiceRecords sr ON bq.QuoteID = sr.cartID AND sr.status = 'Bid' AND bq.status = 'Queued'
    INNER JOIN tblContractors c ON c.ID = sr.contractorID
    INNER JOIN tblEmails e ON e.custom = bq.QuoteID
    WHERE bq.status = 'Queued'

Thanks in advance for any help!

Yes, you can do what you want. It's not exactly a variable but it works the same. It's one of several ways you can use CROSS APPLY.

SELECT
      bq.quoteID
    , bq.unsentNumber
    , bq.sendSubsequent
    , bq.unsentDelay
    , sr.contractorID
    , CASE 
        WHEN (ComputedColumns.MyDateAdd  - FLOOR(ComputedColumns.MyDateAdd)) <= ((ComputedColumns.MyDate) - FLOOR(ComputedColumns.MyDate))
            THEN 1
            ELSE 0
        END AS 'Add Bid'
    , DATEADD(n, bq.markupDelay, sr.lastviewed) AS 'Send Time'
    , GETDATE() AS 'Time Now'  
    , e.email
    , e.name
FROM tblCustomerBidQueue bq
INNER JOIN tblServiceRecords sr ON bq.QuoteID = sr.cartID AND sr.status = 'Bid' AND bq.status = 'Queued'
INNER JOIN tblContractors c ON c.ID = sr.contractorID
INNER JOIN tblEmails e ON e.custom = bq.QuoteID
CROSS APPLY (SELECT CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT) AS MyDateAdd,
                CAST(GETDATE() AS FLOAT) AS MyDate) ComputedColumns
WHERE bq.status = 'Queued'

Basically you CROSS APPLY your variables and then you can use them as if they are a the columns of a table.

StackExchange Database Administrators Q#130940, answer score: 7