OLTP Logic Problem- Noob needs assistance.


  • I am relatively new to sql syntax, but not concepts.


    Working with an OLTP without any sort of warehousing at this time.

    (column)SessionID - ASP generated

    (column)TransType - one of many values we use to determine what the client was doing at the time. The important ones for this problem are Session_End, Session_Start, Session_Restore

    Other non obfuscated columns that may be important - cDate, TransID(not a PK but never repeats).

    -----------Objective / Problem---------

    The objective is to calculate session times.  Because there isnt always an end date, sometimes several starts in a row (some same sessionid some completely different sessionid)  this has proven to a be a difficult venture for me.

    A strategy that was brought to me had the following constraints

    1. look for a START_SESSION (SESSION_RESTORE) or MIN(cDate) and then look for an END_SESSION

    2. (now it gets confusing for me) If there is another START_SESSION(SESSION_RESTORE) before an END_SESSION... (2nd) START_SESSION(SESSION_RESTORE) - 1 = END_SESSION & (2nd) START_SESSION(SESSION_RESTORE) = start of a new session.

    3. (yep it gets worse)  If two END_SESSIONS Exist between START_SESSION(SESSION_RESTORE), it would be (1st) END_SESSION +1 = New Start Session to Pair with the (2nd) END_SESSION

    4. Session Times are always greater than 10 seconds.

    If you prefer a bit of a matrix spin to it.

    restore + (end - 1) = session

    start + (end - 1) = session

    (end + 1) + (end - 1) = session

    0 * start/restore = Min(cDate) + (end-1) || Max(cDate)

    Session > 10 s

    There are multiple sessionIDs in this table.. it is a 0-1NF raw OLTP table.

    I am just a minion though, no warehousing or marts.  You are able to use another table if you destroy it at the end (using report builder 3.0 to call the sproc) you can use @ # and ## too if you would like. 

    I do understand 'what you should do is...' but sometimes you cant do what you should do.  Such is life.

    • Modificato DJPsiPhi mercoledì 25 aprile 2012 16:22
    mercoledì 25 aprile 2012 16:13

Tutte le risposte

  • what I mean by new...  I havent used Row_number, rank and some other hot pink syntaxes.
    TransID SessionID TransType cDate
    22421 sagdassdak Start_Session 4/12/2012 3:00:00
    22422 sagdassdak Step 4/12/2012 3:00:08
    22423 sagdassdak Start_Session 4/12/2012 3:01:00
    22430 sagdassdak Step 4/12/2012 3:01:03
    22431 sagdassdak Step 4/12/2012 3:01:03
    22432 tygjkasd Session_Restore 4/12/2012 3:01:13
    22433 sagdassdak Step 4/12/2012 3:01:03
    22434 tygjkasd Step 4/12/2012 3:01:23
    22435 tygjkasd Step 4/12/2012 3:01:53
    22436 tygjkasd Step 4/12/2012 3:02:03
    Something to this nature.. if you need more let me know and just add more rows to show what I am seeing.
    • Modificato DJPsiPhi mercoledì 25 aprile 2012 18:08
    mercoledì 25 aprile 2012 16:15