/* helpdata constucts the ENDTRNS lines and ensures they appear at the end of each "CLASS" set */
WITH helpdata AS 
(SELECT 'ENDTRNS' trns, class, MAX(accnt) + 1 AS accnt, MAX(date) AS bookdate
FROM EXPORT_CASH_ROUGHDRAFT2 AS MT
GROUP BY  class),
/* basedata merges the helpdata with the original data */
basedata AS 
(SELECT accnt, invitem, amount, date as bookdate, class
FROM EXPORT_CASH_ROUGHDRAFT2 AS MT
UNION
SELECT accnt, NULL AS invitem, NULL AS amount, bookdate AS bookdate, class
FROM helpdata),
 /* prepares all the data for output  */
prepdata AS
(SELECT CASE WHEN invitem IS NULL THEN NULL ELSE accnt END accnt, invitem,
 amount, CASE WHEN invitem IS NULL THEN NULL ELSE bookdate END bookdate,
 CASE WHEN invitem IS NULL THEN NULL ELSE class END class,
 -1 * (ROW_NUMBER() OVER (PARTITION BY class ORDER BY bookdate, accnt) - ROW_NUMBER() OVER (ORDER BY class, bookdate, accnt)) grp, /* this identifies the data into their respective groups */
 CASE WHEN accnt = 1104 THEN 'TRNS' WHEN invitem IS NULL THEN 'ENDTRNS'
 ELSE 'SPL' END AS linetype, CASE WHEN invitem IS NOT NULL THEN 'CASH SALE' END transtype,
 ROW_NUMBER() OVER (PARTITION BY class ORDER BY bookdate, accnt) ord
 FROM basedata AS MT)

SELECT linetype, transtype, bookdate, invitem, accnt, amount, class
FROM prepdata
ORDER BY grp, ord