SQLTeam.com | Weblogs | Forums

Where exists problem


#1

I need to know when a part/serial combo comes into my shop that I shipped out of my shop within the past 365 days.

Does this script look right? It's not returning anything, plus I need to compare ship date/added date into this.

example:
part number A with serial number 12 had shipped_date of 4/3/2017(we shipped the unit out to customer)
part number A with serial number 12 has added_date of 12/01/2017(customer sent it back within 365 days)
I need the script to return this example, letting me know I received a unit in that I shipped out within 365 days.

select wh.doc_no, wh.partnumber, wh.SERIAL_NO
from WO_HDR wh
where exists
  (select wh1.DOC_NO, wh1.PARTNUMBER, wh1.SERIAL_NO
     from WO_HDR wh1
     where wh.DOC_NO = wh1.DOC_NO
     and wh1.shipped_date > getdate()-365
     and wh.PARTNUMBER = wh1.partnumber
     and wh.SERIAL_NO = wh1.serial_no
     and wh.SHIPPED_DATE <> wh1.SHIPPED_DATE)

#2

I thought I got it working but it's not. Any help would be helpful. Thank you


#3

Got this to work. Sorry

select wh.doc_no, we.PN_RCV_AS, wh.SERIAL_NO, wh.ACCTNO
from WO_HDR wh
join WO_EXPEDITE we on (wh.DOC_NO = we.DOC_NO)
where exists
  (select wh1.DOC_NO
     from WO_HDR wh1
        join WO_EXPEDITE we1 on (wh1.DOC_NO = we1.DOC_NO)
     where wh.DOC_NO <> wh1.DOC_NO
     and wh1.APPROVED_DATE > getdate()-365
     and we.PN_RCV_AS = we1.PN_END_AS
     and wh.SERIAL_NO = we1.PN_END_SERIAL_NO
     and wh.ADDED_DTE > wh1.APPROVED_DATE
     and not wh.ACCTNO is null)
 order by wh.ADDED_DTE desc