oracle11g - Oracle SQL 11g - next business day query -


good day, working on code -

1) if request submitted after 5:00pm eastern timezone (america) on weekday (monday through thursday) , before 7:59:59am eastern timezone (america) on weekday, date changed next business day @ 8:00 am.

2) if request submitted between 5:00 pm on friday , before 7:59:59 following monday, time rolls noted above.

3) then, code needs check date field compare "accepted" time (within 4 hours of submitted date or "new" date).

for example:

if request 001 submitted 04/17/2013 02:00am, new date 04/17/2013 08:00am.

if request 002 submitted 04/17/2013 06:45pm, new date 04/18/2013 08:00am.

if request 003 submitted 04/20/2013 05:45am (this saturday), new date 04/22/2013 08:00am.

i have been piecemailing code together, in hopes can bring in final code.

this have far (for date conversion code)

            ,case          when to_date(('date_requested'),'day',nls_date_language = english) in ('friday','saturday'))         next_day(to_date(('date_requested'),'monday') + 8 / 24)         else date_requested      end weekend_converted

for code check if request accepted within 4 hours

     select rit_request_v.*      ,case       when date_accepted null 'not acknowledged'      when date_accepted > (date_requested + 4 / 24) 'over 4 hours'      else 'within 4 hours'      end acknowledgement       rit.rit_request_v       (("date_requested") between trunc(sysdate, 'yyyy') , trunc(sysdate))

as noted in 1 of comments below, updated code, , getting "invalid identifier" errors - 

    select rit_request_v.*     ,case when to_char(date_requested,'d') in (1,6,7)           next_day(date_requested,'monday')           else date_requested + 1 end weekend_converted      ,case when date_accepted null 'not acknowledged'           when weekend_converted null 'not acknowledged'           when date_accepted > (date_requested + 4 / 24) 'over 4 hours'           when date_accepted > (weekend_converted + 4 / 24) 'over 4 hours'           else 'within 4 hours' end acknowledgement      rit.rit_request_v      (("date_requested") between trunc(sysdate, 'yyyy') , trunc(sysdate))                  , form_id in         (2011,2014,5007,5036,5039,7007,10000,10001,10005,10007,10011,10024,10025,10029,10032,10033,10034,10035,10036,10037,11011,11013,11999,36001)

your case generating weekend_converted seems dealing weekend in original version, , seems confused on friday included; updated 1 shifting every date same time either next day or on following monday, losing both 08:00 part , of weekday don't shift day @ all.

you getting 'invalid identifier' in second version because defining weekend_converted pseudocolumn , trying use in same level of query, isn't allowed. need define in @ 1 level, refer in outer select.

this seems cover rules, haven't tested dates:

select r.*,     case         when to_char(date_requested, 'dy',                 'nls_date_language=english') in ('sat', 'sun')             or (to_char(date_requested, 'dy',                 'nls_date_language=english') = 'fri'                 , extract(hour cast(date_requested timestamp)) >= 17)         trunc(next_day(date_requested, 'mon')) + interval '8' hour         when extract(hour cast(date_requested timestamp)) >= 17         trunc(date_requested) + interval '1' day + interval '8' hour         when extract(hour cast(date_requested timestamp)) < 8         trunc(date_requested) + interval '8' hour         else date_requested     end clock_start rit_request_v r;

although, while forcing nls_date_language idea if don't know run, next_day function requires day parameter in local language, you'd better off setting whole session rather on per-function basis.

anyway, gives:

date_requested   date_accepted       form_id clock_start ---------------- ---------------- ---------- ---------------- 2013-04-17 02:00 2013-04-17 11:59       5007 2013-04-17 08:00 2013-04-17 18:45 2013-04-18 11:59       5007 2013-04-18 08:00 2013-04-13 05:45                        5007 2013-04-15 08:00

you can use inline view or cte:

with t ( select r.*,     case         when to_char(date_requested, 'dy',                 'nls_date_language=english') in ('sat', 'sun')             or (to_char(date_requested, 'dy',                 'nls_date_language=english') = 'fri'                 , extract(hour cast(date_requested timestamp)) >= 17)         trunc(next_day(date_requested, 'mon')) + interval '8' hour         when extract(hour cast(date_requested timestamp)) >= 17         trunc(date_requested) + interval '1' day + interval '8' hour         when extract(hour cast(date_requested timestamp)) < 8         trunc(date_requested) + interval '8' hour         else date_requested     end clock_start rit_request_v r date_requested between trunc(sysdate, 'yyyy') , trunc(sysdate) , form_id in (2011, 2014, 5007, 5036, 5039, 7007, 10000, 10001, 10005, 10007, 10011, 10024, 10025, 10029, 10032, 10033, 10034, 10035, 10036, 10037, 11011, 11013, 11999, 36001) ) select t.*,     case         when date_accepted null 'not acknowledged'         when date_accepted > (clock_start + interval '4' hour)         'over 4 hours'         else 'within 4 hours'     end acknowledgement t;

which gives:

date_requested   date_accepted       form_id clock_start      acknowledgement ---------------- ---------------- ---------- ---------------- ---------------- 2013-04-17 02:00 2013-04-17 11:59       5007 2013-04-17 08:00 within 4 hours 2013-04-17 18:45 2013-04-18 11:59       5007 2013-04-18 08:00 within 4 hours 2013-04-13 05:45                        5007 2013-04-15 08:00 not acknowledged

i can refer clock_start because it's defined @ lower level, in case in cte.

you'll notice dates in different format yours. i'd should wrap fields in to_char() display, (a) isn't clear if displayed in sql*plus or wherever, or feed else , should stay dates; , if alter session check nls_date_language set nls_date_format while you're there. again depends dhow you're running it.

you might have check against sysdate @ wrong level. is, include have clock_start of 08:00 today, since it's working on pre-adjustment time. if move outer select can check clock_start in date range instead. might have done intentionally, of course.

hope helps...


Comments

Post a Comment

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers ...
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more

node.js - Bad Request - node js ajax post -

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());...
Read more