generate monthend date from duedate

%3CLINGO-SUB%20id%3D%22lingo-sub-888411%22%20slang%3D%22en-US%22%3Egenerate%20monthend%20date%20from%20duedate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888411%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20generate%20monthend%20date%20as%20of%20today%20from%20the%20due%20date.%20please%20advise%20how%20to%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esample%20input%20record%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ETRX_NUMBER%3C%2FTD%3E%3CTD%3ETRX_DATE%3C%2FTD%3E%3CTD%3EGL_DATE%3C%2FTD%3E%3CTD%3ETRX_CLASS%3C%2FTD%3E%3CTD%3ESTATUS_TRX%3C%2FTD%3E%3CTD%3EDUE_DATE%3C%2FTD%3E%3CTD%3EINVOICE_AMOUNT%3C%2FTD%3E%3CTD%3EAMOUNT_REMAING%3C%2FTD%3E%3CTD%3EACCT_AMOUNT_REMAING%3C%2FTD%3E%3CTD%3ESYSDATE_DUE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eoutput%20record%20(AS_OF_DATE%20AS%20MONTH%20END%20DATE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ETRX_NUMBER%3C%2FTD%3E%3CTD%3ETRX_DATE%3C%2FTD%3E%3CTD%3EGL_DATE%3C%2FTD%3E%3CTD%3ETRX_CLASS%3C%2FTD%3E%3CTD%3ESTATUS_TRX%3C%2FTD%3E%3CTD%3EDUE_DATE%3C%2FTD%3E%3CTD%3EINVOICE_AMOUNT%3C%2FTD%3E%3CTD%3EAMOUNT_REMAING%3C%2FTD%3E%3CTD%3EACCT_AMOUNT_REMAING%3C%2FTD%3E%3CTD%3ESYSDATE_DUE%3C%2FTD%3E%3CTD%3EAS_Of_DATE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E10%2F1%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E9%2F30%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E8%2F31%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E7%2F31%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E6%2F30%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E5%2F31%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E4%2F30%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E3%2F31%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E2%2F28%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E1%2F31%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E12%2F31%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E11%2F30%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E10%2F31%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E9%2F30%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E8%2F31%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E7%2F31%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E6%2F30%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E5%2F31%2F2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12345%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3EINV%3C%2FTD%3E%3CTD%3EOP%3C%2FTD%3E%3CTD%3E4%2F8%2F2018%3C%2FTD%3E%3CTD%3E34388.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E4%2F30%2F2018%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-890436%22%20slang%3D%22en-US%22%3ERe%3A%20generate%20monthend%20date%20from%20duedate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890436%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418610%22%20target%3D%22_blank%22%3E%40jv123980%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExperts%20please%20advise%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-890878%22%20slang%3D%22en-US%22%3ERe%3A%20generate%20monthend%20date%20from%20duedate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890878%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418610%22%20target%3D%22_blank%22%3E%40jv123980%3C%2FA%3Equestion%20unclear%3C%2FP%3E%3CP%3Eyou%20ask%20%22...monthend%20date%20as%20of%20today%20from%20the%20due%20date...%22%3C%2FP%3E%3CP%3Ein%20your%20example%20DUE_DATE%20constant%2C%20but%20AS_Of_DATE%20coundown%20by%20month%3C%2FP%3E%3CP%3Eneed%20more%20details%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-983695%22%20slang%3D%22en-US%22%3ERe%3A%20generate%20monthend%20date%20from%20duedate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-983695%22%20slang%3D%22en-US%22%3EHere%20it%20goes.%3CBR%20%2F%3E%3CBR%20%2F%3EWITH%20cte%20AS(%3CBR%20%2F%3ESELECT%20EOMONTH(TRX_DATE)%20AS%20TRX_DATE%20FROM%20tablename%3CBR%20%2F%3EUNION%20ALL%3CBR%20%2F%3ESELECT%20EOMONTH(DATEADD(Month%2C1%2CTRX_DATE))%20AS%20TRX_DATE%20FROM%20cte%3CBR%20%2F%3EWHERE%20TRX_DATE%20%26lt%3B%20DATEADD(Month%2C-1%2CGETDATE()))%3CBR%20%2F%3E%3CBR%20%2F%3ESELECT%20*%3CBR%20%2F%3EFROM%20tablename%3CBR%20%2F%3ECROSS%20JOIN%20cte%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
Deleted
Not applicable

Hi,

 

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

 

sample input record

         

TRX_NUMBERTRX_DATEGL_DATETRX_CLASSSTATUS_TRXDUE_DATEINVOICE_AMOUNTAMOUNT_REMAINGACCT_AMOUNT_REMAINGSYSDATE_DUE
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.8

 

 

output record (AS_OF_DATE AS MONTH END DATE)

 

          

TRX_NUMBERTRX_DATEGL_DATETRX_CLASSSTATUS_TRXDUE_DATEINVOICE_AMOUNTAMOUNT_REMAINGACCT_AMOUNT_REMAINGSYSDATE_DUEAS_Of_DATE
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.810/1/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.89/30/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.88/31/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.87/31/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.86/30/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.85/31/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.84/30/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.83/31/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.82/28/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.81/31/2019
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.812/31/2018
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.811/30/2018
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.810/31/2018
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.89/30/2018
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.88/31/2018
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.87/31/2018
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.86/30/2018
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.85/31/2018
123454/8/20184/8/2018INVOP4/8/201834388.80.80.80.84/30/2018
3 Replies
Highlighted

@Deleted 

 

Experts please advise

Highlighted

@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

 

Highlighted
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