Q:

Find if something was committed every 6 months

I have a request for a query and its throwing me for a loop here. 
Scope: Build a report that will tell us if a taskey was used every 6 months
I.e. Sally made report x this month, she needs to make that report again with in 6 months. 
I need the report that tells me which ones are out of the range and which ones where out of the range. 
I just need a count.
So I made this view 
SELECT ed.eventkey,
   ed.contactkey,
   ed.wfstagekey,
   ed.contact1id,
   ed.contact2id,
   ed.startdate,
   ed.enddate,
   ed.eventstatusid,
   ed.activeind,
   ed.modifiedbyid,
   ed.modifieddate,
       ed.ScheduledDate
  FROM STMA_CM.dbo.eventdefinition ed
 WHERE (    (ed.wftaskkey = 120 AND year (ed.enddate) = 2013)
        AND ed.eventstatusid IN (15, 16)) and activeind=1

And did this to try and get and understadning but it only gave me the ones in compliance 
select * from (select * from {{POC}} where month(enddate)=1) as A 
    left join (select * from {{POC}} where month(enddate)=2) as b on b.contactkey=a.contactkey
    left join (select * from {{POC}} where month(enddate)=3) as c on c.contactkey=a.contactkey
    left join (select * from {{POC}} where month(enddate)=4) as d on d.contactkey=a.contactkey
    left join (select * from {{POC}} where month(enddate)=5) as e on e.contactkey=a.contactkey
    left join (select * from {{POC}} where month(enddate)=6) as f on f.contactkey=a.contactkey
    left join (select * from {{POC}} where month(enddate)=7) as g on g.contactkey=a.contactkey

I tried some dynimic sql but it wasnt working either. 
BEGIN

DECLARE @ckey           INT,
            @ekey       INT,
            @cstat            INT,
            @ustat            INT,
            @pocdate1   DATETIME,
            @pocdate2   DATETIME,
            @pocdate3   DATETIME,
            @pcount           INT,
            @ecount           INT,
            @pcdate           DATETIME,
            @dcdate           DATETIME

DROP TABLE #yractive

CREATE TABLE #yractive(
contactkey  INT,
pcdate            DATETIME,
dcdate            DATETIME,
branch            VARCHAR(100),
numpoc            INT,
pocupdate1  DATETIME,
pocupdate2  DATETIME,
pocupdate3  DATETIME,
msg               VARCHAR(1000))

INSERT INTO #yractive(contactkey)
SELECT c.contactkey
FROM contact c LEFT OUTER JOIN dbo.ContactRole cr
      ON c.contactkey=cr.contactkey
WHERE c.activeind=1 AND cr.rolekey=8
      AND dbo.getContactBranch(c.contactkey) LIKE 'MA%'
      AND (YEAR(dbo.getwftaskDate(c.contactkey, 6,75))=2013
                  OR dbo.getContactStatus(c.contactkey,'ts')IN ('Placed','Suspension'))

DECLARE c_poc CURSOR FAST_FORWARD FOR
      SELECT contactkey
      FROM #yractive
FOR READ ONLY

OPEN c_poc

FETCH NEXT FROM c_poc
INTO @ckey

SELECT @cstat=@@FETCH_STATUS

WHILE @cstat<>-1
      BEGIN
            IF @cstat<>-2
                  BEGIN
                        SET @pcount=0

                        SELECT @pcount=COUNT(*)
                        FROM dbo.eventdefinition
                        WHERE wftaskkey=120
                                    AND eventstatusid =16
                                    AND contactkey=@ckey
                                    AND enddate > '2013-01-01'

                        UPDATE #yractive  
                        SET numpoc=@pcount
                        WHERE contactkey=@ckey

                        SELECT @pcdate=MAX(wfdate)
                        FROM dbo.contactworkflow
                        WHERE contactkey=@ckey AND wfstagekey=4 AND wftaskkey=49

                        UPDATE #yractive 
                        SET pcdate=@pcdate
                        WHERE contactkey=@ckey

                        SELECT @dcdate=MAX(wfdate)
                        FROM dbo.contactworkflow
                        WHERE contactkey=@ckey AND wfstagekey=6 AND wftaskkey=75

                        UPDATE #yractive 
                        SET dcdate=@dcdate
                        WHERE contactkey=@ckey

                        IF @pcount>3
                              BEGIN
                                    UPDATE #yractive  
                                    SET msg='# of pocs for this year exceeds '+LTRIM(RTRIM(CAST(@pcount AS VARCHAR(2))))
                                    WHERE contactkey=@ckey
                              END

                        IF @pcount>0
                        BEGIN

                              DECLARE c_upd CURSOR FAST_FORWARD FOR     
                                    SELECT eventkey
                                    FROM dbo.eventdefinition
                                    WHERE wftaskkey=120
                                                AND eventstatusid =16
                                                AND contactkey=@ckey 
                                                AND enddate > '2013-01-01'
                                    ORDER BY enddate DESC
                              FOR READ ONLY

                              OPEN c_upd

                              FETCH NEXT FROM c_upd
                              INTO @ekey

                              SELECT @ustat=@@FETCH_STATUS

                              SET @pocdate1=''
                              SET @pocdate2=''
                              SET @pocdate3=''

                              SET @ecount=1

                              WHILE @ustat<>-1
                                    BEGIN 
                                          IF @ustat<>-2
                                                BEGIN

                                                IF @ecount <= @pcount   
                                                      BEGIN       
                                                IF @ecount=1
                                                      BEGIN
                                                            SELECT @pocdate1=enddate
                                                            FROM dbo.eventdefinition
                                                            WHERE eventkey=@ekey

                                                            UPDATE #yractive
                                                            SET pocupdate1=@pocdate1
                                                            WHERE contactkey=@ckey                                                  

                                                      END

                                                IF @ecount=2
                                                      BEGIN
                                                            SELECT @pocdate2=enddate
                                                            FROM dbo.eventdefinition
                                                            WHERE eventkey=@ekey

                                                            UPDATE #yractive
                                                            SET pocupdate2=@pocdate2
                                                            WHERE contactkey=@ckey
                                                      END

                                                IF @ecount=3
                                                      BEGIN
                                                            SELECT @pocdate3=enddate
                                                            FROM dbo.eventdefinition
                                                            WHERE eventkey=@ekey

                                                                  UPDATE #yractive
                                                                  SET pocupdate3=@pocdate3
                                                                  WHERE contactkey=@ckey
                                                      END
                                                END

                                                SET @ecount=@ecount+1
                                    END

                                    FETCH NEXT FROM c_upd
                                    INTO @ekey

                                    SELECT @ustat=@@FETCH_STATUS

                              END

                              CLOSE c_upd
                              DEALLOCATE c_upd

                  END

                  FETCH NEXT FROM c_poc
                  INTO @ckey

                  SELECT @cstat=@@FETCH_STATUS  

            END
      END

      CLOSE c_poc
      DEALLOCATE c_poc
SELECT * FROM #yractive
END

A:

This is how I did it. Got me what i wanted using the partion helped out alot. 
select c.contactkey,c.firstname, c. lastname , ed2.enddate'second most recent', ed.enddate'most recent' 
from STMA_CM.dbo.contact c
inner join (select * from 
                  (select *,RANK() over (PARTITION BY contactkey order by enddate desc) as 'rn' 
                  from eventdefinition 
                  where wftaskkey in( 120) and eventstatusid=16) as s
                        where s.rn =1)  ed on ed.contactkey = c.contactkey
left join (select * from 
                  (select *,RANK() over (PARTITION BY contactkey order by enddate desc) as 'rn' 
                  from eventdefinition 
                  where wftaskkey  in (173,120) and eventstatusid=16) as s
                        where s.rn =2)  ed2 on ed2.contactkey = c.contactkey  
WHERE  ed.activeind=1 and ed2.activeind=1 order by contactkey

