sql - 需要从日期范围中获取月份

Question

我编写了一个 SQL 查询，从当前日期获取日期范围并将其传递给条件，然后返回输出值。

此外，我还获得了从当前日期算起的最近 3 个月的销售数量。

所以，我想把月份也放在另一列的日期范围内。

有什么帮助吗？

    
DECLARE @LastMonthQty Decimal, @LastSecMonthQty Decimal, @LastThrdMonthQty Decimal

DECLARE 
@LstMnthStartDate DATETIME = null, 
@LstMnthEndDate DATETIME = null
SET @LstMnthStartDate= CAST(DATEADD(DAY,1,EOMONTH(GETDATE(),-2))AS DATE) 
SET @LstMnthEndDate =CAST(EOMONTH(GETDATE(),-1) AS DATE)
    

SET @LastMonthQty=  (SELECT SUM (R.Issued_Qty)
    FROM LCL_INVN.dbo.LCL_ReqSub R
    WHERE R.ItmId=@ItmId AND CAST(R.Issued_Date AS DATE) BETWEEN @LstMnthStartDate AND @LstMnthEndDate AND R.Status=1 )

DECLARE 
@LstSecMStartDate DATETIME = null, 
@LstSecMEndDate DATETIME = null
SET @LstSecMStartDate= DATEADD(DAY,1,EOMONTH(GETDATE(),-3)) 
SET @LstSecMEndDate =EOMONTH(GETDATE(),-2) 

SET @LastSecMonthQty=   (SELECT SUM (R.Issued_Qty)
    FROM LCL_INVN.dbo.LCL_ReqSub R
    WHERE R.ItmId=@ItmId AND CAST(R.Issued_Date AS DATE) BETWEEN @LstSecMStartDate AND @LstSecMEndDate AND R.Status=1 )

DECLARE 
@LstThrMStartDate DATETIME = null, 
@LstThrMEndDate DATETIME = null
SET @LstThrMStartDate= DATEADD(DAY,1,EOMONTH(GETDATE(),-4)) 
SET @LstThrMEndDate =EOMONTH(GETDATE(),-3) 

SET @LastThrdMonthQty=(SELECT SUM (R.Issued_Qty)
    FROM LCL_INVN.dbo.LCL_ReqSub R
    WHERE R.ItmId=@ItmId AND CAST(R.Issued_Date AS DATE) BETWEEN @LstThrMStartDate AND @LstThrMEndDate AND R.Status=1 )

SELECT ISNULL(@LastMonthQty,0) AS LstQty, ISNULL(@LastSecMonthQty,0) AS SecQty, ISNULL(@LastThrdMonthQty,0) AS ThrQty

Answer 1

请像这样重构您的代码:

--for example  --> 
--DECLARE @1stDate  DATETIME = CAST(GETDATE()-1 AS DATE) 
--DECLARE @2stDate  DATETIME = CAST(EOMONTH(GETDATE()-1) AS DATE) 


SELECT SUM(R.Issued_Qty),R.ItmId,R.Issued_Date
    FROM LCL_INVN.dbo.LCL_ReqSub R
    GROUP BY R.ItmId,CAST(R.Issued_Date AS DATE)
    WHERE  CAST(R.Issued_Date AS DATE) IN ('@1stDate','@2ndDate',.....)
    AND R.ItmId=@ItmId 

，之后您可以在日期范围内添加另一个月份，如下所示:

-- R.xxx_Date is your new month column
SELECT SUM(R.Issued_Qty),R.ItmId,R.Issued_Date,R.xxx_Date
    FROM LCL_INVN.dbo.LCL_ReqSub R
    GROUP BY R.ItmId,CAST(R.Issued_Date AS DATE),CAST(R.xxx_Date AS DATE)
    WHERE  CAST(R.Issued_Date AS DATE) IN ('@1stDate','@2ndDate',.....)
    AND CAST(R.xxx_Date AS DATE) IN ('@1stDate','@2ndDate',.....)
    AND R.ItmId=@ItmId 

之后就可以旋转 table 了! https://learn.microsoft.com/en-us/troubleshoot/sql/database-design/rotate-table更好的方法: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15