CODEWITHSUNDEEP
sqlsql-servert-sql
smc develpments
smc develpments

Reputation: 145

Need to get Month from the date range

I wrote a SQL query that gets the date range from the current date and passes it to the condition and there returns the output value.

Additionally, I'm getting the last 3-month sales qty from the current date.

So, I want to get the month also within the date range to another column.

Any help?

    
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

Upvotes: 3

Views: 119

Answers (1)

Mohammad Kalhori
Mohammad Kalhori

Reputation: 485

Please refactor your code like this:

--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

, after you can add another month within the date range like bellow:

-- 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

After that, you can rotate the table! https://learn.microsoft.com/en-us/troubleshoot/sql/database-design/rotate-table better way: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

Upvotes: 1

Related Questions