How to get debit balance and credit from sql server in dynamic nav 2009

Hi guys i have dynamic nav 2009 and sql server 2005

I have this query show data from sales line and sales header

SELECT ROW_NUMBER() OVER (ORDER BY dbo.[Jeddah-Live$Sales Header].No_) AS [م], dbo.[Jeddah-Live$Sales Line].[Document No_] AS ‘رقم الطلب’,

dbo.[Jeddah-Live$Sales Header].[Bill-to Name] AS ‘العميل’, dbo.[Jeddah-Live$Sales Line].Area AS ‘نوع الصبه’, dbo.[Jeddah-Live$Sales Line].Description AS ‘البيان’,

dbo.[Jeddah-Live$Sales Header].[Pump No_] AS ‘المضخه’,dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity]

FROM dbo.[Jeddah-Live$Sales Header] INNER JOIN

dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].No_ = dbo.[Jeddah-Live$Sales Line].[Document No_] AND

dbo.[Jeddah-Live$Sales Header].[Sell-to Customer No_] = dbo.[Jeddah-Live$Sales Line].[Sell-to Customer No_]

WHERE DATE DIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0

The query above success work and show data based on date today

My question :I need to add debit balance and credit balance to every customer

from starting period until today

How i do that

the report i need as following

but i need query because i need to do something customization but outside dynamic

Note from dynamic nav i can do no problem

but only i need to do it from sql

this report what i need to do from sql

First your query above is wrong. You need to relate the lines based on the full primary key of the header. Don’t assume a document number is unique. It is possible to have the same document number as post an order or credit memo, as an example.

On your questions, I’d suggest looking at how NAV produced those numbers. Look to reports or pages where that information would normally be viewed in NAV. Then use the logic of that function to design your SQL queries. It will also give you something to test your queries against. For example, run a “Customer Trial Balance” or “Customer AR Aging”. Do your SQL queries yield the same results.

Thank you for reply

Can you help me in write view collect IN SQL SERVER 2005 TO GET

Sum of debit balance to every customer from (details customer ledger entry table)


Sum of credit balance to every customer from (details customer ledger entry table)


Customer No from (customer table)


Customer Name from (customer table)

Relation between customer table and details customer ledger entry table