Query/400: Subtract one date from another

Tags:
AS/400
Query/400
I only want my calculate to use number of business days.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Thanks for coming back. Can your IT department either write the report for you. Querry400 will not allow you to use the calendar file on a join with a date range.

Discuss This Question: 16  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • philpl1jb
    Could you tell us what software or programming language you are using?
    54,090 pointsBadges:
    report
  • CharlieBrowne
    Do also need to exclude Holidays?
    62,385 pointsBadges:
    report
  • queryhelpserena
    Yes.
    105 pointsBadges:
    report
  • queryhelpserena
    Query/400.
    105 pointsBadges:
    report
  • CharlieBrowne

    This is difficult to do with QUERRY400.

    You could get the number of days between the date range and then div by 7 to get the number of weeks. Multiple that by 2 and get the number of weekend days. Subtract that from the total days.

    But that does not account for when there is a reminder with the divide instruction and you do not know if the day of the week of the first date in the range.

    Also you have nothing to compare with to find out if there are any holidays within the range (and the day of the week the holidays fall on).

    Can you use any other language?

    Can you give us the business application for this task?

    62,385 pointsBadges:
    report
  • queryhelpserena
    No. I only know how to use query. I'm making a report of late orders and want to show the number of days late. Weekends and holidays should be omitted.
    105 pointsBadges:
    report
  • CharlieBrowne

    Have you approached anyone in your IT department?

    Do you have experience with EXCEL?

    62,385 pointsBadges:
    report
  • CharlieBrowne

    Check with you IT department to see if they can assist you.

    Else do a data extract with excel and then you can use a function to get the number you want,

    62,385 pointsBadges:
    report
  • queryhelpserena
    Thank you!
    105 pointsBadges:
    report
  • CharlieBrowne

    You are very welcome.

    If/when you get the results you are looking for, please come back and update us with hoe it was done.

    62,385 pointsBadges:
    report
  • queryhelpserena
    Okay thanks again.
    105 pointsBadges:
    report
  • queryhelpserena
    I'm back. I have a calendar file on our system that has only business day dates in it. Now I have something to compare but still don't know how to do this.
    105 pointsBadges:
    report
  • philpl1jb

    Does the calendar file have a running day of the year column.

    It's not very likely but I can't begin to figure how to do this in query without it.

    20160104   1

    20160105    2

    20160106    3

    20160107    4

    20160108    5

    Sat

    Sun

    20160109    6

    54,090 pointsBadges:
    report
  • queryhelpserena
    I added that column to the file.
    105 pointsBadges:
    report
  • queryhelpserena
    SUCCESS - After creating the new file and adding the running day of the year column. I was then able to chain the Due Date field in my first file to the date in the new file. I also have a file in my query that has only one field with today's date. I then added the new file again to the query chaining today's date to the date in the new file. Then I had two running day of the year columns and put in the calc to subtract one from the other. AWESOME!!!!!!

    THANK YOU!!!!!
    105 pointsBadges:
    report
  • philpl1jb
    Great, happy you got the results you needed.
    54,090 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: