SQL Database Statistics

[rinzana]

Hello,

Has anyone written SQL queries to report on SQL database statistics? Like how many Sales Orders received how many sales order line items received, Purchase Orders and Purchase Order Line Item over a specific time period? These queries could get pretty involved with blankets purchase orders and invoiced sales. SQL is not my strength and would prefer to not re-invent the wheel if someone already has queries like this already.

Thank You,
Rich

 

[DARDIT]

This will get you the order count, grouped by Order Type, in date range shown.
It queries the Consolidate OE View table, so you get open and invoiced orders.

SELECT COUNT(ord_no) AS ord_count, ord_type
FROM OEINQORD_SQL
WHERE (ord_dt >= 20120101) AND (ord_dt <= 20120531)
GROUP BY ord_type

Bart

 

[dgillz]

Orders cease to exist in the OEORDHDR_SQL table once the are fully invoiced. You'll need to query the OEINQORD_SQL table to get all orders, open, shipped or partially shipped.

SQL select queries are easy to learn, you should ask your Macola business partner or Exact regional office for about 4 hours of training on it, and you'll be good to go.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html