generate monthend date from duedate

[Deleted]

Hi,

I need to generate monthend date as of today from the due date. please advise how to do it.

sample input record

TRX_NUMBER	TRX_DATE	GL_DATE	TRX_CLASS	STATUS_TRX	DUE_DATE	INVOICE_AMOUNT	AMOUNT_REMAING	ACCT_AMOUNT_REMAING	SYSDATE_DUE
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8

output record (AS_OF_DATE AS MONTH END DATE)

TRX_NUMBER	TRX_DATE	GL_DATE	TRX_CLASS	STATUS_TRX	DUE_DATE	INVOICE_AMOUNT	AMOUNT_REMAING	ACCT_AMOUNT_REMAING	SYSDATE_DUE	AS_Of_DATE
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	10/1/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	9/30/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	8/31/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	7/31/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	6/30/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	5/31/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	4/30/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	3/31/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	2/28/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	1/31/2019
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	12/31/2018
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	11/30/2018
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	10/31/2018
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	9/30/2018
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	8/31/2018
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	7/31/2018
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	6/30/2018
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	5/31/2018
12345	4/8/2018	4/8/2018	INV	OP	4/8/2018	34388.8	0.8	0.8	0.8	4/30/2018

[#2]

@Deleted 

Experts please advise

[#3]

@Deletedquestion unclear

you ask "...monthend date as of today from the due date..."

in your example DUE_DATE constant, but AS_Of_DATE coundown by month

need more details

[#4]

Here it goes.

WITH cte AS(
SELECT EOMONTH(TRX_DATE) AS TRX_DATE FROM tablename
UNION ALL
SELECT EOMONTH(DATEADD(Month,1,TRX_DATE)) AS TRX_DATE FROM cte
WHERE TRX_DATE < DATEADD(Month,-1,GETDATE()))

SELECT *
FROM tablename
CROSS JOIN cte