Average Nettsales per Month

[wickyd]

Hi

I apologise, this is a cross-post with MS-SQL Server: Programming, but I need a response to this request.

I have a table which lists all the invoices we have captured.

We have ACCNUM, DATE, DOCUMENT, etc.

DOCUMENT looks like INVxxx, for invoices, CRNxxx for credIt notes.
DATE looks like dd/mm/yyyy
REP is a single integer, 1 to 9

I want a report that looks like this:

ACCNUM NETTSALES AVG NETTSALES
A001 R 100 000 R 5 000 <-- because this ACCNUM took 20 inv. at R 5000 each on average
A002 R 50 000 R 50 000 <-- this ACCNUM has only ever ordered once
A003 R 50 000 R 1 000 <-- this accnum has taken 55 invoices, 5 credit notes, and 50 invoices at an average of R1000 for each.

The SQL (using DBISAM) to get to the Nettsales is as follows:

SELECT ACCNUM, DATE, DOCUMENT,

IF(SUBSTRING(DOCUMENT FROM 1 FOR 3) = 'CRN',-TOTAL,IF(SUBSTRING(DOCUMENT FROM 1 FOR 3) = 'INV',TOTAL,0))TOTAL,
IF(SUBSTRING(DOCUMENT FROM 1 FOR 3) = 'CRN',-TOTALVAT,IF(SUBSTRING(DOCUMENT FROM 1 FOR 3) = 'INV',TOTALVAT,0))TOTALVAT
INTO RESULT FROM INVOICES

The most important thing I am looking for is the logic on how to put this together. It does not matter if your code looks different to what DBISAM supports.

Thank you.

 

[JarlH]

Create a view that returns ACCNUM and NETSALES:

create view v1 (accnum,netsales) as
select accnum,IF(SUBSTRING(DOCUMENT FROM 1 FOR 3) = 'CRN',-TOTAL,IF(SUBSTRING(DOCUMENT FROM 1 FOR 3) = 'INV',TOTAL,0))TOTAL
from invoices


Then just do

select ACCNUM,SUM(NETSALES),AVG(NETSALES) FROM v1
group by ACCNUM


(Or did I get you wrong?)