Calculate weeks & weekend days?

Hi,

I am trying to write a query in Query/400 on the F4211 file where SDTRDJ is less than today's date + 3 days (NOT counting the weekends). Is there a way to so this in a query? Please let me know. Thanks.

Ahsan

I don't think you will achieve that in a Query.

Marsh

Ahsan,
Reading your message I think what you will need is a workday calendar
table. For a while our fiscal year did not match the calendar year and we
had holidays as well to consider. The only solution I could come up with at
the time was to build a calendar table. One of the columns in my calendar
table was a running workday total. I also had the normal Julian in a column
and the JDE Julian in a column. You could add a column to get the desired
return date based upon the JDE Julian you mention below. The only problem
we had was with actual workdays since we occasionally worked Saturdays.
This was especially hard since sometimes they wanted the reporting by
department. The only way we could do that was through code. I personally
found it easiest to create the calendar in Excel, and then import it to the
AS/400 and set the key over the JDE Julian column.
Good Luck
Hal


Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC


| | Ahsan |
| | <[email protected]|
| | om> |
| | Sent by: |
| | jdeworld-bounces@|
| | jdelist.com |
| | |
| | |
| | 10/18/04 02:28 PM|
| | Please respond to|
| | PeopleSoft® World|
| | |
>

Don’t know the syntax in query/400 but can be done in SQL.


SELECT * FROM F4211

WHERE STDRDJ > CURRENT_DATE + 3 DAYS

AND DAYOFWEEK(STDRDJ) > 1

I use a tool that converts Query/400 into SQL...whole libraries...I think it was real cheap.

Xe - AS/400 - Fat clients/Citrix

How do I get the CURRENT_DATE + 3 DAYS in SQL?
Thanks.

Ahsan

Thanks for the reference Sam!

Ahsan

Using query you can define the following result fields:
-----------------------------------------------------------------------------------

Define Result Fields

Type definitions using field names or constants and operators, press Enter.
Operators: +, -, *, /, SUBSTR, ||, DATE...

Field Expression Column Heading Len Dec
SDDATE date(
'20'||substr(digits(SDTRDJ),2,5))


TODAY CURRENT(DATE)


DIFFERENCE DAYS(SDDATE) - DAYS(TODAY)

-----------------------------------------------------------------------------------

You will then be able to make the following selections :

-----------------------------------------------------------------------------------

Select Records

Type comparisons, press Enter. Specify OR to start each new group.
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...

AND/OR Field Test Value (Field, Number, 'Characters', or ...)
SDTRDJ NE 0
AND DIFFERENCE LE 3
AND DIFFERENCE GE 0
-----------------------------------------------------------------------------------

This does not help with the exclusion of weekends but does enable you select SDTRDJ where it is less that today + 3 days.

Hope this helps.

Regards

Gavin

Thank you very much, Gavin for your tip. I am sure it will come in handy.
I am not sure what Hal meant by creating a seperate table to keep the running total of days. I wouldn't mind examples.

Ahsan, you might also want to search this forum for previous posts re: F00365. It's a table you can populate via P00365, that contains the Julian and Gregorian dates, month, day, year, and column for the date in text (e.g., October 21, 2004). You could add columns to this table for day of week and workday Y/N,as it does not appear to be referenced by any World programs, and then join to this table w/ Julian date in your SQL or Query, but I don't know how you could achieve the logic you want; in my mind, it would take a quick RPG program. This would be a shortcut to Hal's suggested solution.

I wrote an RPG program that determines day of week from a date, which I would be glad to share if you think this would be helpful. Let me know and I will send it to you. The logic I used was to have a program-defined array with the day of the week for January 1 of each year. I wrote the program in 1989, and it only goes through 2005, so would need a minor update to bring it up to date for future years.

Also, how do I add 5 days to a SDTRDJ field in Query/400? Because if I have a SDTRDJ of 104365 and I want to add 5 to it, it would be 104370. That is not valid. Is there a way it would convert it to 105005?

Use subprogram X0027 to increment dates. You can both add and subtract days from a date. Programs P08QEW, P1304 and P4242 all have examples of using this subprogram.

I don't think Query/400 has any date functions to achieve your result. I suggest you to use World Writer tool in JDE as it has lot more options.

In world writer define a new field called SDTEMP and take option '1' to open the query result field definition window and enter the following expression: sdtrdj + 5 and execute your query. HTH!

Sreeni Manda
SYSTIME

Sally,

We were thinking of modding the last 4 positions of the ONDL02 field in the F00365 file to insert the week. Not change the database structure, but just change the value. The change would be like YYWW. We get a ton of requests for reports where we need to group the data by week. We could use query in a lot of these if we can make the chance. Currently the record looks like:
"January 1, 1980________________"
and the change would be like:
"January 1, 1980____________8001"
(I had to put the '_____' to show the blank spaces otherwise the spaces get supressed on this site).

Do you have any suggestion or have any better way to do this?

Thanks.

Ahsan,

I have to confess I have never used the F00365 table myself(!) - I supplied that suggestion from remembering several other posts in the past. My tendency would be to recommend that you add another column to F00365 for YYWW, because you would always need to substring DL02 to select just the last 4 positions in order to get the date expressed as YYWW, and then you end up with an alpha value instead of numeric. My understanding is that P00365 is the only JDE program which would need to be changed for a new column. Another consideration is how many Queries or WorldWriters or custom reports do you have which use this file? They would all need to be updated or recompiled in order to recognize the new field in the file vs. no need to worry if you use DL02. If you decide to add the new field, you may also want to add a corresponding description field that contains the date in words for headings such as, "For the week ending Month DD, YEAR" or week beginning, whichever way your company prefers.