CODEWITHSUNDEEP
sqlsql-server-2005
EvilWeebl
EvilWeebl

Reputation: 701

Get rows that don't exist in a portion of another table

I have data in table1 that looks like so:

  DateMonthID    SubscriberID    othercolumns...
     201201           106
     201201           207
     201202           309
     201203           405
     201203           297

etc, etc

and table2 like so:

  DateMonthID    SubscriberID    Level
       ...           ...          1
       ...           ...          2
       ...           ...          1
       ...           ...          3
       ...           ...          2

etc, etc

What I need to do is check which subscribers in the first table do not exist in a range of 3 months in the future in the other table. Does that make sense?

Example: take the subscriberID 106 above in table for the date 201201(January). I need to accrue a count if it does not appear in the other table from 201201-201204(Jan-April).

This is what I have so far but it seems to be returning too many values:

SELECT
    COUNT(1) AS Total,
    table1.month_key,
    table2.level
FROM
    dbo.table1
INNER JOIN
    dbo.table2 ON (table2.subscriber_id = table1.subscriber_id)
WHERE
    NOT EXISTS (SELECT * FROM table2
                WHERE
                (table2.month_key >= table1.month_key AND table2.month_key <= (table1.month_key + 3))
                AND table2.subscriber_id = table1.subscriber_id)
GROUP BY
table1.month_key, table2.level
ORDER BY
table2.level, table1.month_key

Any help would be much appreciated

-------------- EDIT -------------- Just to make things clearer because I'm not sure I'm explaining it well. The situation is that in table1 are rows of people who stopped subscribing and the date they stopped subscribing. The thing is this may not be genuine, maybe they just changed subscription or re-subscribed a month later. table 2 is a table full of subscribers for each month. I need to find out who has genuinely unsubscribed by checking if they appear in table 2 between the date table1 says they unsubscribed and then next 3 months. Hope this helps.

Upvotes: 1

Views: 107

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269453

I think the problem is that you cannot add "3" to the month key to get what you want. Try this instead:

FROM (select table1.*,
             (cast(left(month_key, 4) as int)*12+
                                cast(right(month_key, 2) as int)
                               ) as newMonthKey
      from dbo.table1
     ) table1
. . .

where not exists (select 1
                  from (select table2.*,
                               (cast(left(month_key, 4) as int)*12+
                                cast(right(month_key, 2) as int)
                               ) as newMonthKey
                        from table2
                       ) t
                  where (t.newmonthkey >= table1.newmonthkey AND t.newmonthkey <= (table1.newmonthkey + 3))
                        AND t2.subscriber_id = table1.subscriber_id
                       )

This changes the monthkey to a month counter since year 0.

Upvotes: 2

Related Questions