updating a table using the max date and values from another table using sql in ms-access -

-

i have following scenario: want update tableb using rating tablea. challenge is: ratings change randomly , when updating tableb's records, have take in account date of match , if rating changed let's on tuesday , game on monday before, want rating previous rating not latest one. 

#table a: contains rating of players, changes randomly @ date depending #on drop of form players    pid| rating | datechange  | 1  |    2   | 10-may-2014 | 1  |    4   | 20-may-2015 | 1  |   20   | 1-june-2015 | 2  |    4   | 1-april-2014| 3  |    4   | 5-april-2014| 2  |    3   | 3-may-2015  |  #table b: contains match sheets. every player has different match sheet #and plays different dates.  msid | pid  | matchdate    | win | rating |  1   |  2   | 10-may-2014  |  no |    0   |  2   |  1   | 15-may-2015  | yes |    0   |  3   |  3   | 10-apr-2014  |  no |    0   |  4   |  1   | 21-apr-2015  | yes |    0   |  5   |  1   | 3-june-2015  | yes |    0   |  6   |  2   | 5-may-2015   |  no |    0   |  #i trying achieve running ms-access query: want #every players rating @ time match played not current #rating.   msid | pid  | matchdate    | rating |  1   |  2   | 10-may-2014  |    4   |  2   |  1   | 15-may-2015  |    2   |  3   |  3   | 10-apr-2014  |    4   |  4   |  1   | 21-apr-2015  |    2   |  5   |  1   | 3-june-2015  |    20  |  6   |  2   | 5-may-2015   |    3   |

i tried following code: 

update [b-table] wdev set wdev.rating = ( select b.msid, b.pid, b.matchdate, a.rating rating  [b-table] b inner join [a-table]   on b.pid = a.pid inner join (        select max(y.datechange) mdc, y.pid, z.matchdate   [b-table] z   inner join [a-table] y    on z.pid = y.pid   , y.datechange <= z.matchdate   group y.pid, z.matchdate) c   on c.mdc = a.datechange  , a.pid = c.pid  , b.matchdate = c.matchdate) , b.msid = wdev.msid

sqlfiddle database schema

in summary: want rating corresponding max date change on or before date of match.

i think want correlated subquery:

update [b-table] b     set rating = (select top 1 rating                   [a-table]                   a.pid = b.pid ,                         a.datechange <= b.matchdate                   order a.datechange desc                  ) ;

note: because of way ms access processes top, subquery return multiple rows in event of tie. normal solution include additional key value in order by, prevent ties. however, there not seem unique key in "a" table.


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

android - Crash when clicking button with custom theme -

-
i creating custom theme button , using android:onclick event of button xml handle click of button. due reason crashing below exception java.lang.illegalstateexception: not find method myonclick(view) in activity class android.view.contextthemewrapper onclick handler on view class android.widget.button id 'button1' and working fine if remove theme attribute button, below theme button <style name="buttontheme" parent="@android:style/widget.button"> <item name="android:textcolor">#ff0000</item> <item name="android:shadowcolor">#ff000000</item> </style> and button defined in xml below, <button android:id="@+id/button1" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_below="@+id/textview1" android:layout_margin="20dp" android...
Read more