T-SQL查询只开始日期和结束日期之间的发票

Question

我有一个会计年度的开始日期和结束日期范围：16年7月1日 - 包括我的查询17年6月30日

日期字段是可以在6/30/17之外的日期付款。

例子：

Company PayonDate 
    Bose  9/30/17 <---Outside the 6/30/17 date range and will not show on report. This date will pay in advance. 

我想显示的日期范围内的最后支付日期：

例子：

Company PayonDate 
Bose  4/1/17 <----Actual paid on date vs. pay on date in the future within the date range. 

有没有办法做到这一点？

在此先感谢！

更新：

SELECT 
    ven.VendorID, 
    ven.Name AS [Vendor Name], 
    inv.ReferenceNumber AS [Ref. No], 
    inv.InvoiceNumber AS [Invoice No], 
    inv.Payment AS Payment, 
    SubMax.[Pay On Date] 
    FROM inv 
    INNER JOIN 
     (
     SELECT MAX(ips.PayOnDateTime) AS [Pay On Date], VendorID 
     from inv 
     LEFT JOIN ips ON ips.SourceID = inv.SourceID AND  ips.InvoiceID  = inv.InvoiceID 
     Group by VendorID 
     ) SubMax 
     on inv.VendorID = SubMax.VendorID 
    INNER JOIN ven ON inv.SourceID = ven.SourceID AND inv.VendorID = ven.VendorID 
    LEFT JOIN ips ON ips.SourceID = inv.SourceID AND ips.InvoiceID = inv.InvoiceID 
    WHERE FacilityID = 'HMC' 
    AND SubMax.[Pay On Date] BETWEEN @StartDate AND @EndDate 
    AND inv.ReferenceNumber LIKE '__-____' 
    ORDER BY ven.Name, SubMax.[Pay On Date] 

Answer 1

为了澄清我先前的评论：（这将是太长，不评论）

我假设IPS表包含每VENDORID日期多条支付。 因此，我们只能限制我们考虑的最大日期与期望范围内的日期？

也许我还没有完全理解这个问题。

SELECT ven.VendorID 
     , ven.Name AS [Vendor Name] 
     , inv.ReferenceNumber AS [Ref. No] 
     , inv.InvoiceNumber AS [Invoice No] 
     , inv.Payment AS Payment 
     , SubMax.[Pay On Date] 
    FROM inv 
    INNER JOIN (SELECT MAX(ips.PayOnDateTime) AS [Pay On Date] 
        , VendorID 
       FROM inv 
       LEFT JOIN ips 
        ON ips.SourceID = inv.SourceID 
       AND ips.InvoiceID = inv.InvoiceID 
       -- Can we put it here? so that max can only be payondates in your fiscal year? 
       AND ips.PayOnDateTime BETWEEN @StartDate AND @EndDate 
       GROUP BY VendorID) SubMax 
     ON inv.VendorID = SubMax.VendorID 
    INNER JOIN ven 
     ON inv.SourceID = ven.SourceID 
    AND inv.VendorID = ven.VendorID 
    LEFT JOIN ips 
     ON ips.SourceID = inv.SourceID 
    AND ips.InvoiceID = inv.InvoiceID 
    WHERE FacilityID = 'HMC' 
     --WHY HERE 
     --AND SubMax.[Pay On Date] BETWEEN @StartDate AND @EndDate 
     AND inv.ReferenceNumber LIKE '__-____' 
    ORDER BY ven.Name 
      , SubMax.[Pay On Date] 

Answer 2

我发现日期维度表是一个神发送，尤其是FY季度不开始一月1.他们增加一个额外的表加入的问题时，但他们节省了大量的处理和思考。对于一个不错的日期模糊表，请参阅Aaron Bertrand的示例 - https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/。用你认为你需要的东西来定制;我制作了几个不同的版本，有时以此为基础开始。但请记住，目标是创建一次，并在需要时使用它。

在上面的示例中，构建一个日期表，其中列出了大量日期的所有日期，并包含会计年度。然后，简单地在日期表中加入日期表，您需要引用该年的日期。

窗口函数也派上用场。这可能是这样的：

SELECT * 
FROM (
    SELECT 
      ven.VendorID 
     , ven.Name AS [Vendor Name] 
     , inv.ReferenceNumber AS [Ref. No] 
     , inv.InvoiceNumber AS [Invoice No] 
     , inv.Payment AS Payment 
     , ips.PayOnDateTime AS [Pay On Date] 
     , d.theFYYear 
     , ROW_NUMBER() OVER (PARTITION BY inv.InvoiceNumber ORDER BY ips.payOnDateTime DESC) AS rn 
    FROM ven 
    INNER JOIN inv ON ven.vendorID = inv.vendorID 
     AND inv.FacilityID = 'HMC' 
     AND inv.ReferenceNumber LIKE '__-____' 
    INNER JOIN ips ON ips.SourceID = inv.SourceID 
     AND ips.InvoiceID = inv.InvoiceID 
     AND ips.PayOnDateTime >= @StartDate 
     AND ips.PayOnDateTime <= @EndDate 
    INNER JOIN ref_DateDimension d ON ips.PayOnDateTime = d.theDate 
     AND d.theFYYear = 'FY2017' -- Or whatever FY you need current 
    ) s1 
WHERE s1.rn = 1