Query Help Crystal 2011 For Sage

Hello Everyone,

I have a need to report on all open quotes that have not generated a sale.
Our system keeps quotes and sales orders all in the same table, and we have a User defined field that gets populated when an order is copied from a quote or another order showing a sale or additional sales.
The query is being established from a Command table, as I cannot think of another way to query the same table twice to filter the records.

Below is how I have the report query laid out right now, but as my table has tens of thousands of records the query takes forever and a day to complete.

Objective:
Report all Quote records [“SalesOrderNo” =“Q”] that do not have a matching “UDF_Copied_From” record from the same table.

[code] SELECT “SO_SalesOrderHeader”.“SalesOrderNo”, “SO_SalesOrderHeader”.“OrderDate”, “SO_SalesOrderHeader”.“OrderType”, “SO_SalesOrderHeader”.“ConfirmTo”
FROM “SO_SalesOrderHeader” “SO_SalesOrderHeader”
WHERE “SO_SalesOrderHeader”.“OrderType”=‘Q’ AND “SO_SalesOrderHeader”.“SalesOrderNo” NOT IN ( SELECT DISTINCT “SO_SalesOrderHeader”.“UDF_COPIED_FROM”
FROM “SO_SalesOrderHeader” “SO_SalesOrderHeader”
WHERE “SO_SalesOrderHeader”.“UDF_COPIED_FROM”<> ‘""’ AND “SO_SalesOrderHeader”.“SalesOrderNo”<>‘Q’) [code/]

Any help is greatly appreciated.

Thanks,

BWolosz


BWOLOSZ (BOB member since 2019-09-16)