Skip to Content

SQL Query for delinquent Purchase Order

Hello All,

Please help to create Query which gives delinquent PO considering 7 days before Line Ship Date.

Eg:

We have PO with 4 items

ItemCode Qty ShipDate

001 100 28/10/16

002 200 20/10/16

003 100 25/11/16

004 100 20/11/16

Current Date : 25/10/16

So query result should give the items 001 becuase 28/10/16 is under 7 days from current date and 002 as 20/10/16 already passed.

Regards,

Hitul

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 03, 2016 at 05:50 AM

    Hi,

    select por1.itemcode, oitm.itemname, por1.quantity, por1.ShipDate ,opor.docdate from opor inner join por1 on opor.docentry = por1.docentry inner join oitm on oitm.itemcode = por1.itemcode where datediff(d,opor.docdate,por1.shipdate) <= 7 order by opor.docdate desc

    Regards

    Narayani

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 25, 2016 at 11:31 AM

    SELECT T0.[ItemCode], T0.[Quantity], T0.[ShipDate] FROM POR1 T0 where t0.shipdate>getdate()-7

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 26, 2016 at 10:27 AM

    It´s an example.

    Adjust the query to your requirements.

    Getdate() is today date...change it to shipdate ant the number of days you want

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 04, 2016 at 11:12 AM

    Hi Hitul,

    I've used purchase order date for calculation. If you want current date then please make use of getdate function. That will help you.

    Apart from this if there is other issue please let me know.

    Will try to solve.

    Regards

    Narayani

    Add comment
    10|10000 characters needed characters exceeded