Created
February 16, 2018 18:09
-
-
Save apple-corps/79153b1ae228fa6a0f58b9958f552bbb to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
); |
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;
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
totally looking for bitwise ops: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-and-transact-sql