Counting with sql sql internal query

Question

Hi everyone, we have two tables: Subscriptioninfo, activationinfo "and the structure is similar to this structure for two tables

Subscription table

cert_id (int, PK) customer_email(string) subscription_key(string) activated_on (datetime) 

activationinfo table:

  cert_id (int) activationcode(string) subscription_key(string , FK) activated_ts(datetime) 

the request below will give the account " one " if the record is in the Subscriptioninfo with the same year and client email id (this is normal)

 SELECT COUNT(*) FROM Subscriptioninfo WITH (NOLOCK) WHERE year(activated_On) = year(getdate()) AND customer_email =@CustomerEmail 

we have one sp that will insert data into both tables (for example, one record in Subscriptioninfo, where as four records to activateinfo

I need to return the account as "0" if there is one entry for the info subscription and two entries for the activation info

and I need to return the account as "1" if there is one entry for the info subscription and four entries for activation.

Can someone help with this question, how can I get this account with join clause.

Thank you very much in advance...

tried with this request, but it gives an activation record account info (ie) 4 instead of 1

  SELECT COUNT(*) FROM subscriptioninfo csi join activationinfo aci on csi.subscription_key = aci.subscription_key WHERE year(Activated_On) = year(getdate()) AND customer_email = ' [e-mail] ' group by csi.subscription_key 

Answer 1

Use CASE statements

 SELECT CASE WHEN COUNT(*) = 4 THEN 1 WHEN COUNT(*) < 4 THEN 0 END CountResults FROM subscriptioninfo csi join activationinfo aci on csi.subscription_key = aci.subscription_key WHERE year(Activated_On) = year(getdate()) AND customer_email = ' [e-mail] ' group by csi.subscription_key 

Answer 2

I just tried to be different, and performance could be better,

 ;WITH CTE AS ( SELECT * ,row_number() OVER ( ORDER BY cert_id ) rn FROM @activationinfo A ) SELECT * ,1 AS CountResult FROM @Subscriptioninfo S WHERE EXISTS ( SELECT cert_id FROM CTE A WHERE s.cert_id = a.cert_id AND rn = 4 ) UNION ALL SELECT * ,0 AS CountResult FROM @Subscriptioninfo S WHERE EXISTS ( SELECT cert_id FROM CTE A WHERE s.cert_id = a.cert_id AND rn < 4 )