Hi All,
I am trying to write a select statement that is designed to pull information about SOP Orders and Invoices that haven't had the document or associated note updated in a set number of days. I also want to exclude orders that have a value other than null or 01/01/1900 in an extender window. I think I am missing something to due with nested where clauses but am having trouble figuring out exactly what it is.
Situation 1. Document hasn't been modified and the note hasn't been updated in 5 days. Display.
Situation 2. Document hasn't been modified and the note hasn't been updated in 5 days but there is a value other than null or 01/01/1900 in the E.[Must Ship By] column. Don't display.
Situation 3. Document or note has been updated in the last 5 days. Don't display.
My code so far.
select
S.sopnumbe,
S.CUSTNMBR,
S.DOCDATE,
S.BACHNUMB,
S.MODIFDT,
N.TXTFIELD,
N.DATE1,
E.[SOP Number],
E.[Must Ship By]
from SOP10100 S
inner join SY03900 N
ON S.NOTEINDX = N.NOTEINDX
left outer join JR_EXT_TKR1 E
ON S.SOPNUMBE = E.[SOP Number]
where s.MODIFDT < dateadd (day, -5,GETDATE())
AND N.DATE1 < dateadd (day, -5,GETDATE())
and S.SOPTYPE in (2,3)
and S.VOIDSTTS = 0
OR E.[Must Ship By] = '1900-01-01 00:00:00.000'
OR E.[Must Ship By] IS NULL
Hopefully my description of what I am trying to accomplish is sufficient for someone to point me in the right direction.
Thank you,
Kirk
