none
Get top 3 payment amounts RRS feed

  • Question

  • We ran across the following SQL that does not actually work in all cases.  What we need to get are the last 3 payments for a given account (RecordID) based on PayType and PaymentFrom (only get payfrom of 2 or 3) and place them in separate variables.  Below is the code but what does not work is that if there is only 1 payment then @Payment1 and @Payment2 return the same result. Also, we need to return the 3rd payment (if any that are not the same record as @Payment1 or @Payment2).  I know this could be done with a cursor but was hoping to avoid that. I can provide table schemas if needed.

    	DECLARE @Payment1 As smallmoney;
    	DECLARE @Payment2 As smallmoney;
    	DECLARE @Payment3 As smallmoney;
    
    	SET @Payment1 = (SELECT TOP 1 CASE WHEN P.AmountPaid > 0 THEN P.AmountPaid
    										ELSE D.PayAmount
    										END
    					   FROM dbo.Payments AS P LEFT OUTER JOIN 
    					        dbo.PaymentsDetail AS D ON P.PaymentID = D.PaymentID
    					  WHERE P.RecordID = @RecordID 
    					    AND P.PayType < 10
    						AND P.PaymentFrom IN(2,3)
    					  ORDER BY P.PaymentID);
    	SET @Payment2 = (SELECT TOP 1 CASE WHEN P.AmountPaid > 0 THEN P.AmountPaid
    										ELSE D.PayAmount
    										END
    					   FROM dbo.Payments AS P LEFT OUTER JOIN 
    					        dbo.PaymentsDetail AS D ON P.PaymentID = D.PaymentID
    					  WHERE P.RecordID = @RecordID 
    					    AND P.PayType < 10
    						AND P.PaymentFrom IN(2,3)
    					  ORDER BY P.PaymentID DESC);


    Monday, May 6, 2019 1:34 PM

Answers

  • Sorry, my mistake. Try this:

    DECLARE @Payment1 smallmoney;
    DECLARE @Payment2 smallmoney;
    DECLARE @Payment3 smallmoney;
    DECLARE @Payments TABLE (
    	Amount money,
    	Ranked int
    );
    INSERT INTO @Payments
    SELECT 
    	CASE WHEN P.AmountPaid > 0 THEN P.AmountPaid ELSE D.PayAmount END AS Amount, 
    	ROW_NUMBER() OVER(PARTITION BY P.RecordID ORDER BY P.PaymentID DESC) AS Ranked					   
    FROM dbo.Payments AS P 
    LEFT OUTER JOIN dbo.PaymentsDetail AS D ON P.PaymentID = D.PaymentID
    WHERE P.RecordID = @RecordID AND P.PayType < 10 AND P.PaymentFrom IN(2, 3);
    
    
    SELECT @Payment1 = ISNULL(Amount, 0) FROM @Payments WHERE Ranked = 1;
    SELECT @Payment2 = ISNULL(Amount, 0) FROM @Payments WHERE Ranked = 2;
    SELECT @Payment3 = ISNULL(Amount, 0) FROM @Payments WHERE Ranked = 3;


    A Fan of SSIS, SSRS and SSAS

    Tuesday, May 7, 2019 6:57 PM

All replies

  • Can you post CREATE TABLE along with INSERT INTO  statements?

    Always state what version you are using.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, May 7, 2019 4:50 AM
    Answerer
  • Hi David Chase89,

     

    Thank you for your posting.

     

    Per your original script , @Payment1 is the first row and @Payment2 is the last row. Right? Why did you say that 'What we need to get are the last 3 payments for a given account (RecordID) based on PayType and PaymentFrom'?

     

    Could you  please share us your table structure and some sample data along with your expected result? So that we’ll get a right direction and make some test.

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 7, 2019 6:23 AM
  • We ran across the following SQL that does not actually work in all cases.  What we need to get are the last 3 payments for a given account (RecordID) based on PayType and PaymentFrom (only get payfrom of 2 or 3) and place them in separate variables.  Below is the code but what does not work is that if there is only 1 payment then @Payment1 and @Payment2 return the same result. Also, we need to return the 3rd payment (if any that are not the same record as @Payment1 or @Payment2).  I know this could be done with a cursor but was hoping to avoid that. I can provide table schemas if needed.

    	DECLARE @Payment1 As smallmoney;
    	DECLARE @Payment2 As smallmoney;
    	DECLARE @Payment3 As smallmoney;
    
    	SET @Payment1 = (SELECT TOP 1 CASE WHEN P.AmountPaid > 0 THEN P.AmountPaid
    										ELSE D.PayAmount
    										END
    					   FROM dbo.Payments AS P LEFT OUTER JOIN 
    					        dbo.PaymentsDetail AS D ON P.PaymentID = D.PaymentID
    					  WHERE P.RecordID = @RecordID 
    					    AND P.PayType < 10
    						AND P.PaymentFrom IN(2,3)
    					  ORDER BY P.PaymentID);
    	SET @Payment2 = (SELECT TOP 1 CASE WHEN P.AmountPaid > 0 THEN P.AmountPaid
    										ELSE D.PayAmount
    										END
    					   FROM dbo.Payments AS P LEFT OUTER JOIN 
    					        dbo.PaymentsDetail AS D ON P.PaymentID = D.PaymentID
    					  WHERE P.RecordID = @RecordID 
    					    AND P.PayType < 10
    						AND P.PaymentFrom IN(2,3)
    					  ORDER BY P.PaymentID DESC);


    What we need to get are the last 3 payments for a given account (RecordID) based on PayType and PaymentFrom (only get payfrom of 2 or 3) and place them in separate variables

    For that isn't it enough to do like this?

    DECLARE @Payment1 As smallmoney;
    	DECLARE @Payment2 As smallmoney;
    	DECLARE @Payment3 As smallmoney;
    
    SELECT @Payment1 = MAX(CASE WHEN Seq = 3 THEN Amount END),
    @Payment2 = MAX(CASE WHEN Seq = 1 THEN Amount END),
    @Payment3 = MAX(CASE WHEN Seq = 2 THEN Amount END) 
    FROM
    (	
    SELECT CASE WHEN P.AmountPaid > 0 THEN P.AmountPaid
    										ELSE D.PayAmount
    										END AS Amount,ROW_NUMBER() OVER(PARTITION BY P.RecordID ORDER BY P.PaymentID DESC) AS Seq					   FROM dbo.Payments AS P LEFT OUTER JOIN 
    					        dbo.PaymentsDetail AS D ON P.PaymentID = D.PaymentID
    					  WHERE P.RecordID = @RecordID 
    					    AND P.PayType < 10
    						AND P.PaymentFrom IN(2,3)
    )t
    WHERE Seq <= 3
    

    This will give you third last payment in @Payment1, last payment in @payment2 and intermediate payment in @Payment 3 assuming they exist


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, May 7, 2019 7:43 AM
  • The only problem I ran across with this solution is that sometimes there are less than 3 payments for a single RecordID so the following message is displayed.  I have to be able to handle 0, 1, 2 or more records.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    Tuesday, May 7, 2019 2:04 PM
  • DECLARE @Payment1 smallmoney;
    DECLARE @Payment2 smallmoney;
    DECLARE @Payment3 smallmoney;
    
    ;WITH CTE AS (
    	SELECT 
    		CASE WHEN P.AmountPaid > 0 THEN P.AmountPaid ELSE D.PayAmount END AS Amount, 
    		ROW_NUMBER() OVER(PARTITION BY P.RecordID ORDER BY P.PaymentID DESC) AS Seq					   
    	FROM dbo.Payments AS P 
    	LEFT OUTER JOIN dbo.PaymentsDetail AS D ON P.PaymentID = D.PaymentID
    	WHERE P.RecordID = @RecordID AND P.PayType < 10 AND P.PaymentFrom IN(2, 3)
    )
    
    SELECT @Payment1 = ISNULL(Amount, 0) FROM CTE WHERE Seq = 1;
    SELECT @Payment2 = ISNULL(Amount, 0) FROM CTE WHERE Seq = 2;
    SELECT @Payment3 = ISNULL(Amount, 0) FROM CTE WHERE Seq = 3;


    A Fan of SSIS, SSRS and SSAS

    Tuesday, May 7, 2019 4:18 PM
  • The SELECT @Payment2 and @Payment3 fail with 

    Invalid object name 'CTE'

    Tuesday, May 7, 2019 4:59 PM
  • Like Uri's request, you need to post sample table DDL, sample data (insert scripts) and your expected result. 

    It saves time to get question answered.  Thanks.

    Tuesday, May 7, 2019 6:31 PM
    Moderator
  • Sorry, my mistake. Try this:

    DECLARE @Payment1 smallmoney;
    DECLARE @Payment2 smallmoney;
    DECLARE @Payment3 smallmoney;
    DECLARE @Payments TABLE (
    	Amount money,
    	Ranked int
    );
    INSERT INTO @Payments
    SELECT 
    	CASE WHEN P.AmountPaid > 0 THEN P.AmountPaid ELSE D.PayAmount END AS Amount, 
    	ROW_NUMBER() OVER(PARTITION BY P.RecordID ORDER BY P.PaymentID DESC) AS Ranked					   
    FROM dbo.Payments AS P 
    LEFT OUTER JOIN dbo.PaymentsDetail AS D ON P.PaymentID = D.PaymentID
    WHERE P.RecordID = @RecordID AND P.PayType < 10 AND P.PaymentFrom IN(2, 3);
    
    
    SELECT @Payment1 = ISNULL(Amount, 0) FROM @Payments WHERE Ranked = 1;
    SELECT @Payment2 = ISNULL(Amount, 0) FROM @Payments WHERE Ranked = 2;
    SELECT @Payment3 = ISNULL(Amount, 0) FROM @Payments WHERE Ranked = 3;


    A Fan of SSIS, SSRS and SSAS

    Tuesday, May 7, 2019 6:57 PM