Hello,

I am trying to create a DECODE to dynamically control the order in which 12 months of a
year to display in a report. The date will be provided via USER input.

THe month will be extracted from the user input.

For example, the user entered 10/15/2019 as October will show up first in the results.
The DECODE will be like:

MORDER/12 = DECODE INONTH(10 '1' 11 '2' 12 '3' 
                           1 '4' 2  '5'  3 '6'  
                           4 '7' 5  '6'  7 '8' 
                           8 '9' 10 '8' 11 '9');

For another example, the user entered 06/10/2018 as June will show up first in the results.
The DECODE will be like:

MORDER/12 = DECODE INONTH(6  '1'  7  '2'  8 '3'
			  9  '4' 10  '5' 11 '6'
 			 12  '7'  1  '8'  2 '9'
                          3 '10'  4 '11'  5 '12');

I would appreciate any ideas anyone can provide.

Programming is a wonderful release, but some days, unlike Hedley Lamarr in "Blazing Saddles",
I do not have "the rivulets of thought, cascading into a waterfall of creative alternatives."

Thank you.

This message has been edited. Last edited by: FP Mod Chuck,

What's the format of the date field?

yymd

Not have been tested, but logically seems to work
Where MthFld is a TABLE field
Where UsrSelectedMth it's the extracted month from user selection

DEFINE FILE xyz
subMth /I2 = IF UsrSelectedMth EQ 1 THEN 0 ELSE UsrSelectedMth - 1;
addMth /I2 = 12 - subMth;
mthOrd /I2 = IF MthFld GE UsrSelectedMth THEN MthFld - subMth ELSE MthFld + addMth;
END

Is MthFld defined here?

mthOrd/I2 = IF MthFld GE usrSelectdMth THEN MthFld - subMth ELSE MthFld + addMth ; 


DEFINE FILE WF_RETAIL
THEDATE/YYMD WITH ID_SALES = '&YYMD'; 
UsrSelectedMth/I2 = DPART(THEDATE, 'MM', 'I11');
subMth/I2 = IF UsrSelectedMth EQ 1 THEN 0 ELSE UsrSelectedMth - 1 ;
addMth/I2 = 12 - subMth ;
mthOrd/I2 = IF MthFld GE usrSelectdMth THEN MthFld - subMth ELSE MthFld + addMth ; 
END

TABLE FILE WF_RETAIL
PRINT
UsrSelectedMth
subMth
addMth
-*mthOrd
BY THEDATE
IF RECORDLIMIT EQ 1
END 

-EXIT

One possible model:

DEFINE FILE GGSALES
 INDATE/YYMD  = '20090601';
 INMONTH/M    = INDATE;
 ININT/I2     = INMONTH;
-* 
 FILEMONTH/M  = DATE;
 FILEINT/I2   = FILEMONTH;
-* 
 SORTORDER/I2 = IF FILEINT EQ ININT THEN 1 
                 ELSE IF FILEINT GT ININT THEN FILEINT - ININT + 1
		          ELSE (12 - ININT + 1) + FILEINT;
END
TABLE FILE GGSALES
PRINT REGION  NOPRINT 
      INDATE  AS 'Incoming Date'
      INMONTH NOPRINT  
      ININT   NOPRINT    
-*
      DATE      AS 'File Date'
      FILEMONTH NOPRINT  
      FILEINT   NOPRINT  
-*	  
      SORTORDER AS 'Sort Order'
BY    SORTORDER NOPRINT
IF RECORDLIMIT EQ 30
END    

Incoming Date   File Date  Sort Order                                         
  -------------   ---------  ----------                                         
  2009/06/01     1997/06/01           1
  2009/06/01     1997/06/01           1
  2009/06/01     1997/07/01           2
  2009/06/01     1997/07/01           2
  2009/06/01     1996/07/01           2
  2009/06/01     1997/07/01           2
  2009/06/01     1997/08/01           3
  2009/06/01     1996/09/01           4
  2009/06/01     1997/09/01           4
  2009/06/01     1996/09/01           4
  2009/06/01     1997/09/01           4
  2009/06/01     1996/09/01           4
  2009/06/01     1997/09/01           4
  2009/06/01     1997/10/01           5
  2009/06/01     1997/10/01           5
  2009/06/01     1996/10/01           5
  2009/06/01     1996/10/01           5
  2009/06/01     1996/11/01           6
  2009/06/01     1996/11/01           6
  2009/06/01     1997/11/01           6
  2009/06/01     1996/12/01           7
  2009/06/01     1997/12/01           7
  2009/06/01     1996/12/01           7
  2009/06/01     1997/01/01           8
  2009/06/01     1997/03/01          10
  2009/06/01     1997/03/01          10
  2009/06/01     1997/04/01          11
  2009/06/01     1997/04/01          11
  2009/06/01     1997/05/01          12
  2009/06/01     1997/05/01          12
  

quote:

Originally posted by Tomsweb:
Is MthFld defined here?

As I mentioned : "Where MthFld is a TABLE field"
A field that is coming from you DB/Table; sure that may need to be a DEFINED one since normally we are keeping a date field and not its components individually. So yes you may need to extract the month component of your date field.

thanks everyone for your suggestions

I would go with Davids suggestion but would simplify(?) the defines -

DEFINE FILE GGSALES
 INDATE/I8YYMD  = 19960301;
 INMONTH/I2     = DTPART(DATECVT(INDATE, 'I8YYMD', 'YYMD'), MONTH);
-* 
 FILEMONTH/I2   = DTPART(DATECVT(DATE, 'I8YYMD', 'YYMD'), MONTH);
-* 
 SORTORDER/I2 = IF (13 - INMONTH + FILEMONTH) GT 12 THEN (13 - INMONTH + FILEMONTH - 12) 
                 ELSE (13 - INMONTH + FILEMONTH);
END

T

For David Briars's SORTORDER, perhaps even simpler:

SORTORDER/I2 = MOD(FILEINT - ININT + 12, 12) + 1 ;