0

Records with starting date/starting time and ending date/time in a date range

Frank Mielcke

June 05, 2019 17:58

Answered

Follow

Hello, I've been working on jet reports for a long time, but I can not get any further here

Task: 
I need the capacity ledger entries that are in a time interval with their start date and end date. The time interval consists of start date and start time as well as end date and end time.
All capacity ledger entries whose ending date and ending time are after the starting date and starting time of the filter and whose starting date and starting time begin before the ending date and ending time of the filter should be taken into account..
Notice:
It should be noted that the ending and starting dates of the capacity ledger entries represent only a time an not a date. So the posting date of the entries must be taken into account. 
In the sample data, records are filtered out which have no starting date and ending date.Only entries are relevant that have starting and ending dates.

Filter (example)
Starting date	09.04.19
Starting time	03:30
ending date	10.04.19
ending time	07:30

Period: 09.04.19 03:30..10.04.19 07:30 

The result should be the yellow marked area in the register "sample data"

Sample Data

 

Thank you very much in advance for your help

Frank

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Harry Lewis June 05, 2019 21:58

  Hello Frank.

  Since you specifically mention the Capacity Ledger Entry table, I assume you are using Dynamics NAV.

  As such, there are a couple of ways to accomplish this.

   

  You could use this filter:

  "Starting Time","6/5/19 07:00..","ending time","..6/5/19 10:01"

  (this example is retrieving all records between 7am and 10am today)

  Apparently NAV keeps track beyond the minute mark.  I had to add 1 minute to my ending time to retrieve my 10am records.

   

  Or like this:

  "Starting Time",">=6/5/19 07:00","ending time","<=6/5/19 10:00"

   

• 0
  
  Frank Mielcke June 06, 2019 18:43

  Hello Harry,

  thank you for your answer. I tried your syntax with these data:

  There is a position in timerange, but out of range of date: 11.04.19 4:00 - 5.00.

  

  

   

  The result with your syntax is:

  

  I also have to filter on the posting date to get a correct result: 090419..100419
  
  So why do you have to specify the date in an time filter? 
  
  Best regards
  Frank
  
  
  

   

   

• 0
  
  Harry Lewis June 06, 2019 21:12

  My apologies, Frank.  I misinterpreted the data in my sample database.

  The "Starting Time" and "Ending Time" fields in NAV's SQL database are of the SQL type "DateTime".  Thus, they contain both a date and a time in a single field.

  I created data in my database to look at your question.  It appeared that that NAV placed the date of the transaction in those fields.

  It does not.

  That date that NAV places in those fields is January 01, 1753.  

  Since those fields do not contain the date of the transaction, my earlier method will not work.

   

  Here is how I would address the problem....

  1) I would list out each day in my desired time period

  2) I would figure out if I need to filter out any records for that day (which I do... IF it is either the first or last day of the period... otherwise, I want all the records for that day).

  3) I would list out the records I want for each day.

   

  Here is how I addressed steps 1 and 2:

  

   

  I place my start and end dates in column C.  I place the Time I want to start on day 1 in column D and the Time I want to stop on the last day in column E

  Then, I placed an NL(Rows) to list my dates, compute if I want all records is a subset (again, for the first and last day, I want a subset... for all other days, I want all records).

  Now, I need to retrieve the actual records;

  

   

  For each day, this NL(Rows) function retrieves the records for that date - filtered by the calculated start and end time in columns C and D.

  Then, I can use NF functions to extract the specific fields I want to see.

  Here are the results of my report.

  Here I'm listing all records from 3am on April 21 until 12pm on April 23:

  

   

  If I adjust my filters, my results reflect that.

  Here I'm listing all records from 6am on April 21 until 6am on April 23:

  

   

  I hope that helps get you pointed in the right direction.  Thanks for introducing me to the Capacity Ledger Entry table. 

• 0
  
  Frank Mielcke June 09, 2019 10:54

  Hello Harry,
  

  I have recreated your syntax. But I also get records without start and end time. But you gave me the decisive food for thought. Thanks a lot for your help! I urgently needed this insight for a customer report. I was very unhappy to be unable to meet the customer requirement.
  

  I have developed the following solution:

  1. I have designed a filter matrix that maps all 3 possible combinations: 1 day, 2 days and more than 3 days

  

   

  2. Then I've created NL (Filter) functions that read out the primary key of the capacity ledger entries.
  I then condensed the primary key values ​​with union queries.
  

   

  

   

  3. Depending on the number of days, the union query is then used in the row replicator for listing or reading field values ​​of the capacity ledger entries.

  

   

  Best regards

  Frank