时间戳

首页 > 解决方案 > SSRS 所需的 SQL 逻辑 - 来自多个结果的最大日期条目

sql - SSRS 所需的 SQL 逻辑 - 来自多个结果的最大日期条目

问题描述

我的情况是有两个表 SS 和 SSUSED。表 SS 列出了使用次数有限的所有票证。表二，SSUSED 包含表 SS 中所有票证的条目。我需要从两个表中收集某些信息但是，我只需要从表 SSUSED 中具有最近日期的行中获取数据。该列已使用。

我的代码带回了表 SSUSED 中所有使用行指定的值。有人可以帮助解决仅从 SSUSED 中的最新条目中检索所需字段的逻辑。

SELECT        SS.memid, SS.initialdate, SS.dateexpire, SS.initialquantity, SS.usedquantity, PRODUCTS.description, EMPLOYEES_1.lname AS trainlname, EMPLOYEES_1.fname AS trainfname, SS.ssid

FROM            SS INNER JOIN
                         PRODUCTS ON SS.productid = PRODUCTS.productid INNER JOIN
                         SSUSED ON SS.ssid = SSUSED.ssid INNER JOIN
                         EMPLOYEES AS EMPLOYEES_1 ON SSUSED.employeeid = EMPLOYEES_1.employeeid

WHERE (SS.initialdate BETWEEN @rvStartDate AND @rvEndDate)

标签： sqlreporting-servicesssrs-2012

解决方案

您可以使用派生列对 SSUSED 记录进行排序。然后将该表与您的数据连接起来，并在连接字段中使用最新的指示器。

;WITH MostRecentSSUSED AS
(
    SELECT ssid, employeid,
        ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY ssid DESC) AS MostRecentID
    FROM
        SSUSED
)

SELECT        SS.memid, SS.initialdate, SS.dateexpire, SS.initialquantity, SS.usedquantity, PRODUCTS.description, EMPLOYEES_1.lname AS trainlname, EMPLOYEES_1.fname AS trainfname, SS.ssid

FROM            SS INNER JOIN
                         PRODUCTS ON SS.productid = PRODUCTS.productid INNER JOIN
                         MostRecentSSUSED SSUSED ON SS.ssid = SSUSED.ssid AND SSUSED.MostRecentID = 1 INNER JOIN
                         EMPLOYEES AS EMPLOYEES_1 ON SSUSED.employeeid = EMPLOYEES_1.employeeid

WHERE (SS.initialdate BETWEEN @rvStartDate AND @rvEndDate)

您也可以尝试其他方式。根据 SSUSED 的大小，下面的查询可能会更有效，因为部分数据将首先被过滤掉，然后再进行排名。

SELECT
 *
FROM
(
    SELECT        SS.memid, SS.initialdate, SS.dateexpire, SS.initialquantity, SS.usedquantity, PRODUCTS.description, EMPLOYEES_1.lname AS trainlname, EMPLOYEES_1.fname AS trainfname, SS.ssid,
        ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY ssid DESC) AS MostRecentID

    FROM            SS INNER JOIN
                             PRODUCTS ON SS.productid = PRODUCTS.productid INNER JOIN
                             MostRecentSSUSED SSUSED ON SS.ssid = SSUSED.ssid  INNER JOIN
                             EMPLOYEES AS EMPLOYEES_1 ON SSUSED.employeeid = EMPLOYEES_1.employeeid

    WHERE (SS.initialdate BETWEEN @rvStartDate AND @rvEndDate) 
)AS X
WHERE 
    X.MostRecentID = 1

推荐阅读