How to get this,monthlast month , last 3,last 6 amount data to display

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 can able to get data upto where userid=38 but when i try to filter with using datepart it is not giving any values but im having data related to date ranges pls help me to fix it

Amit Mohanty · Answer

If you're trying to retrieve the sum of RequestAmount for transactions within specific date ranges based on the current month, last month, last 3 months, and last 6 months then check the below queries. -- For Current Month 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(MONTH, MT.CreatedDate) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, MT.CreatedDate) = DATEPART(YEAR, GETDATE()) AND MT.[Status] IN ('SUCCESS', 'PENDING'); -- For Last Month 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(MONTH, MT.CreatedDate) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(YEAR, MT.CreatedDate) = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())) AND MT.[Status] IN ('SUCCESS', 'PENDING'); -- For Last 3 Months 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 MT.CreatedDate >= DATEADD(MONTH, -3, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))) AND MT.CreatedDate = DATEADD(MONTH, -6, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))) AND MT.CreatedDate

Naimish Makwana · Answer

It seems like you are trying to filter data based on the current month, last month, last 3 months, and last 6 months. The issue in your query is that you are only filtering based on the current month. To include data for the previous months, you need to adjust your date conditions. Here's an example of how you can modify your query to achieve this: 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 MT.[Status] IN ('SUCCESS', 'PENDING') AND ( -- Current month (Datepart(MM, MT.CreatedDate) = Datepart(MM, getdate()) AND Datepart(YYYY, MT.CreatedDate) = Datepart(YYYY, getdate())) OR -- Last month (Datepart(MM, MT.CreatedDate) = Datepart(MM, dateadd(MM, -1, getdate())) AND Datepart(YYYY, MT.CreatedDate) = Datepart(YYYY, dateadd(MM, -1, getdate()))) OR -- Last 3 months (MT.CreatedDate >= dateadd(MM, -2, getdate())) OR -- Last 6 months (MT.CreatedDate >= dateadd(MM, -5, getdate())) ); Thanks

Satyaprakash Samantaray · Answer

can u try below query? 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()) OR DATEPART(YYYY,MT.CreatedDate )=DATEPART(YYYY,getdate()) and MT.[Status] in ('SUCCESS','PENDING')