Show me only one row per month based on rate

Sam Khera 1 Reputation point
2021-08-05T17:10:22.977+00:00

Select '20211' YearMonth,'1/31/2021' RateDate, 0.01291408 Rate Union All
Select '20211' YearMonth,'1/31/2021' RateDate, 0.01296996 Rate Union ALl
Select '20212' YearMonth,'2/28/2021' RateDate, 0.01291408 Rate Union ALl
Select '20212' YearMonth,'2/28/2021' RateDate, 0.01293617 Rate Union ALl
Select '20213' YearMonth,'3/31/2021' RateDate, 0.01289227 Rate Union ALl
Select '20213' YearMonth,'3/31/2021' RateDate, 0.01293617 Rate Union ALl
Select '20214' YearMonth,'4/30/2021' RateDate, 0.01286453 Rate Union ALl
Select '20214' YearMonth,'4/30/2021' RateDate, 0.01289227 Rate Union ALl
Select '20215' YearMonth,'5/31/2021' RateDate, 0.01286453 Rate Union ALl
Select '20215' YearMonth,'5/31/2021' RateDate, 0.01287337 Rate Union ALl
Select '20211' YearMonth,'6/30/2021' RateDate, 0.01287337 Rate

My sample data is above. I only want to see one row for each month. Currently I am seeing one rate for the current month and then one more rate for the next month.
I want to see one rate only, the next month's rate for each month. These are the rows I have. How can I best do that? Thanks.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2021-08-05T21:44:39.653+00:00

    Maybe this:

    SELECT YearMonth, RateDate, Date
    FROM   tbl a
    WHERE  EXISTS (SELECT *
                  FROM  tbl b
                  WHERE  datediff(MONTH, a.RateDate, b.RateDate) = 1
                            AND  a.Rate = b.Rate)
    
    0 comments No comments

  2. EchoLiu-MSFT 14,571 Reputation points
    2021-08-06T01:58:32.537+00:00

    Hi @Sam Khera ,

    Welcome to the microsoft TSQL Q&A forum!

    Please also check:

    CREATE TABLE #test(YearMonth int,RateDate date,Rate decimal(9,8))  
      
    INSERT INTO #test   
    Select '20211' YearMonth,'1/31/2021' RateDate, 0.01291408 Rate Union All  
    Select '20211' YearMonth,'1/31/2021' RateDate, 0.01296996 Rate Union ALl  
    Select '20212' YearMonth,'2/28/2021' RateDate, 0.01291408 Rate Union ALl  
    Select '20212' YearMonth,'2/28/2021' RateDate, 0.01293617 Rate Union ALl  
    Select '20213' YearMonth,'3/31/2021' RateDate, 0.01289227 Rate Union ALl  
    Select '20213' YearMonth,'3/31/2021' RateDate, 0.01293617 Rate Union ALl  
    Select '20214' YearMonth,'4/30/2021' RateDate, 0.01286453 Rate Union ALl  
    Select '20214' YearMonth,'4/30/2021' RateDate, 0.01289227 Rate Union ALl  
    Select '20215' YearMonth,'5/31/2021' RateDate, 0.01286453 Rate Union ALl  
    Select '20215' YearMonth,'5/31/2021' RateDate, 0.01287337 Rate Union ALl  
    Select '20211' YearMonth,'6/30/2021' RateDate, 0.01287337 Rate  
      
    SELECT t1.* FROM #test t1  
    JOIN #test t2  
    ON DATEPART(mm,t1.RateDate)=DATEPART(mm,t2.RateDate)-1   
    AND t1.Rate=t2.Rate  
    

    Output:
    121003-image.png

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments