Hi Mehram,
Refer with below query.
CREATE TABLE #tb_feeslab(FeeSlabId INT,Amount NUMERIC(18,2),SDate DATETIME,EDate DATETIME)
INSERT INTO #tb_feeslab VALUES(1,50,'2011-01-01','2020-12-31')
INSERT INTO #tb_feeslab VALUES(2,60,'2021-01-01','2021-12-31')
INSERT INTO #tb_feeslab VALUES(3,60,'2022-01-01','2023-12-31')
CREATE TABLE #Data(Month VARCHAR(10),Rs NUMERIC(18,2))
DECLARE @StartDate DATETIME, @Enddate DATETIME, @Received NUMERIC(18,2), @Remain NUMERIC(18,2)
SET @StartDate = '2013/12/26'
SET @Enddate = '2021/04/03'
SET @Received = 6000
SET @Remain = @Received
WHILE (@Remain>0)--(@StartDate <= @Enddate)
BEGIN
DECLARE @MonthDate VARCHAR(10)
SET @MonthDate = CONCAT(LEFT(DATENAME(MONTH, @StartDate), 3),'-',RIGHT(DATEPART(YEAR,@StartDate),2))
DECLARE @FeeSlab NUMERIC(18,2)
SET @FeeSlab = (SELECT TOP 1 Amount FROM #tb_feeslab WHERE @StartDate BETWEEN SDate AND EDate)
IF @Remain >= @FeeSlab
BEGIN
INSERT INTO #Data VALUES (@MonthDate, @FeeSlab)
SET @Remain = @Remain - @FeeSlab
SET @StartDate = DATEADD(MONTH, 1, @StartDate)
END
ELSE
BEGIN
INSERT INTO #Data VALUES (@MonthDate, @Remain)
BREAK;
END
END
SELECT Month,CAST(Rs AS INT) Rs FROM #Data
DROP TABLE #Data
DROP TABLE #tb_feeslab
Output
| Month |
Rs |
| 13-Dec |
50 |
| 14-Jan |
50 |
| 14-Feb |
50 |
| 14-Mar |
50 |
| 14-Apr |
50 |
| 14-May |
50 |
| 14-Jun |
50 |
| 14-Jul |
50 |
So on....
