sql - Calculation within the subquery, substract an amount from a different table -

-

i have been working on query on day now.

from total column in first query (calls) below, need add or subtract amount number second table (callsincrease) months within date range (from startdate enddate skill equals skill in query).

i tried create field first amount subtract each month, use subtract total.

i tried different versions of the sub-queries below:

expr1: (   select amount    (     select callsincrease.skill, sum(callsincrease.amount) amount     callsincrease     group callsincrease.skill   )   [skill]=(select [skill] callsincrease)     , [callmonth] between (select [startdate] callsincrease)       , (select [enddate] callsincrease) )   expr1: (   select sum([%$##@_alias].amount) sumofamount   (     select callsincrease.skill, sum(callsincrease.amount) amount     callsincrease     group callsincrease.skill)  [%$##@_alias]     group [%$##@_alias].skill     having ((([%$##@_alias].skill)=forecast.skill)) )

the sql calls query:

select   cdate(dateserial(year([forecastdate]),month([forecastdate]),1)) forecastmonth,   forecast.skill,   sum(forecast.normalizeddata) total forecast group cdate(dateserial(year([forecastdate]),month([forecastdate]),1)),   forecast.skill having (((sum(forecast.normalizeddata)) not null));

calls query:

forecastmonth   skill               total 01-jan-08   generalist english      56541 01-jan-08   generalist spanish      868 01-jan-08   public safety english   26837 01-jan-08   public safety spanish   401 01-jan-08   total                   34584 01-jan-08   utilities english       48332 01-jan-08   utilities spanish       1605 01-feb-08   generalist english      52176

calls increase table:

id  eventid             skill        startdate  enddate     amount 1   9/5/2008    generalist english  01-jan-08   31-aug-08   1800 3   7/10/2010   generalist english  01-jan-08   30-jun-10   -3000 4   12/1/2011   generalist english  01-jan-08   30-nov-11   2500 5   3/1/2013    generalist english  01-jan-08   28-feb-13   200 6   12/1/2011   generalist spanish  01-jan-08   30-nov-11   80 7   7/1/2010    public safety eng   01-jan-08   30-jun-10   -4700 8   3/10/2009   utilities english   01-jan-08   28-feb-09   7000

i hope makes sense, appreciated.

i came following horrendous mess. there hope dbms's query optimizer can reasonable it, in part because avoids correlated subqueries. indeed, there hope dbms accept query more or less written.

select   monthlyforecast.month forecastmonth,   monthlyforecast.skill skill,   (monthlyforecast.total + coalesce(adjustment.amount, 0)) total     (select       cdate(dateserial(year([forecastdate]),month([forecastdate]),1)) month,       forecast.skill skill,       sum(forecast.normalizeddata) total     forecast     group [month], [skill]     having total not null   ) monthlyforecast    left outer join      (select         forecastmonths.month month,         callsincrease.skill skill,         sum(callsincrease.amount) amount               (select distinct           cdate(dateserial(year([forecastdate]),month([forecastdate]),1)) month           forecast         ) forecastmonths          join callsincrease           on forecastmonths.month             between callsincrease.startdate , callsincrease.enddate       group         [month], [skill]     ) adjustment      on monthlyforecast.month = adjustment.month       , monthlyforecast.skill = adjustment.skill ;

for it's worth, need compare specific dates date range significant complicating factor, both designing query , running it. there other ways handle problem 1 i've used above, i'm doubtful of them yield simpler query.


Comments

Post a Comment

Popular posts from this blog

java - Oracle EBS .ClassNotFoundException: oracle.apps.fnd.formsClient.FormsLauncher.class ERROR -

-
i need in configuring java on machine because, when try loading forms, loader goes in circles , gives error: java.lang.classnotfoundexception: oracle.apps.fnd.formsclient.formslauncher.class @ sun.plugin2.applet.applet2classloader.findclass(unknown source) @ sun.plugin2.applet.plugin2classloader.loadclass0(unknown source) @ sun.plugin2.applet.plugin2classloader.loadclass(unknown source) @ sun.plugin2.applet.plugin2classloader.loadclass(unknown source) @ java.lang.classloader.loadclass(unknown source) @ sun.plugin2.applet.plugin2classloader.loadcode(unknown source) @ sun.plugin2.applet.plugin2manager.createapplet(unknown source) @ sun.plugin2.applet.plugin2manager$appletexecutionrunnable.run (unknown source) @ java.lang.thread.run(unknown source) i have tried re-installing firefox, older versions of java runtime environments, disabling proxy. what missing? application working on other machines. i spent around 100 hours finding s...
Read more

c# - how to use buttonedit in devexpress gridcontrol -

-
Image
i trying create gridcontrol buttonedit in 1 of columns. when user clicks button edit, popups form select product. when selection done on popup, returns datarow selection main grid below. but when column loses focus, value wrote column disappear. here code creates gridcontrol's data , buttonedit's click event. private void frmsiparisnew_load(object sender, eventargs e) { dt = new datatable(); dt.columns.add("malzeme_kodu",typeof(string)); dt.columns.add("malzeme_aciklama", typeof(string)); dt.columns.add("adet", typeof(decimal)); dt.columns.add("birim", typeof(string)); dt.columns.add("fiyat", typeof(decimal)); dt.columns.add("kur", typeof(string)); dt.columns.add("tutar", typeof(decimal)); datarow dr = dt.newrow(); dt.rows.add(dr); gc.datasource = dt; } private void repositoryitembuttoneditmal...
Read more

How do you convert a timestamp into a datetime in python with the correct timezone? -

-
you naively expect work: import pytz datetime import datetime def tz_datetime_from_timestamp(timestamp): """convert timestamp datetime """ tz = pytz.timezone('australia/perth') server_time = datetime.utcnow() delta = tz.utcoffset(server_time) value = datetime.utcfromtimestamp(timestamp).replace(tzinfo=tz) return value + delta print tz_datetime_from_timestamp(1416387060) and convert timestamp 1416387060 wed nov 19 16:51:00 2014 gmt+8:00. ...but not. prints: 2014-11-19 16:51:00+07:43 the timezone of australia/perth not gmt+7:43. it gmt+8:00. unequivocally stated on australian government website http://www.australia.gov.au/about-australia/our-country/time : awst equal coordinated universal time plus 8 hours (utc +8). so, heck pytz pulling 7.43 from? well, turns out pytz pulls data http://www.iana.org/time-zones time zone database, , database states: # western australia # # rule name type in on @...
Read more