Show me only one row per month based on rate

Question

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.

Answer 1

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)

Answer 2

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:

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.