Open Sales Order Query

[Former Member]

Hello Experts,

I would like to pull all Open Sales Orders that do not have purchase orders and are more than 180 days old.

I tried the following query but does not seems to work exactly:

select t0.DocNum

,t0.DocDate

,DateAdd(Day,180,t0.DocDate) as Due180Days

, t0.DocDueDate

, t1.PymntGroup

, t0.OwnerCode

, t2.firstName

, t2.lastName from ordr t0

inner join OCTG t1 on t0.GroupNum = t1.GroupNum

inner join OHEM t2 on t0.OwnerCode = t2.empID

where (t1.PymntGroup like '%prepaid%'

or t1.PymntGroup like '%deposit%')

and t0.DocStatus ='O'and t0.PoPrss='N' and DATEADD(Day,180,t0.DocDate)>180

and t0.OwnerCode=18

Any hlep would be appreciated.

Regards,

Praneeth

[Former Member · Accepted solution]

Hello Praneeth,

You may try this:

select t0.DocNum,t0.DocDate,DateAdd(Day,180,t0.DocDate) as Due180Days,

t0.DocDueDate, t1.PymntGroup, t0.OwnerCode, t2.firstName, t2.lastName

from ordr t0

inner join OCTG t1 on t0.GroupNum = t1.GroupNum

inner join OHEM t2 on t0.OwnerCode = t2.empID

where (t1.PymntGroup like '%prepaid%'

or t1.PymntGroup like '%deposit%')

and t0.DocStatus ='O'and t0.PoPrss='N' and DATEDIFF(DD,t0.DocDate,GetDate())>180

and t0.OwnerCode=18

What is exactly the issue not seems to work?

Thanks,

Gordon

[Former Member · Accepted solution]

Hello Gordon,

The problem was with DateAdd function that i had in my query. I replaced with DateDiff as you advised. Works fine.

Thanks a lot !

Praneeth