sql - Doing a calculation between two rows with EXTRA CRITERIA? -

- 

name      exactdate  presents      location bob1         2011        1          home bob2         2008        2          school      bob2         2012        3          school mary2        1986        4          school        mary1        2001        5          home mary1        2012        6          home     kate1        2011        7          home kate1        2012        8          home kate2        2011        9          school  celia2       2011       10          school  celia2       1986       11          school   celia1       1972       12          home celia1       2012       14          home  celia2       2012       13          school

this problem done in sql in ms access 2003 query.

so goal subtract amount of presents kate got celia on same year ( since there few different present values same year choose have priority of home > school....for example celia , kate both receive presents in 2012 celia gets both home presents , school presents in 2012 in case choose home present value calculation) , out put should following: 

name              exactdate        presentsdiff celiaminuskate     2011                      3 celiaminuskate     2012                      6

so far have :

select 'celiaminuskate'as [name],t1.[date] [exact date], t1.presents t2.presents [presents diff] some_table t1, some_table t2

part think needs fixed?? 

where (t1.name = 'celia1'>'celia2') , (t2.name = 'kate1'>'kate2') , t2.exactdate = t1.exactdate

to indicate priority? i'm not sure how 

order t1.exactdate

i created query , saved qryceliaandkategiftdates. returns distinct exactdate values celia , kate both had gifts recorded.

notice re-named name field recipient, because name reserved word.

select distinct celia.exactdate     [select exactdate     some_table     recipient "celia*"     ]. celia     inner join [         select exactdate         some_table         recipient "kate*"         ]. kate     on celia.exactdate = kate.exactdate order celia.exactdate;

then used correlated subqueries return correct presents values celia , kate on each of exactdates. 

select     raw.recipients [name],     raw.exactdate  [exact date],     (raw.celia_presents - raw.kate_presents) [presents diff]     [select     'celiaminuskate' recipients,     dates.exactdate,     (select top 1 presents     some_table             recipient "celia*"         , exactdate = dates.exactdate     order location) celia_presents,     (select top 1 presents     some_table             recipient "kate*"         , exactdate = dates.exactdate     order location) kate_presents     qryceliaandkategiftdates dates]. raw;

it returns results requested when run access 2003. correlated subqueries notoriously slow, i'll interested see other answers get.


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