Create procedure using SELECT sum and JOIN

[gionna]

I am using ADO.net with SQL server. I created a stored procedure to get multiple records count. The other queries return the correct results, but the last two queries, which count the total amount of refunds in 1 year and one month are returning incorrect results(NULL). Following is my query.

    USE [jobportal]
    GO
    /****** Object:  StoredProcedure [dbo].[GetAdminReportData]    Script Date: 4/17/2022 5:50:00 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
ALTER proc [dbo].[GetAdminReportData]  
AS  

DECLARE
@JobseekersCount bigint,
@ActiveJobseekers bigint,
@DeactivatedJobseekers bigint,
@JobseekersLast365Days bigint,
@JobseekersLast30Days bigint,
@TotalRefund30Days bigint,
@TotalRefund365Days bigint

SELECT @JobseekersCount = count(1) FROM Users u WHERE u.User_Type_ID = 2 and u.is_Active = 1  
SELECT @ActiveJobseekers = count(1) FROM Users u WHERE u.User_Type_ID = 2 and Status_ID = 1 and u.is_Active = 1  
SELECT @DeactivatedJobseekers = count(1) FROM Users u WHERE u.User_Type_ID = 2 and Status_ID <> 1 and u.is_Active = 1  
SELECT @JobseekersLast365Days = count(1) FROM Users u WHERE u.User_Type_ID = 2 and Joining_Date <= getdate() and Joining_Date > getdate() - 365  
SELECT @JobseekersLast30Days = count(1) FROM Users u WHERE u.User_Type_ID = 2 and Joining_Date <= getdate() and Joining_Date > getdate() - 30
  
  
SELECT  
@JobseekersCount as JobseekersCount, @ActiveJobseekers as ActiveJobseekers, @DeactivatedJobseekers as DeactivatedJobseekers, @JobseekersLast365Days as JobseekersLast365Days, @JobseekersLast30Days as JobseekersLast30Days,
@TotalRefund365Days as TotalRefund365Days, @TotalRefund30Days as TotalRefund30Days

SELECT @TotalRefund30Days = sum(a.total_amount)

from  [dbo].[UserPurchase] a

inner join [dbo].[UserPurchaseRefunds] b

on a.user_purchase_id = b.user_purchase_id

where b.DateCreated >  (getdate() - 30)

GROUP BY a.is_refunded

HAVING a.is_refunded = 1
SELECT @TotalRefund365Days = sum(a.total_amount)

from  [dbo].[UserPurchase] a

inner join [dbo].[UserPurchaseRefunds] b

on a.user_purchase_id = b.user_purchase_id

where a.is_refunded = 1 AND b.DateCreated >  (getdate() - 380)

[dbo].[UserPurchase]

[dbo].[UserPurchaseRefunds]

[rosemadder]

You're returning the results before assigning two of the variables. The SELECT that returns all the variable values to the client should be the last statement in the procedure.

SELECT @TotalRefund30Days = sum(a.total_amount)
 from  [dbo].[UserPurchase] a
       inner join [dbo].[UserPurchaseRefunds] b 
               on a.user_purchase_id = b.user_purchase_id
 where b.DateCreated >  (getdate() - 30)
 GROUP BY a.is_refunded
 HAVING a.is_refunded = 1
SELECT @TotalRefund365Days = sum(a.total_amount)

from  [dbo].[UserPurchase] a

inner join [dbo].[UserPurchaseRefunds] b

on a.user_purchase_id = b.user_purchase_id

where a.is_refunded = 1 AND b.DateCreated >  (getdate() - 380)
SELECT  
@JobseekersCount as JobseekersCount, @ActiveJobseekers as ActiveJobseekers, @DeactivatedJobseekers as DeactivatedJobseekers, @JobseekersLast365Days as JobseekersLast365Days, @JobseekersLast30Days as JobseekersLast30Days,
@TotalRefund365Days as TotalRefund365Days, @TotalRefund30Days as TotalRefund30Days