How do I get a query to return a "1" value in place of text-CodePudding

Here's the full code:

WITH Part1 AS 
(
    SELECT 
        session_id, occurred_at, activity_name
    FROM      
        Activity_Data_Table$
    WHERE   
        (occurred_at > CURRENT_TIMESTAMP - 31) 
        AND (activity_name = 'create')
    GROUP BY 
        session_id, occurred_at, activity_name
), 
Part2 AS 
(
    SELECT 
        session_id, occurred_at, activity_name
    FROM      
        Activity_Data_Table$ AS Activity_Data_Table$_1
    WHERE   
        (occurred_at > CURRENT_TIMESTAMP - 31) 
        AND (activity_name = 'Resolve')
    GROUP BY 
        session_id, occurred_at, activity_name
)
SELECT 
    Part1_1.occurred_at, Part1_1.activity_name, 
    Part2_1.activity_name
FROM   
    Part1 AS Part1_1 
LEFT OUTER JOIN 
    Part2 AS Part2_1 ON Part1_1.session_id = Part2_1.session_id

Here is what I get:

It returns 3 columns:

Date, activity 1, activity 2

What I want, and it can be in additional columns or substitute: in the 2 activity columns, if there is a non-null value (create or resolve) then I want to insert a '1' otherwise 0 or null. Tried countless ways of inserting count and it's just not working, but I'm sure someone with better skills this is 2 seconds.

Thanks

CodePudding user response：

There's no need to query the table twice and join the results.

SELECT 
    session_id,
    occurred_at,
    MAX(CASE WHEN activity_name = 'create'  THEN 1 ELSE 0 END) AS has_create,
    MAX(CASE WHEN activity_name = 'Resolve' THEN 1 ELSE 0 END) AS has_resolve
FROM
    Activity_Data_Table$
WHERE   
        occurred_at   >  CURRENT_TIMESTAMP - 31
    AND activity_name IN ('create', 'Resolve')
GROUP BY
    session_id,
    occurred_at

This will only return sessions that have at least one or other activity. If you want to allow sessions with neither activity, just remove the AND activity_name IN ('create', 'Resolve')

EDIT:

I just realised I don't know if the two activities happen at the same time. If they do, great, the above query works. If they can be different, something like this may help...

SELECT 
    session_id,
    MIN(occurred_at)   AS occured_at_first,
    MAX(occurred_at)   AS occured_at_last,
    MAX(CASE WHEN activity_name = 'create'  THEN occurred_at END) AS occured_at_create,
    MAX(CASE WHEN activity_name = 'Resolve' THEN occurred_at END) AS occured_at_resolve,        
    MAX(CASE WHEN activity_name = 'create'  THEN  1  ELSE  0 END) AS has_create,
    MAX(CASE WHEN activity_name = 'Resolve' THEN  1  ELSE  0 END) AS has_resolve
FROM
    Activity_Data_Table$
WHERE   
    occurred_at   >  CURRENT_TIMESTAMP - 31
GROUP BY
    session_id

It depends on what you need/want.

CodePudding user response：

You can use a CASE statement, something like

SELECT ...
     , CASE 
         WHEN Part1_1.activity_name IS NOT NULL
         THEN 1
         ELSE 0
       END AS activity1_name_available
     , CASE 
         WHEN Part2_1.activity_name IS NOT NULL
         THEN 1
         ELSE 0
       END AS activity2_name_available
  FROM ...

You can also combine them for example if you need a 1 if either of them is available, or when both are available:

SELECT ...
     , CASE 
         WHEN Part1_1.activity_name IS NOT NULL
           OR Part2_1.activity_name IS NOT NULL
         THEN 1
         ELSE 0
       END AS either_available
     , CASE 
         WHEN Part1_1.activity_name IS NOT NULL
          AND Part2_1.activity_name IS NOT NULL
         THEN 1
         ELSE 0
       END AS both_available
  FROM ...