I am having some difficulty trying to put together a query that reports the following exceptions:
Same Day Opportunities
Note: I have specified the field names relevant to the objective in brackets to help.
1. Same product (REF_NO) delivered to a site (LOC_NO) on multiple occasions within the same day (RECEIPT_DATE) with the same code date (CODE_DATE) made up of less than a full pallet quantity (RECEIVED TI HI).
Examples:
Item 71247 was delivered in two seperate deliveries from the supplier on the 6/5/2011. The first delivery was for 22 cases, the second was for 40 cases. A full pallet contains 200 cases. If the supplier sent only one delivery with the entire 62 cartons, then the cost of receiving this order would be halved as I would only handle on pallet.
Item 90922 was delivered in two seperate deliveries from the supplier on the 4/5/2011. The first delivery was 80 cases, the second was for 52 cases. A full pallet contains 128 cases. I should have recieved the entire order on 1 pallet rather than two.
Notes:
1.The production table (ST_RECEIPT_MASTER) is very large and contains more than two million records at any given time.
2. The outputs of the final query will be exported to a users desktop in ms excel.
3. The end state query will be paramater driven using form controls as follows:
>=[Forms]![UF_REPORT_SELECTION]![DCStart] And <=[Forms]![UF_REPORT_SELECTION]![DCEnd]
>=[Forms]![UF_REPORT_SELECTION]![WeekFrom] And <=[Forms]![UF_REPORT_SELECTION
Performance is a major concern hence I require some expert assistance with this.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.
Our community of experts have been thoroughly vetted for their expertise and industry experience.