We use GP Sales Order Fulfilment.
The SOP10112 table is pretty simple - it stores a record for the progression of order fulfilment status.
SOPNUMBE SOPTYPE ORD Effective_Date TIME1 SOPSTATUS USERID DEX_ROW_ID
FULFIL100148 3 16384 2020-08-10 00:00:00.000 1900-01-01 17:56:16.000 2 CHALLENSTEIN 976
FULFIL100148 3 32768 2020-08-12 00:00:00.000 1900-01-01 15:27:22.000 3 CWALKINGTON 1127
FULFIL100148 3 49152 2020-08-12 00:00:00.000 1900-01-01 17:07:00.000 4 ABECERRA 1143
FULFIL100148 3 65536 2020-08-13 00:00:00.000 1900-01-01 13:55:47.000 6 CWALKINGTON 1231
FULFIL100148 3 81920 2020-08-29 00:00:00.000 1900-01-01 11:50:02.000 4 BTAMULIS 3501
FULFIL100148 3 98304 2020-09-02 00:00:00.000 1900-01-01 08:16:52.000 6 JANDERSON 3709
FULFIL100148 3 114688 2020-09-08 00:00:00.000 1900-01-01 16:59:31.000 7 ACARDONA 4069
I have a SQL query that I am struggling with to assure I return only one record with the earliest date for a particular status.
If you notice this record (FULFIL100148) has two records with Status = '6'.
I need to grab the earliest dated record.
My query doesn't return MIN as I expected........
(SELECT SOPNUMBE, MIN(CAST(Effective_Date as Date)) as PackedDate from FIRE..SOP10112
where SOPSTATUS ='6'
GROUP BY SOPNUMBE, Effective_Date
)
I thought the MIN would return earliest date but it doesn't......in other words it does nothing......
It returns:
SOPNUMBE PackedDate
FULFIL100148 2020-08-13
FULFIL100148 2020-09-02
Any guidance or suggestions would be appreciated.