Skip to Content
0

SQL Query for delinquent Purchase Order

Oct 25, 2016 at 10:35 AM

85

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

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

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Narayani,

Though it haven't gave me the exact result, but it is very helpful.

Thanks & Regards,

Hitul

0
Gonzalo Gomez Oct 25, 2016 at 11:31 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Not giving correct result.

It is givng all the items which has ship date greater than 19th Oct.

0
Gonzalo Gomez Oct 26, 2016 at 10:27 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 04, 2016 at 11:12 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Narayani.

0