Hi all,
I have to select the ordernumbers(aufnr)and date from JCDS table in a way that,
I have to check the status in jcds table with status
'delivered'(I0012) or 'Technically completed'(I0045) and if you have both status for a given order number then i have to check the dates for both status and select the one which is first.
for ex :
status 'I0012' date 27.07.2001
status 'I0045' date 12.09.2005
then i have to select only status 'I0012' because it is
first in date.
please help me.
and now I have following select statement which selects both dates.
SELECT aufk~aufnr aufk~auart aufk~werks aufk~prctr afko~plnbez afko~gamng afko~gasmg afko~aufpl afko~dispo jcds~udate afpo~posnr afpo~wemng afpo~amein afpo~matnr mara~matkl afvj~aplzl afvj~lmnga afvj~xmnga afvj~umrez afvj~umren FROM aufk JOIN afko ON afko~aufnr = aufk~aufnr JOIN jcds ON jcds~objnr = aufk~objnr AND jcds~chgnr = '001' JOIN afpo ON afpo~aufnr = aufk~aufnr JOIN mara ON mara~matnr = afpo~matnr JOIN afvv AS afvj ON afvj~aufpl = afko~aufpl INTO CORRESPONDING FIELDS OF TABLE gt_data * INTO TABLE GT_DATA WHERE aufk~aufnr IN s_aufnr AND aufk~auart IN s_auart AND aufk~werks IN s_werks AND aufk~prctr IN s_prctr AND afko~dispo IN s_dispo AND jcds~udate IN s_budat AND afpo~posnr = '0001' AND afpo~matnr IN s_matnr AND mara~matkl IN s_matkl AND ( afvj~lmnga > 0 OR afvj~xmnga > 0 ) AND afvj~aplzl IN ( select MIN( aplzl ) FROM afvv WHERE aufpl = afko~aufpl AND ( lmnga > 0 OR xmnga > 0 ) AND exists ( SELECT * FROM afvc WHERE aufpl = afvv~aufpl AND aplzl = afvv~aplzl AND steus <> 'YP12' ) ) AND ( jcds~stat = 'I0012' OR jcds~stat = 'I0045' ) AND exists ( SELECT * FROM jest WHERE objnr = aufk~objnr AND ( stat = 'I0012' OR stat = 'I0045' ) AND inact = space ).