Question: I have a table with transaction records which is grouped by Client codes. 

Typical Table is like Client, Transaction\_Type, Day, Outcome.

I need to Find the effectiveness of a particular type of transaction. 

Eg. 

IF A SMS HAS BEEN SENT ON DAY 1, AND IF THERE IS A POSITIVE OUTCOME WITHIN 4 BUSINESS DAYS OF SENDING THE SMS, THEN IT CAN BE COUNTED AS A EFFECTIVE SMS. 

SAMPLE TABLE:
[![SAMPLE DATA](https://i.stack.imgur.com/1nHwo.png)](https://i.stack.imgur.com/1nHwo.png)

EXPECTED OUTPUT:
[![enter image description here](https://i.stack.imgur.com/QncJh.png)](https://i.stack.imgur.com/QncJh.png)

How do I do a row wise comparison of a single record against all the transactions over a particular Client code? 

Thanks in Advance.

Answer: 
Use `GROUP BY` and `EXISTS`

```
select client, 
       count(*) [sms count],
       (
         select count(*) 
         from data t
         where t.client = d.client and
               ttype = 'sms' and exists(
                 select 1
                 from data h
                 where h.client = t.client and
                       h.day between t.day and t.day + 4 and
                       h.outcome = 'pos'
               )
       ) [eff sms count]
from data d
where ttype = 'sms'
group by client

```

[DBFiddle DEMO](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b24939912dbdde05e58644f5285a5667)

EDIT: Alternative solution

```
select d1.client, 
       d1.[sms count],
       d2.[eff sms count]
from (
    select client, 
       count(*) [sms count]
    from data
    where ttype = 'sms'
    group by client
) d1
join 
(
    select client, count(*) [eff sms count]
    from data t
    where  ttype = 'sms' and exists(
        select 1
        from data h
        where h.client = t.client and
           h.day between t.day and t.day + 4 and
           h.outcome = 'pos'
    )
    group by t.client
) d2 on d1.client = d2.client

```

[DBFiddle DEMO](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=bda8c5817d5f3394f2d2f4fef2d59c2b)

For both solutions the followinf indexes should be present

```
data(ttype, client)
data(outcome, client, day)

```
try this : - 

```
select CLIENT,
   SUM(case when isnull(A.TTYPE,'') <> ''  THEN 1 END) SMS_Count ,
         SUM( case when isnull(A.TTYPE,'') <> '' and A.DAY  <= 4 THEN 1 END)   EFF SMS COUNT 

 from a inner join b on a.Client on b.client and a.TTYPE = b.TTYPE
GROUP BY CLIENT

```