Sales by hour

[BryanLane]

I am trying to create a report that shows the number of sales each hour for each location. Unfortunately the system does not record the time of the sale, only the time that lines are added to a pick ticket....this will be "close enough" for our report.

There are two related tables: OrdHdr and OrdLin that store all the data for the Pick Tickets. The time is stored in the OrdLin table. So I need to determine the time that the last line was added to a ticket. That is easy enough if I group by order and then take the Maximum time value.

The time is stored as a number that represents HHMMSS so the values can potentially run from 000000 to 235959, with most of the values being between 073000 and 170000, our store hours. I have created a true time value from this number by converting it to a string, parsing it into its components and then using TIME(hh,mm,ss) to create a time value.

But now I am left with the time in the footer of each OrderNo group.

How do I go about grouping them by hour? The @OrderTime field in my Footer1 is not available in either the Group or Group Sort Experts.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

You should be able to insert a maximum on your detail level time formula at the group level. This would then be available for a group sort (inserting a summary activates the group sort feature). However, this will just order the order numbers by time, not group them.

To group, first create a SQL expression:

(
select max(`timenumber`)
from OrdLin A
where A.`OrderNo` = OrdLin.`OrderNo`
)

Note that the punctuation depends upon your datasource. You should also always identify your CR version as it can affect solutions. Syntax in SQL expressions varies by version, for example.

Then create a formula to convert the SQL expression to a time, using your ealier method. You should then be able to group on this formula on change of hour.

-LB

 

[BryanLane]

Thank you for the response. I am dealing with a day of disasters today...so I will have to hold off until tomorrow to resome my reporting effort.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein