Sunday, March 13, 2016

Priority tip of the day

I wanted to write a report which showed to how many customers we were supplying on a given day. This number is not the same as the number of delivery notes, as a customer may have more than one delivery note on a given day. In standard SQL, this shouldn't be too great a problem, as one can use the 'COUNT' function with the customer number - select count (cust) from documents. This does not work in the Priority flavour of SQL.

What does work, however, is a variation on the above - count (distinct cust). So the query becomes (complete with parameters)
SELECT SQL.LINE, SUM (QPRICE), CURDATE, COUNT (DISTINCT CUST) FROM DOCUMENTS WHERE TYPE = 'D' AND FINAL = 'Y' AND CURDATE BETWEEN :$.FDT AND :$.TDT GROUP BY CURDATE;

No comments: