i need to create a report. where the column descriptions are given and need to write a query for the report using sequel. This is the description for creating the report. and attached file is to be excepted.
Salesrep - should be required use code "LC " as your test (table MMMSTSMP)
Year - Required and Default to current year
Account - Optional - "W441A" is Walmart and a good test account
Box 1: Top accounts by shipped LY volume
Account name will be from the NAACCTP field (ACACCT and ACNAME)
Booked TY will be
the sum of the IVR00F (Field IVR$) for the current year (IVRDAT8) plus
the sum of the ivmstdtl (DTLINQ * DTSLS$) where dtpksh = "B" or "P" plus
the sum of the ormstdtl (DTLINQ * DTSLS$)
Shipped TY will be
the sum of the IVR00F (Field IVR$) for the current year (IVRDAT8)
Shipped LY will be
the sum of the IVR00F (Field IVR$) for the prior year (IVRDAT8)
Booked LY will be
the sum of the IVR00F (Field IVR$) for the prior year (IVRDAT8) plus
the sum from DTEXT$ from the fpopoh file from the nearest weekend (DTWEDT)
The FPOPOH file is a snapshot of our order file. It is taken every Saturday. You will need to take the current date, go back 1 year and then find the next freeze date. If you ran it today, you should only read the records from 2018-02-10.
Note- In the attached filed i'm looking for top accounts report.