Create procedure using SELECT sum and JOIN



  • 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].[UserPurchase]

    [dbo].[UserPurchaseRefunds] [dbo].[UserPurchaseRefunds]



  • 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
    




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2