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

Jaya Prakash

 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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty2y ago

Accepted 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 <= EOMONTH(GETDATE())
AND MT.[Status] IN ('SUCCESS', 'PENDING');

-- For Last 6 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, -6, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))
AND MT.CreatedDate <= EOMONTH(GETDATE())
AND MT.[Status] IN ('SUCCESS', 'PENDING');

Naimish Makwana2y ago

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

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