CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2012
Glory Raj
Glory Raj

Reputation: 17701

count with inner join sql query

Hi all we have got two tables "Subscriptioninfo , activationinfo" and the structure is like this for the two tables

Subscriptioninfo 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 below query will give the count as "one" if an entry to Subscriptioninfo with the same year and customer email ID (this is fine)

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

we have one sp that will insert the data into both the tables (i.e) one entry to Subscriptioninfo where as four entries to activationinfo

I need to return count as "0" if one entry to subscriptioninfo and two entries to activationinfo

and i need to return count as "1" if one entry to subscriptioninfo and four entries to activationinfo..

Would any one please help on this query how can i get that count with join clause..

Many thanks in advance...

tried with this query but it is giving the activationinfo entry count(i.e) 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 = '[email protected]' group by csi.subscription_key

Upvotes: 5

Views: 97

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

i just tried to be different and may performance is 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
        )

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

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 = '[email protected]' group by csi.subscription_key

Upvotes: 3

Related Questions