Tyler Prichard Tyler Prichard - 2 years ago 216
SQL Question

Applying CASE WHEN to MIN/MAX

I'm trying to apply MIN/MAX to my select clause based on two criteria. (See Below). When I try to reference my column

submitted_at
in another query, it says the column reference is ambiguous.

MIN(CASE WHEN (document != 'dphoto' AND document_type != 'dlicenses' AND status = 'upload') THEN occurred_at END) AS submitted_at,


MAX(CASE WHEN (document_type = 'dphoto' AND document_type = 'dlicenses' AND status = 'upload') THEN occurred_at END) AS submitted_at


I've attempted to consolidate the phrase into 1 select but have been unsuccessful. Any tips are appreciated

Answer Source

You can combine the two into one column by using the following:

CASE    WHEN    (document != 'dphoto' AND document_type != 'dlicenses' AND status = 'upload')
            THEN    Min(occurred_at)
        WHEN    (document_type = 'dphoto' AND document_type = 'dlicenses' AND status = 'upload') 
            THEN    Max(occurred_at)
END As Submitted_at
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download