Solved

Hourly occupation counts

Posted on 2013-12-13
18
227 Views
Last Modified: 2013-12-25
I have an interesting challenge where I have an Entry ticket (Entry Time stamp) for example 12/12/2013 08:05:00 am and an Exit transaction (Exit timestamp) 12/1/2013 5:05:00 pm - (Duration 9Hrs) and I am trying to figure out a way to count the hours that this ticket can be counted as 'Occupied'.

12/12/13
08:05      +1          +1           +1...............................................................................+1
-|----------|----------|----------|-----------|----------|----------|---------|----------|---------|---------|-
              09:00     10:00........................................................................................17:05      
                                                                                                                           12/13/13

So I will have 24 Hourly counters (datetimetoseconds) that run thru each transaction and check the entry time then each Hour and an add a count until it reaches the exit time.

My solution initially started with the approach to have Hourly schedules run and look at all tickets that are currently open, which I got to work perfectly but this leaves me with other challenges like for example I can only process data since the day I started the scheduling process.

Looking at the new approach  would want to look at all available data.

Any ideas please..
0
Comment
Question by:John-S Pretorius
  • 11
  • 6
18 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39717787
WHat do you want the report to show when you have the data?

Are you looking for a line like your example for each record or are you looking for some accumulation to show how many tickets were active each hour?

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 39717810
Typically in a day I will be processing approx. 2000 transactions, each one with his entry and transaction(exit time) but also there are about 10 different types of tickets (categories) so essentially in my original approach I was using a cross tab with the data sorted by Hour (Crosstab is also sorted by Hour)

I'm not concerned about the categories at this stage but more about t a slick solution to have an hourly group that will be able to count that transaction if it was still open during that time(Hour)

Below is a snapshot of my first initial approach (as explained in the posted question) which I'm hoping to change the hard way so I can access/report on all previous data:

Current approach
0
 

Author Comment

by:John-S Pretorius
ID: 39722428
My progress looked promising after I created a excel sheet with Hourly timeslots and making it part of the database field (nothing is linked) which means after creating a group for each hour pulled from the excel sheet I have all the data under each hour. I then created a formula to show a 1 if the transaction has entered before that hour and not processed during that hour - then just calculating the sum which gives me the total duration.

My challenge now seems to hide/suppress the transactions that is not suppose to be part of that hour.

Maybe with this additional information the is someone that can give some thought as I'm a little stuck again.

Below is a snapshot of the excel sheet I used to create the Hourly groups.

Excel sheet used for grouping Hours
0
 
LVL 34

Expert Comment

by:James0628
ID: 39723538
Do you just have one record for a transaction, with both the start and end datetime; or do you have two records for each transaction, one with the start datetime and one with the end datetime; or something else?

 James
0
 

Author Comment

by:John-S Pretorius
ID: 39723562
I have a Transaction time (Time) and a duration. I created a formula to show the Entrytime :
dateadd ('n',-{TransactionCombo;1.ParkingDuration},{TransactionCombo;1.Time})

Below is a image of the tables being used :

Tables
I then also have the Hourly Category's formulated with :

Cattegorys
0
 

Author Comment

by:John-S Pretorius
ID: 39723906
Just to recap and simplify, my first goal would be to calculate the occupation for all tickets (Typically 1500+/day transactions) - the short, if someone pays at 09:01 AM with a duration of 122 mins(entry time would be 06:59 AM), I want to reflect him as occupied for the Hour of 07:00 am and 08:00 am. (Off course there will be challenging aspects to keep in mind since the entry time can also be from the previous day etc..)

I have this currently working utilizing a very unorthodox method by running a scheduled Hourly report that grabs all 'Open tickets' and exporting them into a local SQL database where I can easily group and count totals - this causes all kinds of other issues like for example a growing database, only data from the day the schedule was initiated can be analyzed and of course should there be any network issue, pc down etc.. I won't be able to run the squedule. This is why I'm attempting this complex method.

When I get this to work properly I will tackle challenge 2 which includes grouping the 'Validated/Unvalidated' payments with validations and also durations : See below

if {TransactionCombo;1.RateNo}=5 then 'BHP' ELSE
if {TransactionCombo;1.Revenue}=35 then 'Red Sox' else
if {TransactionCombo;1.RateNo}=18 then '6am-8pm' else
if {TransactionCombo;1.RateNo}=16 then 'BalGame' else
if {TransactionCombo;1.RateNo}=6 then {TransactionCombo;1.ValProvider} & ': 1Hr-free $'
else if (                                         //(< 10 min)Unvalidated
          {TransactionCombo;1.RateNo}=1 and
           (
            {TransactionCombo;1.ParkingDuration}< 10
            )
          ) then '(< 10 mins)Unvalidated'


else if (                                         //(10min-1Hr)Unvalidated
          {TransactionCombo;1.RateNo}=1 and
           (
            {TransactionCombo;1.ParkingDuration}>=10 and
            {TransactionCombo;1.ParkingDuration}<=60
            )
          ) then '(10mins-1Hr.)Unvalidated'


 else if (                                        //(1Hr-1.5hr)Unvalidated
          {TransactionCombo;1.RateNo}=1 and
           (
            {TransactionCombo;1.ParkingDuration}>60 and
            {TransactionCombo;1.ParkingDuration}<=90
            )
          ) then '(1Hr-1.5Hrs.)Unvalidated'

 else if (                                         //(1.5Hrs-2Hrs.)Unvalidated
          {TransactionCombo;1.RateNo}=1 and
           (
            {TransactionCombo;1.ParkingDuration}>90 and
            {TransactionCombo;1.ParkingDuration}<=120
            )
          ) then '(1.5Hrs-2Hrs.)Unvalidated'
 else if (                                         //(2Hrs-2.5hr)Unvalidated
          {TransactionCombo;1.RateNo}=1 and
           (
            {TransactionCombo;1.ParkingDuration}>120 and
            {TransactionCombo;1.ParkingDuration}<=150
            )
          ) then '(2Hrs-2.5Hrs.)Unvalidated'
else if (                                         //(2.5Hrs-18hr)Unvalidated
          {TransactionCombo;1.RateNo}=1 and
           (
            {TransactionCombo;1.ParkingDuration}>150 and
            {TransactionCombo;1.ParkingDuration}<=1080
            )
          ) then '(2.5Hrs-18Hrs.)Unvalidated'
else if (                                         //(18Hrs +)Unvalidated
          {TransactionCombo;1.RateNo}=1 and {TransactionCombo;1.ParkingDuration}>1080
         ) then '(18Hrs+)Unvalidated'
//else if {TransactionCombo;1.Revenue}=35 then 'Red Sox'
else {TransactionCombo;1.ValProvider}
0
 
LVL 34

Expert Comment

by:James0628
ID: 39726280
So, the "Transaction time" is the end time?

 Maybe it's just me, but I'm still not sure what you're trying to get.

 You talk about doing something for each hour (eg. "I want to reflect him as occupied for the Hour of 07:00 am and 08:00 am").  If you're trying to group the records by the hour, like if you want to get a total or count for each hour, then a basic problem is that each record can only be in one group.  For example, if a transaction went from 7 to 8:59 AM, it could be in the 7 AM group or the 8 AM group, but the single record for that transaction can't be in both groups.

 If that's an issue, then one possible way to get around it is to basically duplicate your data, adding an extra copy of each record, for each additional hour of that transaction.  I think that may be what you effectively did when you added the Excel file with the time periods.  The problem with duplicating the records for each hour is that the report ends up reading a lot more data.  Whether or not that's a problem for you will depend on your data, etc.

 James
0
 

Accepted Solution

by:
John-S Pretorius earned 0 total points
ID: 39726301
Thank you James, by grouping by the Hourly formula set by the excel sheet, and selecting the previous day transactions(exit times) I'm able to show all the transactions under each hour group. I then conditionally suppress the data where the transaction Time (Hour) is less than than the Excel (hour) which now shows the data that belongs in the hourly group. I then create a formula based on the entry time and the transaction time to reflect a 1(occupied) and a 0 if it either did not enter yet or not occupied. I then sum the 1's and get the global total, my next step is grouping the validations.

So it's working but not very efficiently it takes forever to process and need cleaning up, I will attach a copy soon.
0
 

Author Comment

by:John-S Pretorius
ID: 39727530
I was able to get the report functional and now just need some assistance 'bugging' a few of the discrepancies when compared to my previous 'Hourly open snapshot count' approach.

The first change I made which improved the processing speed was to remove the excel sheet and actually create a SQL table, thanks to the SQL experts (see question ID: 39726686). Below is a snapshot which shows a slight difference in counts when compared next to each other :

Comparison (Old format to new format)
So what I'm doing after all the data is duplicated automatically under each grouped (SQL Hourly table) is to remove the transactions on each GroupedHour that has already been completed by conditionally suppressing :
datetimetoseconds({CompleteCombo.TransTime})<datetimetoseconds({@Conv_DateTimeString})

I then add my scripted 'Validation/type' group and another formula that checks if the transaction is before the Hourly check and also actually entered by that hour :
if Hour({CompleteCombo.TransTime})= Hour({@ConvDate}) then 1 else
(
  if Hour({CompleteCombo.TransTime}) > Hour ({@ConvDate}) and
  {CompleteCombo.EntryTime}< {@ConvDate} then 1 else 0
)

I now sum all the '1' which gives me a count of the occupation for each hour by validation.

I'm guessing that I'm maybe missing another check that could be causing the discrepancy.

I also included the finished report with saved data.
Test1-12182013.rpt
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 34

Expert Comment

by:James0628
ID: 39727782
In your "snapshot" pic, you highlighted 12 AM and 11 PM in yellow, which I thought meant that only those two hours had different counts, but most of the hourly grand totals are different.  I'm guessing that you just picked those 2 hours as examples?  Or maybe I'm misunderstanding what that pic is supposed to be showing us.

 Anyway, you mentioned conditional suppression.  That may be the problem.  Suppression (conditional or otherwise) simply hides things.  For example, if the detail section is suppressed, any formulas in that section will still be evaluated.  And if you do a summary on a formula, like the summary on @HourTest in your cross-tab, that summary is completely unaffected by any fields or sections that might be suppressed in the report.  To put it another way, you could suppress every section in your report except the Report Header, and the figures in your cross-tab wouldn't change.

 If (and I'm just guessing here) you don't want your total for a formula to include the records that are in the detail sections that are suppressed, you should include your suppression test in the formula.  But reverse the test.  For example, if you're suppressing the records where A > B, then you'd want to have the formula check for the opposite condition: A <= B.  Or add "not" to the suppression condition, like: not (A > B)

 James
0
 

Author Comment

by:John-S Pretorius
ID: 39727810
Thank you James,
The conditional suppress is just to hide the transactions per Hourly group and to make the RAW data more understandable. I'm going to start looking where the first discrepancy starts :- (all is exactly the same until 7am) to try and see what may be different.

I will also try your suggested formula.
0
 

Author Comment

by:John-S Pretorius
ID: 39728248
I'm really having a strange 'tugg' with any data showing up after changing my 'Record selection' from -3 to -1 (previous day)

{CompleteCombo.TransTime}=currentdate-3

As soon as I bring it back to -3 the data is back, is there a need for me to reset the Number variable I'm using to combine my Hourly group :

//Combining Date and time from Imported Excel hourly table into SQL : NOTE -3 FOR 3 DAYS BACK
numbervar y := tonumber(mid({HourlyTables.ProccessDate},7,4));
numbervar m := tonumber(mid({HourlyTables.ProccessDate},1,2));
numbervar d := tonumber(mid({HourlyTables.ProccessDate},4,2));
numbervar h := tonumber(mid({HourlyTables.Expr1},1,2));
numbervar n := tonumber(mid({HourlyTables.Expr1},4,2));
numbervar s := tonumber(mid({HourlyTables.Expr1},7,2));
datetime(y,m,d,h,n,s)-3

I'm not sure what is causing this issue now.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39728306
I don't understand why you're subtracting 3 days in @Conv_DateTimeString.  Why change the date?

 When you change the record selection formula, do you not get any data?  "strange 'tugg'" doesn't really mean anything to me, but "the data is back" implies that you're not seeing any data when you change the record selection, which would imply that you simply don't have any data for the new date (the previous day).

 OTOH, if you have data on the report, but it seems to be for the wrong date, I would guess that that's because of the -3 at the end of @Conv_DateTimeString.  You're selecting the previous day (-1) in the record selection, but still subtracting 3 days in @Conv_DateTimeString, so you're getting the data for yesterday, but the displayed date is 2 days earlier.  If that's the problem, then I guess you need to change @Conv_DateTimeString to subtract 1 day (or, more generally, to subtract the same number of days as the record selection formula).

 James
0
 

Author Comment

by:John-S Pretorius
ID: 39728322
I found the issue to be with the actual group settings which was set to sort by specified order(datetime) and discard others, as soon as I changed it to the normal sorting order I was able to get -1 (previous day) The reason I had -3 is because my database was loaded on Monday and that is the only way I could get to reflect any data. I will have access to the production server/data shortly.

James do you think my approach may be over shooting what I'm trying to accomplish - can you maybe suggest another way to show 'occupation' based on the data? If you do I would love to hear about it.

I'm pretty sure I will find the issue to my discrepancies tomorrow

Thank you for your interest.

I will try to be clearer met time I describe my issues.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39728798
Ah.  I hadn't looked at the group options and didn't realize that you were using Specified Order.

 Why are you using Specified Order?  Your order just seems to be each hour.  If so, couldn't you forget the Specified Order and just make the group "for each hour", instead of "for each day"?  Then you wouldn't need to try to adjust the dates to fit into the slots defined in the Specified Order.  You used "for each hour" for the columns in the cross-tab, but not for the group in the report.

 James
0
 

Author Comment

by:John-S Pretorius
ID: 39730347
It turns out that the 'comparison' I was using as displayed in the one of the comments above had a scheduling issues on that day which contributes to miscalculations and totals. This is exactly why I pursued another solution to calculate hourly occupation. Time will tell how accurate my approach is after true ticket counts can be compared to a manual occupation count.

At this time I feel confident that my approach is giving true values of occupation by the hour for the processed transactions.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39731602
Glad to hear it.  If/When you're satisfied that it's working, you can accept any posts, including your own, that you think contributed to the solution.  At this point I really have no idea if my posts were any help.  I feel like I was mostly asking questions and making guesses.  :-)

 James
0
 

Author Closing Comment

by:John-S Pretorius
ID: 39738954
Thank you all for the input, it seems by pursuing the obvious I was able to get thru the formulation and a functional approach.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now