How to get debit balance and credit from sql server in dynam

ahmedbaahmedba Member Posts: 424
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

Comments

  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • ahmedbaahmedba Member Posts: 424
    Thank you for reply
    if i need to make query
    How i do
    if it is wrong
    show to me the true if possible
  • bbrownbbrown Member Posts: 3,268
    Add "Document Type" to your join relation. The primary key for the header is ["Document Type", "No."]. You need to include both in your join to insure line records for other documents are not included.
    There are no bugs - only undocumented features.
  • ahmedbaahmedba Member Posts: 424
    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)
    AND
    Sum of credit balance to every customer from (details customer ledger entry table)
    AND
    Customer No from (customer table)
    AND
    Customer Name from (customer table)
    Relation between customer table and details customer ledger entry table
  • FaulconFaulcon Member Posts: 19
    Isn't that what the "Credit Amount" and "Debit Amount" fields on the Customer table already do? If you want to display it externally and not within Navision then you need to take the CalcFormula property from those fields and translate it to a SQL statement.

    eg Debit Amount CalcFormula is:
    Sum("Detailed Cust. Ledg. Entry"."Debit Amount" WHERE (Customer No.=FIELD(No.),Entry Type=FILTER(<>Application),Initial Entry Global Dim. 1=FIELD(Global Dimension 1 Filter),Initial Entry Global Dim. 2=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(Date Filter),Currency Code=FIELD(Currency Filter)))
    

    Which would be (ignoring global dim 1, 2, Posting Date - since you want everything -, and currency code):
    SELECT     SUM([Debit Amount]) AS SumDebitAmount, [Customer No_]
    FROM         [<Company>$Detailed Cust_ Ledg_ Entry]
    GROUP BY [Customer No_], [Entry Type]
    HAVING      ([Entry Type] <> 2)
    

    Change the HAVING line to "HAVING ([Entry Type] <> 2) AND ([Customer No_] = '<CustomerNo>')" if you want a specific customer, not just a full list.

    To get both Debit and Credit just make the select statement read
    SELECT SUM([Debit Amount]) AS SumDebitAmount, SUM([Credit Amount]) AS SumCreditAmount, [Customer No_]

    And finally including the Customer Name:
    SELECT     SUM([<Company>$Detailed Cust_ Ledg_ Entry].[Debit Amount]) AS SumDebitAmount, 
                          SUM([<Company>$Detailed Cust_ Ledg_ Entry].[Credit Amount]) AS SumCreditAmount, 
                          [<Company>$Detailed Cust_ Ledg_ Entry].[Customer No_], [<Company>$Customer].Name
    FROM         [<Company>$Detailed Cust_ Ledg_ Entry] INNER JOIN
                          [<Company>$Customer] ON [<Company>$Detailed Cust_ Ledg_ Entry].[Customer No_] = [<Company>$Customer].No_
    GROUP BY [<Company>$Detailed Cust_ Ledg_ Entry].[Customer No_], [<Company>$Detailed Cust_ Ledg_ Entry].[Entry Type], 
                          [<Company>$Customer].Name
    HAVING      ([<Company>$Detailed Cust_ Ledg_ Entry].[Entry Type] <> 2)
    
Sign In or Register to comment.