sql server - Need help with SQL query -

-

i have following data:

 transactiontype        lane      transactiondate ------------------------------------------------            sale          17  2011-07-21 06:15:34          signon          18  2011-07-21 07:00:00            sale          18  2011-07-21 07:06:43          signon          20  2011-07-21 08:01:12            sale          18  2011-07-21 07:30:00            sale          18  2011-07-21 08:30:00            sale          18  2011-07-21 09:30:50            sale          20  2011-07-21 10:15:34         signoff          18  2011-07-21 19:00:00         signoff          20  2011-07-21 19:10:52

i need following information:

  1. the count of lane lie between signon , signoff transaction types
  2. the total time each lane @ signon state, e.g. row lane number 18 12 hours, row lane 20 has time of 11 hours, 9 minutes , 40 seconds

this of it... including multiple sign in/out per lane , how many transactions within each login/out time period. once result, can query total counts of active lanes / sessions whatever roll-up values... such total times lane 18 signed in/out total sales transactions, or overall how many lanes open , accounted such sale activity.

since group on lane, signon/off, applied min() hours, minutes, seconds constant per each signon/off session.

select        prequery.lane,       prequery.signedon,       prequery.signedoff,       min( datediff(hour, prequery.signedon, prequery.signedoff )) hours,       min( datediff(minute, prequery.signedon, prequery.signedoff )) minutes,       min( datediff(second, prequery.signedon, prequery.signedoff )) seconds,       count(*) numofsales            ( select                t1.lane,               t1.transactiondate signedon,               min( t2.transactiondate ) signedoff                           trans1 t1                  left join trans1 t2                     on t1.lane = t2.lane                     , t2.transactiontype = "signoff"                     , t2.transactiondate > t1.transactiondate                            t1.transactiontype = "signon"             group                t1.lane,               t1.transactiondate ) prequery        left join trans1 t3           on prequery.lane = t3.lane          , t3.transactiontype = "sale"          , t3.transactiondate between prequery.signedon , prequery.signedoff    group        prequery.lane,       prequery.signedon,       prequery.signedoff

Comments

Post a Comment

Popular posts from this blog

linux - Using a Cron Job to check if my mod_wsgi / apache server is running and restart -

-
my group , running server based upon django , uses mod_wsgi run apache server. not working on project after over, attempting set cronjob similar functionality check if apache server has shut down(system restart or power failure), , if has, restart server me. i've found documentation on how check if apache server down , restart server if is, our server uses https , our start command pretty verbose. can use functionality provided in these examples: https://askubuntu.com/questions/277389/cron-job-to-restart-apache https://www.digitalocean.com/community/tutorials/how-to-use-a-simple-bash-script-to-restart-server-programs or need more complicated process make happen? the command use start server is python manage.py runmodwsgi --host 0.0.0.0 --port 8001 --https-port 8000 --ssl-certificate (certificate location) --server-name (domain name) i'm pretty new linux , using both mod-wsgi apache appreciated. you better off using --setup-only option mod_wsgi-expres...
Read more

actionscript 3 - TweenLite does not work with object -

-
i got following problem: i have object called tempscore game. this object blitted canvas renderer via copypixels method. object not display object. it's score-object (self made). score-object extends object called basicblitarrayobject. basicblitarrayobject extends eventdispatcher (therefore no display object). i tried apply several different tweenlite-plugins tempscore-object (i.e. transformaroundcenter, colormatrixfilter, etc.). nothing happens. absolutely nothing. sometimes error messages (when plugin requires display object , object not display object). far good. according greensock (maker of tweenlite) engine can tween numeric property of object. when plugin transformaroundcenter requires display object tweening have modify plugin working non-display object (tempscore). can't because it's way hard me. my game rests upon code: http://www.8bitrocket.com/book/ch11_blastermines.zip try apply tweenlite object called tempmine inside game class blastermines. ...
Read more

jQuery Ajax Render Fragments OR Whole Page -

-
i'm in throes of building application wraps mess of legacy code. page working on, need substitute fragment divs constructed on back-end -- or else need replace entire page altogether. idea there dynamically controlled flow needs satisfied before can forward legacy product. substituting fragments works fine, seen in below my_body_content swap. trouble comes when i'm trying render not fragment, whole page, in "body" swap. @ point page goes blank. i want similar errors returned server. want nice rest 404 error messages displayed on screen, legacy-product 404s show in legacy-product 404 page. yes, requirements project weird. that's not problem can fix. here's jquery invocation, names changed protect guilty: $.ajax({ url: "places/things", type: "post", data: json.stringify(somebadassobject), datatype: "text", accepts: "text/html", contenttype: "application/json; charset=...
Read more