CODEWITHSUNDEEP
sqlcaseamazon-redshift
Emm
Emm

Reputation: 2507

Conditional subtraction on SQL

I would like to write a case statement to subtract the lowest datetime between app login and portal login from the signup time.

I am assuming this would involve a case statement, but not sure how to write this out

My current code is:

select case when first_app_login < first_portal_login
                then signup_complete - first_app_login
            when first_app_login > first_portal_login
                then signup_complete - first_portal_login
            else signup_complete - first_app_login
       end as signup_to_login
from database

However this returns the following error message:

ERROR: Invalid protocol sequence 'P' while in PortalSuspended state. Untitled Chart

Here is a sample of my data:

signup_complete               first_app_login     first_portal_login
2019-07-08 08:38:56.523                          2019-09-07 19:01:01.915
2019-05-10 12:24:11.704     2019-05-10 12:54:11.704

The desired result would be signup_complete - first_portal_login for the first row, signup_complete - first_app_login for the second row.

So we only subtract signup_complete with either the non null value or the lowest datetime value between first_app and first_portal login

Upvotes: 0

Views: 967

Answers (2)

Mushif Ali Nawaz
Mushif Ali Nawaz

Reputation: 3866

You can make use of LEAST function. LEAST function's documentation states that it will "return the smallest value and ignores the NULL values".

SELECT LEAST(first_app_login, first_portal_login) - signup_complete as signup_to_login from dummy_table;

Here you can see it in action for Postgres 8.4 DB on db<>fiddle.

Upvotes: 1

N&#233;stor Carre&#241;o Gimeno
N&#233;stor Carre&#241;o Gimeno

Reputation: 523

If you want to get the difference in days there you go. Otherwise you will have to put "month" or "year" on the first parameter of DATEDIFF():

SELECT
CASE
    WHEN first_app_login < first_portal_login THEN 
        DATEDIFF(day, signup_complete, first_app_login)
    WHEN first_app_login > first_portal_login THEN 
        DATEDIFF(day, signup_complete, first_portal_login)
    ELSE DATEDIFF(day, signup_complete, (case
                                WHEN first_app_login IS NULL THEN
                                    first_portal_login
                                WHEN first_portal_login IS NULL THEN
                                    first_app_login
                                END))
END AS signup_to_login FROM [YOUR_TABLE_NAME]

If you just want to subtract dates as you were doing:

SELECT 
CASE
    WHEN first_app_login < first_portal_login THEN 
        signup_complete - first_app_login
    WHEN first_app_login > first_portal_login THEN 
        signup_complete - first_portal_login
    ELSE signup_complete - (case
                                WHEN first_app_login IS NULL THEN
                                    first_portal_login
                                WHEN first_portal_login IS NULL THEN
                                    first_app_login
                                END)
END AS signup_to_login FROM [YOUR_TABLE_NAME]

Upvotes: 1

Related Questions