How to group 24-hour data in 30 minutes interval to get the count?

Question

I have a field name timestamp in the sales table in which the data format is: 20210725.1800 which means 2021-year 07-month 25th-date 00:30:00 AM.

Now, if I want to count the sales on between 30 minutes intervals from 20210725.0000 to 20210725.1800, I can do that by simply writing:

def var k as int no-undo.

for each sales no-lock 
   where salesdate =  07/25/2021 
   and   timestamp >= 20210725.0000
   and   timestamp <= 20210725.1800
:

   if available sales then do:

      k = k + 1.
      pause 0.

      display k with frame f.

   end.

end.

But, I don't want to run the same query 24 times by changing the start and end time of the timestamp field.

So, I am looking for a smarter way to find out the whole day sales count grouped by 30 minutes intervals on this timestamp field.

Your data format is confusing me. If 0.1800 = 00:30, then 0.9000 = 02:30 - so how do you record 03:00? Or should you example be 0.01800 = 00:30 ? — Stefan Drissen, Aug 06 '21 at 14:46

score 2 · Answer 1 · answered Jul 25 '21 at 19:23

2

You can do something along the lines of the below. You could also sort the query by salesdate if you wanted more than one date too, though you'd have to clear the integer array on each new date (see the byandbreak by` doc on how to do that).

The calculation of the ts variable will depend on how the decimal value was constructed: are .1800 and .18002 in the same 30 minute slot?

def var numSales as integer extent 24 no-undo.
def var k as integer no-undo.
def var ts as integer.

for each sales 
 no-lock 
   where salesdate eq 07/25/2021:

    // get the timestamp for the day
    // this could use a calculation like the below,
    // or a simple CASE statement
    ts = 10000 * (timestamp - decimal(string(year(salesdate)) + string(month(salesdate)) + string(day(salesdate)))).

    // find the 30-minute slot. +1 makes it a 'ceiling'
    numSales[integer(ts / (30 * 60)) + 1] += 1.
end.

do k = 1 to 24 with frame f:
    displ
       k (k * 30 * 60) numsales[k].
end.

answered Jul 25 '21 at 19:23

nwahmaet

3,589
3
28
35

thanks for your reply @nwahmaet. I have used INT64 variable as data type on your provided above code as only INT was giving error but now getting this error message `** Array subscript -6509 is out of range. (26) ** Array subscript -6508 is out of range. (26) Press space bar to continue.` Any idea, why it's happening? – Rafailo Jul 25 '21 at 20:18
1

Using the fresh off the press += may not be enlightening… – Stefan Drissen Jul 25 '21 at 22:26
As to “any idea what’s happening”, put the extent in an intermediate variable and figure out why it is being assigned an unexpected value yourself. – Stefan Drissen Jul 25 '21 at 22:30
Simpler date to timestamp (since the above looks incorrect): integer( replace( iso-date( salesdate ), ‘-‘, ‘’ ) ) – Stefan Drissen Jul 26 '21 at 05:17

score 1 · Answer 2 · answered Jul 26 '21 at 13:18

There really isn't anything wrong with running the same query 24 times (or whatever) so long as each sub query is as efficient as one big query.

It is especially not wrong to do so if it makes your code clearer to the maintenance programmer that comes along 3 years from now trying to understand what you did.

The following example just uses a DATE field since the ubiquitous "sports" database does not have any fields with the style of date.time that your example has, but it should be simple to extrapolate from:

define variable n as integer no-undo.
define variable d as date    no-undo.

define variable b as handle no-undo.
define variable q as handle no-undo.

create buffer b for table "order".
create query q.
q:set-buffers( b ).

do d = 1/1/1998 to 1/31/1998:

  n = 0.
  q:query-prepare( substitute( 'preselect each order no-lock where orderDate = &1', d )).
  q:query-open no-error.
  if q:query-off-end = no then
    n = q:num-results no-error.
  display d n with frame a down.
  down with frame a.
  q:query-close no-error.

end.

score 1 · Answer 3 · answered Aug 06 '21 at 14:51

If I step over my question about how your timestamp is really constructed, you can use break by to get your results:

def temp-table ttsales no-undo
   field salesdate as date
   field timestamp as decimal
   .

function createSale returns logical (
   i_detimestamp as decimal
):

   def buffer busale for ttsales.

   def var idate  as int.
   def var iyear  as int.
   def var imonth as int.
   def var iday   as int.

   assign
      idate  = truncate( i_detimestamp, 0 )
      iyear  = truncate( idate / 10000, 0 )
      idate  = idate - iyear * 10000
      imonth = truncate( idate / 100, 0 )
      iday   = idate - imonth * 100      
      .

   create busale.
   assign
      busale.salesdate = date( imonth, iday, iyear )
      busale.timestamp = i_detimestamp
      .

end function.

createSale( 20210725.0000 ).
createSale( 20210725.0001 ).
createSale( 20210725.1799 ).
createSale( 20210725.1800 ).
createSale( 20210725.1801 ).

def buffer busale for ttsales.

def var irecords as int.
def var idate    as int.

for each busale
   where busale.salesdate = 07/25/2021
break 
   by truncate( busale.timestamp * 10000 / 1800, 0 )
:

   irecords = irecords + 1.

   if last-of( truncate( busale.timestamp * 10000 / 1800, 0 ) ) then do:
      display 
         int( truncate( ( ( busale.timestamp * 10000 ) modulo 10000 ) / 1800, 0 ) )
         irecords
         .
      irecords = 0.
   end.

end.

Try it out in ABLdojo.

How to group 24-hour data in 30 minutes interval to get the count?

3 Answers3