btamulis 1/23/2021 8:44:41 AM

SQL - Getting Minimum Date in a table

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. 

Version: All
Section: Dynamics GP, SQL Scripts


Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3