datetime - SQL Server: WHILE Within SELECT? -


as part of report built through sql server 2012's report builder, i'm trying measure amount of time has "officially" taken workers various tasks. in task table, have task created datetime field, task resolved datetime field, , have other tables clocking on/off times i'm able take data from.

i want measure in terms of time during they're supposed have been working. doesn't include overnight periods they're not @ work, means it's not simple as

datediff(minutes,taskcreated,taskresolved).

one way i've found work out how many hours they've taken each day separate fields, , sum fields:

created          |resolved         |day1time|day2time |day3time |totaltime 14/04/2015 20:00 |16/04/2015 10:00 |01:00:00|12:00:00 |01:00:00 |14:00:00

unfortunately, don't know how long take finish job account this, don't know how many fields need using, code repetitive , therefore terrible if jobs take month or more complete.

as more familiar programming languages java , c++ sql, seems me better done function creation , resolution parameters, loop going through each day, , variable incrementing day's downtime. unfortunately, seems don't have permissions create temporary functions, i'm wondering is possible while loop calculate time within select statement? i've tried this, code validator says it's invalid.

sorry don't have code explain i'm trying @ moment, i'm away work pc, i'd this:

created          | resolved         | time taken | priority | worker 01/01/2015 12:00 | 01/01/2015 13:00 | 01:00:00   |        | c forbes 02/03/2015 20:00 | 03/03/2015 11:00 | 03:00:00   | b        | j flansburgh 31/05/2015 18:50 | 01/05/2015 10:50 | 00:10:00   | d        | e idle 14/04/2015 20:00 | 16/04/2015 10:00 | 14:00:00   | d        | ng

c forbes - standard job - datediff work here

j flansburgh - worker finishes @ 21:00 weekdays, , starts @ 09:00, 1 hour on day 1 + 2 hours on day 2 = 3 hours total

e idle - finishes @ 19:00 on sundays, , finished job before (s)he officially supposed start on monday. 10 minutes on sunday 31st, 0 minutes on monday 1st = 10 minutes

a ng: finishes @ 21:00 weekdays, , starts @ 09:00... 1 hour on 14th+12 hours on 15th+1 hour on 16th = 14 hours total

another idea i've had while typing question out while loop, taking 1 line @ time out of table, working out time individual line, appending each line new table. better?

thanks in advance tries help.

i depends on content in other table contains on/off times. assuming can join between 2 tables based on task (and worker?) think should able like:

select tasks.id, tasks.created, tasks.resolved,  sum(datediff(minutes,timesheet.end, timesheet.start) [time taken], tasks.priority, tasks.worker tasks    inner join timesheet on tasks.id = timesheet.taskid , tasks.worker = timesheet.worker group tasks.id, tasks.created, tasks.resolved, tasks.priority, tasks.worker

Comments

Post a Comment

Popular posts from this blog

angularjs - ADAL JS Angular- WebAPI add a new role claim to the token -

i have created angular spa webapi backend using adal js authentication.as there no roles in ad, need manually add role claims in order give users access different api controllers. the roles stored in database. expecting inject claim through call webapi after authentication ad. webapi code might this. identity.addclaim(new claim("role", "user")); var ticket = new authenticationticket(identity, props); var accesstoken = startup.oauthbeareroptions.accesstokenformat.protect(ticket); is possible replace adal idtoken new token? is viable solution or there other better way handle this? as initial token generated azuread, possible edit token add new claim? appreciated. graph api supporting group claims. see here: http://justazure.com/azure-active-directory-part-4-group-claims/ if @ examples on page, users assigned groups , app can check group in claims. in current version of portal, need app manifest , modify it.
Read more

php - CakePHP HttpSockets send array of paramms -

i problem when make request (i can request). $link = 'http://uri/path/?param1=1&sid=2&sid=3&sid=4' $http = $httpsocket = new httpsocket(); $res = $httpsocket->get($link); if check request pr($http); i see : [line] => /url/path/?param1=1&sid%5b0%5d=2&sid%5b1%5d=3&sid%5b2%5d=4 http/1.1 and i'm getting empty response because server doesn't know parameters sid%5b0%5d=2 when try send parameters array : $data = array('param1' => '1', 'sid' => array('2','3', '4')); i see same changes %5b0%5d - additional indexes. how fix it? unfortunately , can not change server side, if send request in browser, i'll normal response in json format. when make request, can parameters this: $param1 = $_get['param1']; $sid= $_get['sid']; tip: use diferente names parameters. have multiples sid. use sid1, sid2, sid3... update: can parameters this:
Read more

node.js - Using Node without global install -

i'm working on few node & electron (atom shell) projects , i'm curious how them work without user having install node globally. i've seen few applications including node directory in project contains node.exe binary. how go setting node project use binary? edit: thought should add, i've searched everywhere answer this, unrelated results.
Read more