Customer balance Report Query

Iam trying since twodays but no luck what mistake I did ?

Select e.Name,

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],


FROM Entities as e
inner join AccountTransactionValues as AV on AV.AccountId = E.AccountId
Where EntityTypeId = 1
Group by e.Name
HAVING sum( <> 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.

1 Like

@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.

I must be missing something but how has a single entity got multiple numbers and addresses…

Exactly my thought as well.

Maybe the query must not be parsing the Custom Data properly? With all that substring/charindex stuff in there it is hard to tell.

Better to see an actual row of data, so that we could figure a better way.

Not to mention, there are some script API helpers that might work better than trying to parse the JSON in the DB.