Tech Tip: Working with Max/Min Date Fields

Maximums and minimums can give you great insight into what is going on within the data you’re working with, or within operations. If they appear outside of the norm, further research should be done on those transactions. This Tech Tip provides step-by-step instructions to determine maximum or minimum dates or times, and how to find the record for maximum date AND time.

How Do I Determine Maximum or Minimum Date or Time?

1. APPEND FIELD: DTODAYS_DATE
   1. Type: Numeric
   2. Size: 0 decimal places
   3. Parameter: @DtoDays(DATE)
   4. Description: Number of days since 1-Jan-1900
2. ANALYSIS – SUMMARIZATION
   1. Fields to summarize: Your Choice! MONTH for sample
   2. Numeric fields to total: DTODAYS_DATE
   3. Statistics to include: Maximum & Minimum
3. APPEND FIELD: MIN_DATE
   1. Type: DATE
   2. Parameter: @DaysToD(DTODAYS_DATE_MIN)
   3. Description: Minimum date
4. APPEND FIELD: MAX_DATE
   1. Type: DATE
   2. Parameter: @DaysToD(DTODAYS_DATE_MAX)
   3. Description: Maximum date

Functions for time are:

@TtoN – Converts a Time field that has values stored in Time format (HH:MM:SS) to a Numeric field with the time value converted into seconds.

@Ntot –  Converts a Numeric field containing time stored as seconds to a Time field type with a Time format (HH:MM:SS).

WAIT A MINUTE! I need to find the record for maximum date AND time.

1. APPEND FIELD: DATE_TIME
   1. Type: Numeric
   2. Size: 5 decimal places
   3. Parameter: @Val(@Dtoc(DATE,“YYYYMMDD”) + “.” + @Replace(@Str(@Tton( TIME ),5,0),” “,“0”))
2. ANALYSIS – SUMMARIZATION
   1. Fields to summarize: Your Choice! MONTH for sample
   2. Numeric fields to total: DATE_TIME
   3. Statistics to include: Maximum
3. ANALYSIS – JOIN
   1. Primary database: SUMMARIZATION
   2. Secondary database: DATE_TIME DATA
   3. Match Key Fields:
      1. MONTH <-> MONTH
      2. DATE_TIME_MAX <-> DATE_TIME
   4. Matches Only