Generate List of Month based on database record in SQL Server

Mehram
 
on Mar 02, 2021 05:08 AM
722 Views

Sir,

Need to display a list of month from date of birth to current month memberidwise

For example

Membered: 10

Membershipdate: 26/12/2013

Expire date = NULL then Current date/Month

Month & Rs.

Dec-13    50

Jan-14    50

So on……..

Jan-21   60

Feb-21  60

Mar-21  60

FeeSlabId	Amount	SDate	                EDate
1	        50	2011-01-01 00:00:00.000	2020-12-31 00:00:00.000
3	        60	2021-01-01 00:00:00.000	2021-12-31 00:00:00.000

I need to member monthly fee paid /pending dues status as per the table of fee slab month wise.

For example, received total Rs. 6000 from a person id=10 and his membership date is 25-12-2013, In view of above his payment adjustment will start from the month Dec-2013 according to the slab

Obviously, if has paid more than the dues then ahead/upcoming monthly will also show as paid out till (whatever month like: Jun-2021)

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Mar 03, 2021 07:10 AM
on Mar 05, 2021 01:33 AM

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