如何获得每个事项ID的最新记录列表？

I have a view that populates a form in our Extranet. We only want to see the latest data for each group of Matter Numbers.

我有一个视图,在我们的外联网中填充表格。我们只想查看每组物质编号的最新数据。

In the image below we don't want to see any event dates to the right of where it's RED because they are old events. We do want to keep the data that is to the left.

在下面的图片中,我们不希望看到任何事件发生在RED的位置,因为它们是旧事件。我们确实希望保留左侧的数据。

The rows I have highlighted in the tables are the rows that should appear in the Extranet form.

我在表中突出显示的行是应该出现在Extranet表单中的行。

Each line is a different Matter Number.

每一行都是不同的物质编号。

This is the view that was created to pull the data.

这是为拉取数据而创建的视图。

I can't figure out how to only show the latest events. Any help will be greatly appreciated.

我无法弄清楚如何只显示最新的事件。任何帮助将不胜感激。

SELECT e.eventsno        AS DocketID, 
 M1.mattercode, 
 M1.clientid, 
 M1.matterid,
 M1.AreaofLaw,
 DocketCode, 
 e.eventdate         AS StartDate, 
 e.enddate,
 et.eventdesc, 
 Cast(e.notes AS VARCHAR(4096)) AS Summary 
FROM   dbo.events AS e 
 INNER JOIN dbo.eventmatters AS em 
         ON em.events = e.events 
 INNER JOIN dbo.matters AS m 
         ON m.matters = em.matters 
 INNER JOIN dbo.eventtypes AS et 
         ON et.eventtypes = e.eventtypes 
 RIGHT OUTER JOIN dbo.vu_lp2_matters AS M1 
               ON M1.mattercode = m.matterid 
 WHERE   e.eventkind = 'D' 
 AND   e.eventtypes IN (SELECT eventtypes 
                        FROM   dbo.events 
                        WHERE  ( et.eventdesc <> 'Accounts Receivable' 
                                  OR et.eventdesc NOT LIKE 'Reminder%' ) 
     ) 
 AND ( e.eventdate >= CONVERT(DATETIME, '2014-01-01 00:00:00', 102))

3 个解决方案

#1

Thank you for your help. I ended up using a CTE to get the data to display properly. Below is the completed script.

感谢您的帮助。我最终使用CTE来正确显示数据。以下是完成的脚本。

 With cte As 
 (Select e.EventsNo, 
    m1.MatterCode,
    m1.ClientID,
    m1.MatterID,
    m1.AreaOfLaw,
    e.EventTypes    AS DocketCode, 
    e.EventDate,
    e.EndDate, 
    et.DocketDesc ,
    Cast(e.notes AS VARCHAR(4096)) Summary ,
    Row_Number() Over(Partition By MatterCode Order By EventDate Desc) AS rnLastOverAll,
    Row_Number() Over(Partition By MatterCode, DocketDesc Order By EventDate Desc) As rnLastByDocDesc
 FROM   dbo.events e 
 INNER JOIN dbo.eventmatters em ON em.events = e.events
 INNER JOIN dbo.matters m ON m.matters = em.matters 
 INNER JOIN dbo.vu_LP2_DocketCodes et ON et.DocketCode = e.eventtypes 
 RIGHT OUTER JOIN dbo.vu_lp2_matters M1 ON M1.mattercode = m.matterid 
 WHERE e.eventkind = 'D'
 )

 Select c2.EventsNo 'DocketID',c2.MatterCode,c2.ClientID,c2.MatterID, c2.AreaOfLaw,
 c2.DocketCode, c2.EventDate 'StartDate', c2.EndDate, c2.DocketDesc, c2.Summary
 From cte c1
 Inner Join cte c2 On c1.MatterCode = c2.MatterCode And c2.rnLastByDocDesc = 1  
 And (
  (c1.DocketDesc = 'Demand' And c2.DocketDesc In ('Demand'))
  Or (c1.DocketDesc = 'Complaint' And c2.DocketDesc In ('Demand', 'Complaint'))
  Or (c1.DocketDesc = 'Pre-Trial' And c2.DocketDesc In ('Demand', 'Complaint', 'Pre-Trial'))
  Or (c1.DocketDesc = 'Post Judgment' And c2.DocketDesc In ('Demand', 'Complaint', 'Pre-Trial', 'Post Judgment'))
  Or (c1.DocketDesc = 'Stop Collections' And c2.DocketDesc In ('Demand', 'Complaint', 'Pre-Trial', 'Post Judgment', 'Stop Collections'))
) Where c1.rnLastOverAll = 1

#2

You could use ROW_NUMBER/RANK to get first value for each group:

您可以使用ROW_NUMBER / RANK获取每个组的第一个值:

WITH cte AS (
  SELECT
    ROW_NUMBER() OVER(PARTITION BY M1.mattercode ORDER BY e.eventdate DESC) AS rn
    -- your complex query
)
SELECT *
FROM cte
WHERE rn = 1;

#3

Use ROW_NUMBER function to your query , order the partition by event date descending and filter records having row_number =1 :

对查询使用ROW_NUMBER函数,按事件日期降序排序分区并过滤具有row_number = 1的记录:

SELECT * from (
  SELECT e.eventsno        AS DocketID, 
     M1.mattercode, 
     M1.clientid, 
     M1.matterid,
     M1.AreaofLaw,
     DocketCode, 
     e.eventdate         AS StartDate, 
     e.enddate,
     et.eventdesc, 
     Cast(e.notes AS VARCHAR(4096)) AS Summary ,
ROW_NUMBER () OVER (PARTITION BY M1.mattercode order by e.eventdate desc) as row_no
    FROM   dbo.events AS e 
     INNER JOIN dbo.eventmatters AS em 
             ON em.events = e.events 
     INNER JOIN dbo.matters AS m 
             ON m.matters = em.matters 
     INNER JOIN dbo.eventtypes AS et 
             ON et.eventtypes = e.eventtypes 
     RIGHT OUTER JOIN dbo.vu_lp2_matters AS M1 
                   ON M1.mattercode = m.matterid 
     WHERE   e.eventkind = 'D' 
     AND   e.eventtypes IN (SELECT eventtypes 
                            FROM   dbo.events 
                            WHERE  ( et.eventdesc <> 'Accounts Receivable' 
                                      OR et.eventdesc NOT LIKE 'Reminder%' ) 
         ) 
     AND ( e.eventdate >= CONVERT(DATETIME, '2014-01-01 00:00:00', 102))
) TMP where row_no=1

#1