I am trying to combine records based on a grouping
Attached is the example result data and below the current query arriving that that result.
SELECT g.Date,g.Period, g.Reference, g.Description, a.AccountNumber,
CASE WHEN gd.Amount > 0 THEN gd.Amount ELSE NULL END AS Debit
, CASE WHEN gd.Amount < 0 THEN - gd.Amount ELSE NULL END AS Credit, gd.Amount
FROM dbo.GLJournal AS g INNER JOIN
dbo.GLJournalDetails AS gd ON gd.JournalID = g.JournalID INNER JOIN
dbo.GLAccounts AS a ON a.AccountID = gd.GLID LEFT OUTER JOIN
dbo.GLDepts AS d ON d.DeptID = gd.DeptID LEFT OUTER JOIN
dbo.ShTransactionTypes AS t ON t.TransactionTypeID = g.TransactionTypeID
where g.Reference = 'MOAR10700' and (a.AccountNumber like '4%' or a.AccountNumber like '5%')
--order by RIGHT(a.AccountNumber , 3)
order by g.Reference
What I need is to see the result as seen in the attached based on grouping of the first 5 records. The records should only combine if they (as in the example) have the same first 5 fields and the 5th field the last 3 digits are the same. eg 4400 and 5400.
Hope this makes sense