GROUP BY query

slmaluwa

I want to print a list like the one below from Cust. Ledger Entry table for a period.
Purpose is to print the no of unique customers the sales person touched (brought sales from) during that period.

Salesperson  NoOfUniqueCustomers
AAA                XXX
BBB                 XXX

I can't find a way to do it easily in NAV 4.0 SP3 - SQL

Pls help me to write a code to get this as my plan is to have a common "function" in a code unit.

Thank you

Maluwa

matttrax

If you just want to print it out you should investigate the GroupTotalFields property for Report data items. That way you can group by the Salesperson Code and easily display the number of customers.

If you want a function to do it you're going to have to sort the list by Salesperson and Customer and count them up every time the customer changes.

Counting the number of unique records is not one of NAV's strong areas.

Yaroslav_Gaponov

Hi

In NAV function "group by" in c/al like this just schematic

CustLedgerEntry.reset;
CustLedgerEntry.setcurrentkey("Salesperson Code","Customer No."); // need to add a key
CustLedgerEntry.setfilter(...); // any addition filters
if CustLedgerEntry.findset then
  repeat
    CustLedgerEntry.setrange("Salesperson Code",CustLedgerEntry."Salesperson Code");
    TotalCustomers := 0;
    if  CustLedgerEntry.findset then
      repeat  
        CustLedgerEntry.setrange("Customer No.",CustLedgerEntry."Customer No.");
        TotalCustomers := TotalCustomers + 1;
        CustLedgerEntry.findlast;
        CustLedgerEntry.setrange("Customer No.");    
     until CustLedgerEntry.next = 0;

    // keep a result in some table as example
    Result.init;
    Result."Salesperson Code" := CustLedgerEntry."Salesperson Code";
    Result."Total Customers" := TotalCustomers;
    Result.insert;

    CustLedgerEntry.findlast;
    CustLedgerEntry.setrange("Salesperson Code");
  until CustLedgerEntry.next = 0;

Best Regards

bangswit

if i have data like this
DATA01
DATA02
DATA01
DATA01
DATA02
DATA03

--> But i want to make like this
DATA01
DATA02
DATA03

how to do that?
like disctinct in sql syntax

klavin

The Primary Key of Cust. Ledger Entry is "Entry No.". To sort by something different your report should use a more relevant key. Not to go into writing the whole report, you can make a key with MaintainSQLIndex = false with Salesperson Code,Customer No., or find another relevant key. First DataItem SalesPerson/Purchaser table, Second indented Cust. Ledger Entry Linked by Salesperson Code.

Then you can group Cust. Ledger entry on Customer No., and have summaries for each customer under the Salesperson section.

Andwian

bangswit wrote:

if i have data like this
DATA01
DATA02
DATA01
DATA01
DATA02
DATA03

--> But i want to make like this
DATA01
DATA02
DATA03

how to do that?
like disctinct in sql syntax

recData.RESET;
recData.FINDSET;
recData2 := recData;
REPEAT
  reqUnique.SETRANGE(recData.Code);
  IF recUnique.ISEMPTY THEN BEGIN
    recData2.SETRANGE(recData.Code);
    IF recData2.FINDFIRST THEN BEGIN
      reqUnique := recData2;      
      recUnique.INSERT;
    END
  END;
UNTIL recData.NEXT = 0;

IF NOT reqUnique.ISEMPTY THEN
  REPEAT
     MESSAGE(recUnique.Code);
  UNTIL reqUnique.NEXT = 0;

bangswit

Andwian wrote:

bangswit wrote:

if i have data like this
DATA01
DATA02
DATA01
DATA01
DATA02
DATA03

--> But i want to make like this
DATA01
DATA02
DATA03

how to do that?
like disctinct in sql syntax

recData.RESET;
recData.FINDSET;
recData2 := recData;
REPEAT
  reqUnique.SETRANGE(recData.Code);
  IF recUnique.ISEMPTY THEN BEGIN
    recData2.SETRANGE(recData.Code);
    IF recData2.FINDFIRST THEN BEGIN
      reqUnique := recData2;      
      recUnique.INSERT;
    END
  END;
UNTIL recData.NEXT = 0;

IF NOT reqUnique.ISEMPTY THEN
  REPEAT
     MESSAGE(recUnique.Code);
  UNTIL reqUnique.NEXT = 0;

thanks Andiwan

Andwian

bangswit wrote:

thanks Andiwan

My pleasure, bang!

Anyway, is it works?

I have to admit that these codes are not so "efficient" though. You can try the suggested method by Yaroslav

Yaroslav Gaponov wrote:

Code: Select all
CustLedgerEntry.reset;
CustLedgerEntry.setcurrentkey("Salesperson Code","Customer No."); // need to add a key
CustLedgerEntry.setfilter(...); // any addition filters
if CustLedgerEntry.findset then
  repeat
    CustLedgerEntry.setrange("Salesperson Code",CustLedgerEntry."Salesperson Code");
    TotalCustomers := 0;
    if  CustLedgerEntry.findset then
      repeat 
        CustLedgerEntry.setrange("Customer No.",CustLedgerEntry."Customer No.");
        TotalCustomers := TotalCustomers + 1;
        CustLedgerEntry.findlast;

.

This code is more optimized to search for the only unique records, by always FINDLAST for the common records, but of course you have to set is as the current key.