Counting distinct invoices, ignoring cancelled invoices

[wickyd]

Here is a sample set of data:
[tt]
ACCNUM NAME REP DOCTYPE TOTAL
CUST005 Y 2 I 16348.00
CUST005 Y 2 C 16348.00
CUST005 Y 2 I 16208.00
CUST005 Y 2 C 16208.00
CUST005 Y 2 I 16188.00
CUST005 Y 2 C 450.00
CUST013 Z 2 I 17056.68
CUST013 Z 2 C 17056.68
CUST020 X 2 I 18650.40
[/tt]
... from this SQL statement:

SELECT ACCNUM, NAME, REP, DOCTYPE, TOTAL, TOTALVAT FROM INVOICES
WHERE REP = 2
ORDER BY ACCNUM ASCENDING, TOTAL DESCENDING

Doctype:
I = invoice
C = credit note

I want to end up with the following result:
[tt]
ACCNUM NAME REP INVCOUNT CRNCOUNT NETTSALES
CUST020 X 2 1 0 18650.40
CUST013 Z 2 0 0 0.00
CUST005 Y 2 1 1 15738.00
[/tt]
CUST020 has one invoice and no credit notes.
CUST013 has one invoice, but the order was cancelled, so he hasn't actually ordered anything.
CUST005 has two cancelled invoices, one invoice after that and one credit note. 16188 - 450 = 15738

How do I translate this into SQL?

I am using DBISAM version 3.21, so please keep the solution as rudimentary as possible.

------------------------

I currently use the following SQL, but it gives me the incorrect results with respect to INVCOUNT and CRNCOUNT:

SELECT COUNT(DOCTYPE)INVCOUNT, NAME, SUM(TOTAL - TOTALVAT)INVTOTAL INTO RESULT1 FROM INVOICES
WHERE (DOCTYPE = 'I' AND REP = 2)
GROUP BY NAME
ORDER BY NAME ASCENDING;

SELECT COUNT(DOCTYPE)CRNCOUNT, NAME, SUM(TOTAL - TOTALVAT)CRNTOTAL INTO RESULT2 FROM INVOICES
WHERE (DOCTYPE = 'C' AND REP = 2)
GROUP BY NAME
ORDER BY NAME ASCENDING;

SELECT * INTO RESULT3 FROM RESULT1 LEFT OUTER JOIN RESULT2 ON
RESULT1.NAME = RESULT2.NAME;

UPDATE RESULT3 SET CRNCOUNT = 0 WHERE CRNCOUNT IS NULL;
UPDATE RESULT3 SET CRNTOTAL = 0 WHERE CRNTOTAL IS NULL;

SELECT NAME, INVCOUNT, CRNCOUNT, (INVCOUNT - CRNCOUNT)NETTCOUNT, INVTOTAL, CRNTOTAL, (INVTOTAL - CRNTOTAL)NETTSALES FROM RESULT3
ORDER BY NETTCOUNT DESCENDING

Thank you.

Regards
wickyd

 

[dnoeth]

Is there a CASE (or something similar, e.g. IIF) in DBISAM?

SELECT ACCNUM, NAME, REP,
COUNT(CASE WHEN DOCTYPE = 'I' THEN 1 END) AS INVCOUNT,
COUNT(CASE WHEN DOCTYPE = 'C' THEN 1 END) AS CRNCOUNT,
SUM(CASE WHEN DOCTYPE = 'I' THEN Total ELSE -Total END) AS NETSALES


Dieter

 

[wickyd]

Hi dnoeth,

Unfortunately CASE is unsupported.

That would not solve my problem though, as my original code does work, but if I have two distinct invoices and two small credit notes for that customer, the NETTCOUNT is zero which is incorrect. I want the solution to say INVCOUNT = 2 and CRNCOUNT = 2.

I am trying to use my accounting software's report writer to iterate through each row, comparing variables etc. but a SQL solution would be so much faster and simpler.

Thank you.

 

[PHV]

Brute force method:
SELECT DISTINCT A.ACCNUM, A.NAME, A.REP
,(SELECT Count(*) FROM INVOICES B WHERE B.DOCTYPE='I' AND B.ACCNUM=A.ACCNUM AND B.NAME=A.NAME AND B.REP=A.REP) AS INVCOUNT
,(SELECT Count(*) FROM INVOICES C WHERE C.DOCTYPE='C' AND C.ACCNUM=A.ACCNUM AND C.NAME=A.NAME AND C.REP=A.REP) AS CRNCOUNT
,((SELECT Sum(D.TOTAL) FROM INVOICES D WHERE B.DOCTYPE='I' AND B.ACCNUM=A.ACCNUM AND B.NAME=A.NAME AND B.REP=A.REP)
-(SELECT Sum(E.TOTAL) FROM INVOICES E WHERE E.DOCTYPE='C' AND E.ACCNUM=A.ACCNUM AND E.NAME=A.NAME AND E.REP=A.REP)) AS NETTSALES
FROM INVOICES A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wickyd]

Hi PHV

I can't have multiple selects in one query. I can however run as many queries as I want separated by semi-colons whereby I write each result set to a table and then join the tables later.

See my example query above.

With that in mind, can you possibly help?

Thank you.

Regards
wickyd

 

[PHV]

You have posted in ANSI SQL forum, so you get an ANSI SQL answer.
Have you tried it ? It is very BASIC sql...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wickyd]

PHV

I appreciate your help. I certainly did try your solution but the following error appeared:

"Right parentheses expected, instead found 'Count' in source column expression."

Is there some way of changing your SQL into a way where I can write one set of results into one table, write another into another table etc. and then join them up again using certain criteria?

Thank you.

Regards
wickyd