How to Get this month,last month,last 3 and last 6 mnts amount to display in my dashboard

Question

SELECT SUM(RequestAmount)
FROM Transactions MT
    INNER JOIN User_Heirarchy_Details UHD ON MT.UserID = UHD.UserID
WHERE UHD.ParentUserID IN (SELECT ParentUserID
                           FROM User_Heirarchy_Details
                           WHERE UserID = 38)
  AND DATEPART(MM, MT.CreatedDate) = DATEPART(MM, GETDATE())
  AND DATEPART(YYYY, MT.CreatedDate) = DATEPART(YYYY, GETDATE())
  AND MT.[Status] IN ('SUCCESS', 'PENDING')

I was getting amount data up to where userid=38 but when I try to filter with date from this step I'm not able to get the calculated amount and I'm having data related to date ranges.

    Datepart(MM,MT.CreatedDate )=dATEPART(MM,getdate())
AND Datepart(YYYY,MT.CreatedDate )=dATEPART(YYYY,getdate())

Any suggestion?

Answer 1

1. This Month

AND DATEPART(MM, MT.CreatedDate) = DATEPART(MM, GETDATE())
AND DATEPART(YYYY, MT.CreatedDate) = DATEPART(YYYY, GETDATE())

or

AND DATEPART(month, MT.CreatedDate) = DATEPART(month, GETDATE())
AND DATEPART(year, MT.CreatedDate) = DATEPART(year, GETDATE())

2. Last Month

AND DATEPART(MM, MT.CreatedDate) = DATEPART(MM, DATEADD(MM, -1, GETDATE()))
AND DATEPART(YYYY, MT.CreatedDate) = DATEPART(YYYY, DATEADD(MM, -1, GETDATE()))

or

AND DATEPART(month, MT.CreatedDate) = DATEPART(month, DATEADD(month, -1, GETDATE()))
AND DATEPART(year, MT.CreatedDate) = DATEPART(year, DATEADD(month, -1, GETDATE()))

3. Last 3 Months

AND MT.CreatedDate >= DATEADD(MM, -3, GETDATE())
AND MT.CreatedDate < GETDATE()

or

AND MT.CreatedDate >= DATEADD(month, -3, GETDATE())
AND MT.CreatedDate < GETDATE()

4. Last 6 Months

AND MT.CreatedDate >= DATEADD(MM, -6, GETDATE())
AND MT.CreatedDate < GETDATE()

or

AND MT.CreatedDate >= DATEADD(month, -6, GETDATE())
AND MT.CreatedDate < GETDATE()