CODEWITHSUNDEEP
sqlsql-servert-sqlleft-joinlateral-join
Cataster
Cataster

Reputation: 3541

How to declare variable within a SELECT?

I have a long query that calculates a report refresh time and if the report didn't refresh on the "scheduled" time, then it would display late, per the cases below:

SELECT
RefreshStartStatus      =   CASE 
                                WHEN CS.StartTime > CURRENT_TIMESTAMP THEN 'STARTING LATER'
                                WHEN DATEDIFF(MINUTE, CS.StartTime, SA.TodayFirstRunTime) < 30 THEN 'Less than half hour late!' -- less than 30 mins late
                                WHEN DATEDIFF(MINUTE, CS.StartTime, SA.TodayFirstRunTime) >= 30 THEN 'More than half hour Late!' -- 30 mins late
                                ELSE '???'
                                END
FROM #CSTempTable CS

LEFT OUTER JOIN #SubscriptionAggTempTable SA
    ON SA.SubscriptionID = CS.SubscriptionID

I'd like to enhance this further and display by actually how late it was.

Since I've worked with scripts (e.g. PowerShell), it would be something as easy as

WHEN DATEDIFF(MINUTE, CS.StartTime, SA.TodayFirstRunTime) < 30 THEN "$(DATEDIFF(MINUTE, CS.StartTime, SA.TodayFirstRunTime)) late!"

but of course thats not the case here with a SQL query so how would I output the actual value of how late it was? I've thought of declaring and setting a scalar variable like this:

DECLARE @timing int;
SET @timing = DATEDIFF(MINUTE, CS.StartTime, SA.TodayFirstRunTime);

WHEN @timing < 30 THEN @timing + ' mins late!'

But I don't know how I can do that WITHIN the SELECT statement because the value is generated inside it due to the joins required to perform the diff...

so that it ends up like this:

SELECT

DECLARE @timing int;
SET @timing = DATEDIFF(MINUTE, CS.StartTime, SA.TodayFirstRunTime);

RefreshStartStatus      =   CASE 
                                WHEN CS.StartTime > CURRENT_TIMESTAMP THEN 'STARTING LATER'
                                WHEN timing < 30 THEN @timing + ' mins late! a little late' -- less than 30 mins late
                                WHEN timing >= 30 THEN timing + ' mins Late! VERY LATE!' -- 30+ mins late
                                ELSE '???'
                                END
FROM #CSTempTable CS

LEFT OUTER JOIN #SubscriptionAggTempTable SA
    ON SA.SubscriptionID = CS.SubscriptionID

Upvotes: 0

Views: 127

Answers (1)

GMB
GMB

Reputation: 222572

If I follow you correctly, you can use VALUES() and a lateral join to do the computation only once:

SELECT
    CASE 
        WHEN CS.StartTime > CURRENT_TIMESTAMP THEN 'STARTING LATER'
        WHEN x.timing <  30 THEN 'Less than half hour late!' 
        WHEN x.timing >= 30 THEN 'More than half hour Late!'
        ELSE '???'
    END as RefreshStartStatus,
    CASE WHEN CS.StartTime <= CURRENT_TIMESTAMP THEN CONCAT(x.timing ' mins late') END as info
FROM #CSTempTable CS
LEFT OUTER JOIN #SubscriptionAggTempTable SA ON SA.SubscriptionID = CS.SubscriptionID
CROSS APPLY (VALUES (DATEDIFF(MINUTE, CS.StartTime, SA.TodayFirstRunTime))) as x(timing)

Upvotes: 3

Related Questions