我有一个查询请求，它在这里引发了一个循环。
范围：建立一个报告，告诉我们是否每6个月使用一次taskey
也就是说，Sally这个月做了x报告，她需要在6个月后再做一次。
我需要告诉我哪些超出范围，哪些超出范围的报告。
我只需要数数。
所以我做了这个视图
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
这样做是为了尝试和理解，但它只给了我符合的东西
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
我尝试了一些动态sql，但它也不起作用。
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
我就是这么做的。使用partion帮我得到了我想要的东西。
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