min(substring(cast(CustomData as nvarchar(4000)),
CharIndex(’{“Name”:“Customer Name”,“Value”:"’,cast(CustomData as nvarchar(4000)))+26,
CharIndex(’"’,cast(CustomData as nvarchar(4000)),
CharIndex(’{“Name”:“Customer Name”,“Value”:"’,cast(CustomData as nvarchar(4000)))+26
)- CharIndex(’{“Name”:“Customer Name”,“Value”:"’,cast(CustomData as nvarchar(4000)))-26)
) as [Customer Name],
min(substring(cast(CustomData as nvarchar(4000)),
CharIndex(’{“Name”:“Address”,“Value”:"’,cast(CustomData as nvarchar(4000)))+27,
CharIndex(’"’,cast(CustomData as nvarchar(4000)),
CharIndex(’{“Name”:“Address”,“Value”:"’,cast(CustomData as nvarchar(4000)))+27
)- CharIndex(’{“Name”:“Address”,“Value”:"’,cast(CustomData as nvarchar(4000)))-27)
) as [Address],
sum(AV.credit-AV.debit)
FROM Entities as e
inner join AccountTransactionValues as AV on AV.AccountId = E.AccountId
Where EntityTypeId = 1
Group by e.Name
HAVING sum(AV.credit-AV.debit) <> 0
Take a look at one of my topic from last week,
I had trouble with account balance too.
I used some SQL qmckay helped me with a script for account balance.
However mine was a little different as was merging two entity types (rooms & bookings)
If your still struggling later will have at my solution later.
The query is displaying the correct results. What result do you want to see?
If you want it collapsed to a single line for Customer Name, you need to remove Phone and Address from your query, because that Entity has 3 different phones and 3 different addresses.
@QMcKay t would be good if we can display Name,Address and balance so the way we can print it or posted the letters to them about the owing money , but here only phone and address and amount showing iam missing name which is a very important than address thanks again
In an aggregate query, you must Group By all columns except those that are aggregates (i.e. Sum, Count, etc).
When the field data does not match from 1 row/record to the next, you will not get a complete sum. Instead, it will separate rows which contain unmatched fields.
The result you show has 2 different Phones, and 2 different addresses. They must all match (name, phone, address) from 1 row to the next, or you will get the results you see.
Maybe I don’t understand what the perceived “issue” is. Make a mockup of the returned data that you want. But you will probably need to “fix” your data.
