如何在 SQL Server 2014 中获取 @StartDate - 30

Question

我有一个查询，用于生成语句，该语句显示根据日期参数(@StartDate和@EndDate(计算的月份到期金额

在

报表中，我想添加上个月的应付金额(上个月的欠款(，日期范围@StartDate - 30 到 @EndDate - 30。运行它的代码是什么？

我的代码：

set nocount on
Declare @S AS DateTime = ISNULL(@StartDate,DateAdd(d,-60,GETDATE()))
Declare @anum as nvarchar(8) = ISNULL(@panum,'25991275')
Declare @E AS DateTime = ISNULL(@EndDate,DateAdd(d,-0,GETDATE()))
SELECT A.AccountNumber
      ,C.FirstName + ' ' + C.LastName CustName
      ,[InvoiceNumber]
      ,[StatementDate]
      ,[NewCharges]
      ,[AmountDue]
      ,[Charges]
      ,[AccountFee]
      ,[Interest]
      ,[Payments]
      ,[Refunds]
      ,[DueDate]

FROM [StatementSummary] S
INNER JOIN Account A ON S.AccountID = A.Accountid
INNER JOIN Contact C ON A.AccountId = C.AccountId

WHERE A.AccountNumber = @anum
  AND StatementDate >= @S
  AND StatementDate <= @E
ORDER BY StatementDate DESC

我正在考虑制作另一个数据集来运行以下代码：

  SELECT Top 1 AcctBalance    
  FROM [FinMaster]
  WHERE AcctID = @anum  
    AND BusinessDay >= @S - 30  
    AND BusinessDay <= @E - 30
  ORDER BY AcctBalance DESC

如何将日期范围添加回上个月的日期范围？

如果可以将第二个代码添加为第一个代码中的一行，则无需为报表创建第二个数据集。

Answer 1

使用 OUTER APPLY 和 EOMonth 函数获取"上个月"值
只是一个逻辑，不使用你的字段

declare @reportdate date = getdate()
select a.*, x.field....
 from table1 A
   OUTER apply (        --- to get last month data, can be null.. similar to left outer join but in a cartesian way of display
     select b.field1, b.field2, b.... 
     from table1 B
     where 
     b.product_id = a.product_id  and
     trans_date
     between   -- between last month based on the @reportdate 
     dateadd(day,1,eomonth(dateadd(month,-2,@reportdate)))   -- or      a.trans_date
    and 
    eomonth(dateadd(month,-1,@reportdate))
    ) x
where trans_date 
  between    -- your reporting date, can be any date
  dateadd(day,1,eomonth(dateadd(month,-1,@reportdate)))
  and eomonth(@reportdate)