Skip to content

Instantly share code, notes, and snippets.

@apple-corps
Created February 16, 2018 18:09
Show Gist options
  • Save apple-corps/79153b1ae228fa6a0f58b9958f552bbb to your computer and use it in GitHub Desktop.
Save apple-corps/79153b1ae228fa6a0f58b9958f552bbb to your computer and use it in GitHub Desktop.
create temp table event_shp
as (
select cust_key,
case when delivery_channel <> 'JOIN' and
product_type = 'General' and
sale_dt in
(select event_dt
from user_tbls.events
where anniversary_public_event=1 ) then 1
else 0
end as Anniversary_Public,
case when delivery_channel <> 'JOIN' and
product_type = 'General' and
sale_dt in
(select event_dt
from user_tbls.events
where anniversary_early_access=1) then 1
else 0
end as Anniversary_EA,
from user_tbls.transitory
);
@apple-corps
Copy link
Author

apple-corps commented Feb 16, 2018

Then the table event_shp has Anniversary_Public and Anniversary_EA with values of either 1 or 0. What I think I want to do is look at the table user_tbls.events from the subquery possibly a JOIN on sale_dt=event_dt and set Anniversary_Public and Anniversary_EA to the "boolean AND" between them and the corresponding anniversary_public_event or anniversary_early_access. EG. 1&1 = 1 and all others 0.

@apple-corps
Copy link
Author

@apple-corps
Copy link
Author

apple-corps commented Feb 16, 2018

Given modified table without subquery above maybe something like :

select * from event_shp E left join select event_dt,anniversary_public_event,anniversary_early_access from user_tbls.events T
 CASE WHEN E.sale_dt=T.event_dt
   then E.AnniversaryPublic & T.anniversary_public_event as E.Anniversary_Public and E.Anniversary_EA & T.anniversary_early_access as E.Anniversary_EA
 else 0 as E.Anniversary_Public and 0 as E.Anniversary_EA;

@chrwei
Copy link

chrwei commented Feb 16, 2018

select cust_key,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' and
            anniversary_public_event=1 ) then 1
      else 0
      end as Anniversary_Public,
 case when delivery_channel <> 'JOIN' and
           product_type = 'General' and
            anniversary_early_access=1) then 1
      else 0
      end as Anniversary_EA,
 from user_tbls.transitory left join user_tbls.events on sale_dt = event_dt 

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment