CODEWITHSUNDEEP
mysqlsql
Kamran Shahid
Kamran Shahid

Reputation: 4124

Case statement in Having clause in mysql

I have a stored procedure where I have to check compare some result on bases of operator. operator could be either '>' or '<' My Query is:

  SELECT COUNT(LastWeekId) , COUNT(PreviousToLastWeekId)
        FROM 
        (
        SELECT `transaction_id`  AS LastWeekId, NULL AS PreviousToLastWeekId
        FROM `kwt_transaction`
        WHERE `statusid` =1000            
        AND (createdon BETWEEN CONCAT(DATE_SUB(DATE(NOW()), INTERVAL 7 DAY)," 00:00:00") AND CONCAT(DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)," 23:59:59"))
    UNION

        SELECT  `transaction_id` AS LastWeekId, NULL AS PreviousToLastWeekId
        FROM `kwt_transactions_p2p`
        WHERE `statusid` =1000            
        AND (createdon BETWEEN CONCAT(DATE_SUB(DATE(NOW()), INTERVAL 7 DAY)," 00:00:00") AND CONCAT(DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)," 23:59:59"))
        UNION                   
        SELECT NULL AS LastWeekId, `transaction_id` AS PreviousToLastWeekId
        FROM `kwt_transaction`
        WHERE `statusid` =1000            
        AND (createdon BETWEEN CONCAT(DATE_SUB(DATE(NOW()), INTERVAL 14 DAY)," 00:00:00") AND CONCAT(DATE_SUB(DATE(NOW()), INTERVAL 8 DAY)," 23:59:59"))
    UNION   
        SELECT NULL AS LastWeekId, `transaction_id` AS PreviousToLastWeekId
        FROM `kwt_transactions_p2p`
        WHERE `statusid` =1000            
        AND (createdon BETWEEN CONCAT(DATE_SUB(DATE(NOW()), INTERVAL 14 DAY)," 00:00:00") AND CONCAT(DATE_SUB(DATE(NOW()), INTERVAL 8 DAY)," 23:59:59"))
    ) ttable
    HAVING COUNT(LastWeekId) > COUNT(PreviousToLastWeekId)

So I wants to check:

HAVING Case _operaotr
       when '>'    
       COUNT(LastWeekId) > COUNT(PreviousToLastWeekId);
when '<'
    COUNT(LastWeekId) < COUNT(PreviousToLastWeekId);
End Case;

Is this possible in mysql

Upvotes: 1

Views: 6822

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272146

I think you can do this:

HAVING CASE @Operator
    WHEN '>' THEN COUNT(LastWeekId) > COUNT(PreviousToLastWeekId)
    WHEN '<' THEN COUNT(LastWeekId) < COUNT(PreviousToLastWeekId)
    WHEN '=' THEN COUNT(LastWeekId) = COUNT(PreviousToLastWeekId)
    -- and so on
END

Upvotes: 2

Related Questions