Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

Hours count if over x minutes of hour

I need to insert a record if the user was logged in for more than 30 mins of the hour for each hour of the day. I'm looking for the SQL of how to make this happen.

Here's the data which has the login & logout fields that I need to parse and append into a new table.
User_ID	LoginDt	LogoutDt
user1	2/3/2014 9:02:03 AM	2/3/2014 10:54:36 AM
user1	2/3/2014 10:56:35 AM	2/3/2014 11:39:37 AM
user1	2/3/2014 11:46:16 AM	2/3/2014 2:08:35 PM
user1	2/3/2014 2:09:38 PM	2/3/2014 3:07:33 PM
superuser22	2/3/2014 12:59:48 PM	2/3/2014 4:14:58 PM
superuser22	2/3/2014 4:17:37 PM	2/3/2014 5:47:39 PM
superuser22	2/3/2014 5:50:48 PM	2/3/2014 7:39:39 PM
superuser22	2/3/2014 7:43:32 PM	2/3/2014 8:10:59 PM
userX	2/3/2014 10:24:29 AM	2/3/2014 10:24:40 AM
userX	2/3/2014 10:55:32 AM	2/3/2014 10:55:44 AM

Open in new window


Here's the target table I'm trying to populate which should look something like the below:

UserID	Date	Hour	LoginCount
user1	2/3/14	9	1
user1	2/3/14	10	1
user1	2/3/14	11	1
user1	2/3/14	12	1
user1	2/3/14	13	1
user1	2/3/14	14	1
superuser22	2/3/14	13	1
superuser22	2/3/14	14	1
superuser22	2/3/14	15	1
superuser22	2/3/14	16	1
superuser22	2/3/14	17	1
superuser22	2/3/14	18	1
superuser22	2/3/14	19	1

Open in new window

0
Dalexan
Asked:
Dalexan
  • 7
  • 4
  • 3
  • +1
1 Solution
 
HooKooDooKuCommented:
Looks like you're going to need some sort of processing loop to process each line of the input:

Pseudo code might look something like this:
While Input to read
  Get User, inTime, outTime
  if( outTime - inTime > 30 minutes )
    if( HourOf(inTime) = HourOf(outTime) )
      insert User, DateOf(inTime), HourOf(inTime), 1
    else
      if( MinuteOf(inTime) <= 30 )
        insert User, DateOf(inTime), HourOf(inTime), 1
      for( Each Hour between (HourOf(inTime), HourOf(outTime) )
        insert User, DateOf(inTime), Hour, 1
      if( MinuteOf(outTime) >= 30 )
        insert User, DateOf(inTime), HourOf(outTime), 1
Next Line

The situation would need to be further expanded if you have to handle the possibility that dates change while they are logged in.
0
 
DalexanAuthor Commented:
Yes, dates change where the user is logged in through midnight...
0
 
HooKooDooKuCommented:
Then the pseudo code only needs the following changes:

While Input to read
  Get User, inTime, outTime
  if( outTime - inTime > 30 minutes )
    if( HourOf(inTime) = HourOf(outTime) )
      insert User, DateOf(inTime), HourOf(inTime), 1
    else
      if( MinuteOf(inTime) <= 30 )
        insert User, DateOf(inTime), HourOf(inTime), 1
      for( Each Hour between (HourOf(inTime), HourOf(outTime) )
        insert User, DateOf(Hour), Hour, 1
      if( MinuteOf(outTime) >= 30 )
        insert User, DateOf(outTime), HourOf(outTime), 1
Next Line
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
DalexanAuthor Commented:
Thanks, I meant to ask in my question if you can you show the sql for this. I don't think there's a for loop structure available in mysql. I want to put this into a stored proc that runs on the server.
0
 
HooKooDooKuCommented:
As you can see, I can figure out the logic... but I'm no SQL expert.  I know how to use SQL to query and modify a database.  But I don't know how to implement this logic in just SQL... given that SQL is a way to interact with a database and NOT a programming language, I don't think it is possible to implement this sort of logic into something as limiting as SQL... but then again, I'm not enough of a SQL expert to say it's impossible.
0
 
DalexanAuthor Commented:
Thanks HooKooDooKu but I'm really looking for some help with the SQL of how to make this happen. SQL has some programmatic structures: Mysql flow control statements and I really want this to run as a stored proc on the server.
0
 
Ray PaseurCommented:
You might consider using PHP to do part of the work.  It's more suited to DATETIME manipulation that MySQL (See AntiPractice #12).  The knowledge base for handling DATETIME in PHP and MySQL is available in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

If you're interested in a PHP solution, please post back and I'll try to show you how the code might work.
0
 
DalexanAuthor Commented:
I looked through the link, please show the php that might work to append the calculated result to the target table.
0
 
Ray PaseurCommented:
I can spend a little time on this today, and I'll try to show you the logic for handling the DATETIME values.  Before you go too far down this road, consider your data source and quality carefully.  It's nearly impossible to know how long a user is "logged in" if you're dealing with a stateless client/server protocol.  Some of the reasons for that are described here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/A_11271-Understanding-Client-Server-Protocols-and-Web-Applications.html

Another reason, that I should probably add to the article is that once the response is complete, the server cannot know what the (human) client is doing.  Am I reading the computer screen or walking the dog?  No way to tell.
0
 
DalexanAuthor Commented:
The data source is from a client/server app which is inserting to the DB upon button click of login or logout. This data is consistent and used for other reports. This is part of a commissions based CRM system and the report will be used to value the performance of the user based on logged in time per hour, its to the users benefit to be at the computer screen. Thanks for being thorough and detail oriented in your responses.
0
 
Ray PaseurCommented:
Please see http://www.laprbass.com/RAY_temp_dalexan.php

<?php // RAY_temp_dalexan.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Database/MySQL/Q_28356371.html

// THE TEST DATA SET (SLIGHTLY MODIFIED - SEE user2)
$str = <<<EOD
User_ID    LoginDt    LogoutDt
user1    2/3/2014 9:02:03 AM    2/3/2014 10:54:36 AM
user1    2/3/2014 10:56:35 AM    2/3/2014 11:39:37 AM
user1    2/3/2014 11:46:16 AM    2/3/2014 2:08:35 PM
user1    2/3/2014 2:09:38 PM    2/3/2014 3:07:33 PM
user2    2/3/2014 4:04 PM    2/3/2014 4:30 PM
user2    2/3/2014 4:35 PM    2/3/2014 4:45 PM
superuser22    2/3/2014 12:59:48 PM    2/3/2014 4:14:58 PM
superuser22    2/3/2014 4:17:37 PM    2/3/2014 5:47:39 PM
superuser22    2/3/2014 5:50:48 PM    2/3/2014 7:39:39 PM
superuser22    2/3/2014 7:43:32 PM    2/3/2014 8:10:59 PM
userX    2/3/2014 10:24:29 AM    2/3/2014 10:24:40 AM
userX    2/3/2014 10:55:32 AM    2/3/2014 10:55:44 AM
EOD;


// SET THE TIMEZONE TO GREENWICH
date_default_timezone_set('UTC');


// MAKE AN ARRAY FROM THE LINES AND DISCARD THE TOP ROW
$arr = explode(PHP_EOL, $str);
unset($arr[0]);


// MAKE AN ARRAY OF OBJECTS
$datas = array();
$users = array();
foreach ($arr as $str)
{
    // BREAK EACH LINE ON TAB CHARACTERS
    $sub = explode("\t", $str);

    // SAVE THE USER ID ONLY ONCE
    $users[$sub[0]] = $sub[0];

    // COLLECT THE HOURS WORKED
    $datas[] = new Datex($sub[0], $sub[1], $sub[2]);
}


// AGGREGATE THE DATA FROM THE OBJECTS INTO ONE FOR EACH USER
foreach ($users as $user)
{
    $aggrs[$user] = new Aggregate($user, $datas);
}


// USE THE AGGREGATED TIMES FOR EACH USER TO PRODUCE THE STATEMENTS
foreach ($aggrs as $user => $aggr)
{
    foreach ($aggr->hours as $timex => $secs)
    {
        $date = date('Y-m-d', strtotime($timex));
        $hour = date('H',     strtotime($timex));
        echo PHP_EOL . "$user $date $hour 1";
    }
}


Class Datex
{
    public $user, $hours;
    public function __construct($user, $alpha, $omega)
    {
        $this->user  = $user;
        $this->hours = array();

        // MAKE TIMESTAMPS FROM THE PERIOD
        $ta = strtotime($alpha);
        $tz = strtotime($omega);

        // GET INITIAL HOUR BOUNDARIES
        $ha = date('Y-m-d H:00:00', $ta);
        $hz = date('Y-m-d H:00:00', $tz);

        // DO THESE TIMES OCCUR WITHIN THE SAME HOUR?
        if ($ha == $hz)
        {
            $secs = $tz - $ta;
            $this->hours[$ha] = $secs;
            return TRUE;
        }

        // IF TIMES ARE SPREAD ACROSS MORE THAN ONE HOUR
        while ($ha < $hz)
        {
            // GET THE STARTING POINT OF THE NEXT HOUR
            $ha1 = date('Y-m-d H:00:00', strtotime($ha . ' + 1 HOUR'));

            // GET THE NUMBER OF SECONDS IN THIS HOUR
            $secs = strtotime($ha1) - $ta;
            $this->hours[$ha] = $secs;

            // BUMP THE HOUR AND THE STARTING TIMESTAMP
            $ha = $ha1;
            $ta = strtotime($ha);
        }

        // AT THE END, TAKE THE PART OF THE LAST HOUR
        $secs = $tz - strtotime($hz);
        $this->hours[$hz] = $secs;
    }
}


Class Aggregate
{
    public function __construct($user, $arr)
    {
        // INPUT IS THE ARRAY OF DATEX OBJECTS
        foreach ($arr as $obj)
        {
            // AGGREGATION MATCHING USERS
            if ($obj->user == $user)
            {
                // FIRST ENTRY - JUST COPY THE HOURS
                if (empty($this->hours))
                {
                    $this->hours = $obj->hours;
                }
                // SUBSEQUENT ENTRIES - ADD TO THE HOURS
                else
                {
                    foreach ($obj->hours as $ha => $secs)
                    {
                        // IF THERE IS ALREADY DATA FOR THIS HOUR
                        if (array_key_exists($ha, $this->hours))
                        {
                            // ADD TO THE SECONDS IN THIS HOUR
                            $this->hours[$ha] += $secs;
                        }
                        // IF THERE IS NOT ALREADY DATA FOR THIS HOUR
                        else
                        {
                            // JUST STORE THE SECONDS FOR THIS HOUR
                            $this->hours[$ha] = $secs;
                        }
                    }
                }
            }
        } // END ARRAY COMBINATION AND SUMMATION

        // ELIMINATE THE HOUR IF LESS THAN 30 MINUTES WORKED
        foreach ($this->hours as $ha => $secs)
        {
            if ($secs < 1800) unset($this->hours[$ha]);
        }
        return $this->hours;
    }
}

Open in new window

Hope that helps, ~Ray
0
 
c_kedarCommented:
This can be done with SQL-only solution:

select login.*, 
(truncToHour(loginDt)+interval i.n hour) as DATE_HOUR,
timestampdiff(MINUTE, greatest(loginDt, truncToHour(loginDt)+interval i.n hour), least(logoutDt, truncToHour(loginDt)+interval i.n+1 hour)) ACTIVE_MINUTES
from login
join intseq i 
	on i.n between 0 and timestampdiff(HOUR, truncToHour(loginDt), truncToHour(logoutDt))
order by user_id, date_hour

Open in new window

truncToHour function should return datetime truncated to hour.
intseq is a table with integer numbers
Required criterion of 30+ minutes can now be implemented on this result set.

Following will explain how this works:
HourOfLogin = truncToHour(loginDt)
HourOfLogut = truncToHour(logoutDt)
NumberOfHoursOfDayInSession = (loginEndHour-HourOfLogut)+1
NthSplitHourStartTime = HourOfLogin+n
NthSplitHourEndTime = HourOfLogin+n+1
activeMinutesInNthSplitHour = least(nThSplitHourEndTime, logoutDt) - greatest(nThSplitHourStartTime,loginDt)

Open in new window


http://sqlfiddle.com/#!2/04e289/7
0
 
DalexanAuthor Commented:
Holy cow, such a beautiful solution!!!!!
0
 
Ray PaseurCommented:
@Dalexan: If you did not want a PHP solution, why did you waste time asking for one?  More than two weeks ago I posted a tested-and-working PHP solution.  It's traditional at EE to award at least some of the points to the first correct and working solution.  It's not a big deal, just a little surprised that you'd ask for something then ignore it. ~Ray
0
 
DalexanAuthor Commented:
Ray, I value your answer and your help as you have helped me out numerous times on other issues. In hindsight if I could go back and re-award 100 pts to you, your solution would have worked but in the end the business decision was set on a pure SQL solution.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now