SQL variable group within another group

Question

I'm looking for a way to match a set of results from a larger list of other results, and count the number of matches. For example:

I have a set of results

Result 1
sub1
sub2
sub3

Result 2
sub1
sub2
sub3

I need to find how many times either set of the above results appears in a much larger data set of results below.

Result 1
sub1
sub2
sub3

Result 2
sub1
sub3
sub4

Result 2
sub1
sub2
sub3

Result 2
sub1
sub2
sub3
sub4

In the example above, Result 1 from the first set would match the first result in the second set, and Result 2 would match the final 2 results in the second set, because they contained all the sub results from the first set. So Result 1 would show a frequency count of 1, while Result 2 would show a frequency count of 2.

I'm rather new with SQL, but would like to find a solution to the above problem.

Sample Data from yesterday:

Group    Ad   Date
A        1    7/14
A        2    7/14
A        3    7/14
B        1    7/14
B        2    7/14
B        3    7/14
B        4    7/14
C        1    7/14
D        1    7/14
D        3    7/14
D        4    7/14

I need to find out how many times Group A Ads 1-3 ran in the past week, but say on Monday Group A ran only ads 1 and 3. I don't want this result returned. Tuesday Group A ran ads 1, 2, 3, 4. I WOULD want to know this result, Wednesday Group A had ads 1, 2, 3 run, this again I would want to know.

Group    Ad   Date
A        1    7/09
A        3    7/09
A        1    7/10
A        2    7/10
A        3    7/10
A        4    7/10
A        1    7/14
A        2    7/14
A        3    7/14

So, given the example, I would expect to see this:

Group    Ad   Date
A        1    7/10
A        2    7/10
A        3    7/10
A        1    7/14
A        2    7/14
A        3    7/14

Answer 1

It's a little messy, but here is what I was able to come up with:

SELECT a.*, b.*
FROM 
(
    SELECT 'A' AS grp, 1 AS ad UNION ALL
    SELECT 'A', 2 UNION ALL
    SELECT 'A', 3
) a 
CROSS JOIN
(
    SELECT DISTINCT date
    FROM tbl
    WHERE date >= CURDATE() - INTERVAL 1 WEEK
) b
LEFT JOIN tbl c ON a.grp = c.grp
               AND a.ad = c.ad
               AND b.date = c.date
INNER JOIN
(
    SELECT a.date
    FROM 
    (
        SELECT 'A' AS grp, 1 AS ad UNION ALL
        SELECT 'A', 2 UNION ALL
        SELECT 'A', 3
    ) a 
    CROSS JOIN
    (
        SELECT DISTINCT date
        FROM tbl
        WHERE date >= CURDATE() - INTERVAL 1 WEEK
    ) b
    LEFT JOIN tbl c ON a.grp = c.grp
                   AND a.ad = c.ad
                   AND b.date = c.date
    GROUP BY a.date
    HAVING COUNT(1) = COUNT(c.grp)
) d ON b.date = d.date

I'm a little too tired to write an explanation, but perhaps when I wake up tomorrow, I will continue my answer.

For now, you can view a SQLFiddle Example. Notice I have inserted four more values than there are in your example data to showcase how the query works when a set appears more than once per day.

^ You can see in the second executed query that you can also filter the frequency the set appears in each day via HAVING COUNT(1) >= 2.

Answer 2

In SAS SQL:

    proc sql;
    CREATE TABLE tbl (
      grp CHAR(1),
      ad INT,
      date DATE
    );

    INSERT INTO tbl 
    values('A', 1, '09jul2012'd) 
    values('A', 3, '09jul2012'd) 
    values('A', 1, '10jul2012'd) 
    values('A', 2, '10jul2012'd)
    values('A', 3, '10jul2012'd) 
    values('A', 4, '10jul2012'd) 
    values('A', 1, '14jul2012'd) 
    values('A', 2, '14jul2012'd) 
    values('A', 3, '14jul2012'd) 
    values('A', 1, '14jul2012'd) 
    values('A', 2, '14jul2012'd) 
    values('A', 3, '14jul2012'd) 
    ;
    quit;

    proc sql noprint; /* the set and upper date I'm interested in */
    CREATE TABLE my_set (
      grp CHAR(1),
      ad INT,
      date DATE
    );

    INSERT INTO my_set (grp, ad)
    VALUES ('A', 1)
    VALUES ('A', 2)
    VALUES ('A', 3)
    ;
    update my_set set date=today()-1;
    select count(*) into :my_set_size from my_set
    ;
    quit;

    proc sql;
    create table potential_dates as
     select t.date, s.grp, s.ad, count(*) as ad_occurrence
    from my_set s
     inner join tbl t 
        on s.grp = t.grp and s.ad = t.ad and s.date >= t.date
     group by t.date, s.grp, s.ad
    ;
    quit;

    proc sql;
        create table result as
            select a.* from potential_dates a
             inner join (select date from potential_dates
                        group by date
                        having count(*) = &my_set_size ) d
            on a.date = d.date
    ;
    quit;


 date     grp        ad  ad_occurrence
 10JUL12  A           1              1
 10JUL12  A           2              1
 10JUL12  A           3              1
 14JUL12  A           1              2
 14JUL12  A           2              2
 14JUL12  A           3              2

Answer 3

Maybe you could sort and transpose:

proc sort data=mydata1;
  by group date;
run;

proc transpose data=mydata1 out=mydata2;
  by group date;
  var ad;
run;

data mydata3;
  set mydata2;
  if not missing(col1,col2,col3);
run;

You will have one row per date. If needed, you could merge this back on to your original data:

data mydata4;
  merge mydata1 mydata3;
  by group date;
run;