many records one problem

[KOVMoe]

I am trying to pull one line per package. I get a record for every scan, can be up to 10 scans. I just want the last one.

pkg_tck_nr is unique to package, but not to database.

code:
SELECT E.PKG_TCK_NR, E.SHR_AC_NR,D.DY_DT,E.PKG_XCP_RPT_DT
,E.PKG_XCP_RPT_TM,E.PKG_XCP_RPT_OGZ_NR
,E.LOC_SYS_NR,E.PKG_TCK_NR,E.USR_NR
,E.UTC_TCF_MT_QY,E.PKG_XCP_RSN_CD
,E.SRT_TYP_CD,S.STP_CML_TM,S.DY_DT
,E.PKG_DTN_OGZ_NR
FROM DDAT0033.DBO.TECSDAT E
INNER JOIN DDAT0013.DBO.TDIRPKD_DELVPKG D ON (E.PKG_TCK_NR = D.PKG_TCK_NR)
INNER JOIN DDAT0013.DBO.TDIRSTD_DELVSTOP S on (D.DY_DT = S.DY_DT)
AND (D.XTC_REG_NR = S.XTC_REG_NR)
AND (D.LOC_SYS_NR = S.LOC_SYS_NR)
AND (D.DPA_ID_NR = S.DPA_ID_NR)
AND (D.STP_ACT_SEQ_NR = S.STP_ACT_SEQ_NR)
WHERE E.PKG_XCP_RSN_CD = '39'
AND D.DY_DT =@deldate


GROUP BY E.PKG_TCK_NR,E.SHR_AC_NR,D.DY_DT,E.PKG_XCP_RPT_DT
,E.PKG_XCP_RPT_OGZ_NR
,E.LOC_SYS_NR,E.USR_NR
,E.UTC_TCF_MT_QY,E.PKG_XCP_RSN_CD
,E.SRT_TYP_CD,S.STP_CML_TM,S.DY_DT
,E.PKG_DTN_OGZ_NR,E.PKG_XCP_RPT_TM

order by e.pkg_tck_nr

Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave

 

[SQLBill]

How do you identify the 'last one'? Is it by date/time order? Or something else?

The MAX() function will probably be what you need.

-SQLBill

Posting advice: FAQ481-4875

 

[KOVMoe]

Bill,
To answer your question, time order, but it is in its own field. I have 15 fields that can be the same with the time being different. I tried max on E.PKG_TCK_NR and E.PKG_XCP_RPT_TM and still get the same 3200 rows.




Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave

 

[SQLBill]

I haven't added anything yet...just reformatted the code.

SELECT E.PKG_TCK_NR, 
       E.SHR_AC_NR,
       D.DY_DT,
       E.PKG_XCP_RPT_DT,
       E.PKG_XCP_RPT_TM,
       E.PKG_XCP_RPT_OGZ_NR,
       E.LOC_SYS_NR,
       E.PKG_TCK_NR,
       E.USR_NR,
       E.UTC_TCF_MT_QY,
       E.PKG_XCP_RSN_CD,
       E.SRT_TYP_CD,
       S.STP_CML_TM,
       S.DY_DT,
       E.PKG_DTN_OGZ_NR 
FROM DDAT0033.DBO.TECSDAT E 
  INNER JOIN DDAT0013.DBO.TDIRPKD_DELVPKG D 
    ON (E.PKG_TCK_NR = D.PKG_TCK_NR) 
  INNER JOIN DDAT0013.DBO.TDIRSTD_DELVSTOP S
    ON (D.DY_DT = S.DY_DT) 
    AND (D.XTC_REG_NR = S.XTC_REG_NR) 
    AND (D.LOC_SYS_NR = S.LOC_SYS_NR) 
    AND (D.DPA_ID_NR = S.DPA_ID_NR) 
    AND (D.STP_ACT_SEQ_NR = S.STP_ACT_SEQ_NR) 
WHERE E.PKG_XCP_RSN_CD = '39'
  AND D.DY_DT =@deldate
GROUP BY E.PKG_TCK_NR,
         E.SHR_AC_NR,
         D.DY_DT, 
         E.PKG_XCP_RPT_DT,
         E.PKG_XCP_RPT_OGZ_NR,
         E.LOC_SYS_NR,
         E.USR_NR,
         E.UTC_TCF_MT_QY,
         E.PKG_XCP_RSN_CD,
         E.SRT_TYP_CD,
         S.STP_CML_TM,
         S.DY_DT,
         E.PKG_DTN_OGZ_NR,
         E.PKG_XCP_RPT_TM
ORDER BY e.pkg_tck_nr

-SQLBill

Posting advice: FAQ481-4875