Think with me for exception reporting

loisellec
loisellec used Ask the Experts™
on
I am working on a project for the newspaper I work for.  We have 2 systems, one for ordering ads and one for what makes the paper and how it's laid out (production system).  We have moved production system data into the ordering system in custom tables.   So I have tables with our order data and tables with our production data.  Great!  

Now how do I do an unmatched query where either dates don't match (ie... something was ordered but didn't make the production system) or publications don't match, or ad size doesn't match, or color does not match?  Can this be done in Crystal Reports?  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Do you have any way of referencing the order to what was done on the production system? If you have some sort of key field then you could write some query's

Author

Commented:
Yes, they both have an order number that joins.  However... we want to also see if any orders didn't make it to the other system (so production would have no data) or any order in production that were not ordered in the ordering system.  So Im not sure joining on any field will give us the results we need... unless through Crystal I need to write 5 reports or something.

I would do something like the following, at least for the non-existant orders.

select * from order table where order number not in (select * from production orders)

as for the other query you could just do an inner join and use some logic to evaluate if the size was the same etc.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Author

Commented:
So logically it could not be done on the same report?  

If I wrote a bunch of reports could I take one main report and list them all on it as subreports so all the info could be pulled at once?  Something like that?  
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can also use a LEFT OUTER JOIN to get the information that didn't make it.

mlmcc

Author

Commented:
Yes, I could... and suppress the ones that match on other criteria... but what do I join on?  I think I need a full outer join where I can get records from each table that are not in the other table.  Does that make sense?  
If they are all on one table i'd join on the order number :P

Author

Commented:
If I linked orders to production by orderno with a left outer join how would I get records from the production table that are not in the orders table?  
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
If you have records in production that aren't in the orders table then you will hve to use another method or 2 reports.  Crystal doesn't support a full outer join.

Why do records exist in production that didn't have an order?

you could do something like this with a COMMAND or view in the database

SELECT ORDERS.*, PRODUCTION.*
FROM
ORDERS LEFT OUTER JOIN PRODUCTION ON Orders.OrderId = Production.OrderId
UNION
SELECT ORDERS.*, PRODUCTION.*
FROM
PRODUCTION LEFT OUTER JOIN ORDERS ON Production.OrderId = Orders.OrderId

mlmcc

Author

Commented:
GREAT!  I needed some ideas.  

The reason that happens is we have a dept that takes orders and pulls them down and lays out the paper (dummy).  Sometimes a manager or sales rep will bring in a late ad and they will put it on the dummy of the paper, but the rep or manager will forget to order the ad in the ordering system after it does make the paper.  Then ads get in the paper and run but were never ordered and never will be billed.  
Sometimes ads won't make the paper because in the ordering system the rep will forget to take the ad off hold or they will order it for a section that is not in that days paper, or they will order it late and they didn't realize the dummy was already pulled and forget to tell the other dept to repull the dummy so the late order makes the paper.   There are numerous other reasons for the mismatches, sometimes ad size ordered does not match the ad size that made the paper, or the page number it was ordered for is not the page number it appeared on, etc...

 Its rare this happens but everyday accounting physically goes in and measures every ad in the paper and matches the actual physical paper to the ordering system to make sure everything matches, this is time consuming and outdated.  If we can match the 2 systems developmentally then they will only have to check on stuff that comes out on an exception report.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial