sql server - How can I join 3 SQL tables and return latest current date and status? -


i have 3 tables: i'm trying write sql query joins 3 tables return first , last name table a, status of 1 there no 2 logged afterwards , logdate (only want current day) table c.

tablea: 

userid | firstn | lastn | ------------------------- 2324   | john   | doe   | 2034   | jane   | doe   | 2946   | mike   | blank |

tableb:

viewid | userid |  ----------------- 2315   | 2324   |  8956   | 2034   |  6587   | 2946   |

tablec:

viewid | logdate                 | status | ------------------------------------------- 2315   | 2017-02-14 11:03:47.000 | 1      | 2315   | 2017-02-14 10:14:47.000 | 2      | 2315   | 2017-02-14 10:00:19.000 | 1      |

in status column of tablec, 1 means viewing , 2 means done viewing, logdate giving date , time of status.

tablea , tableb have userid column in common. tableb , tablec have viewid column in common.

a person can view document multiple times in 1 day it's possible viewid shows multiple times in 1 day different statuses want return current status 1 user has not finished viewing document on current day.

so if @ tablec, can see same user viewed document @ 2017-02-14 10:00:19.000, finished @ 2017-02-14 10:14:47.000, , viewed again @ 2017-02-14 11:03:47.000. in example, want user viewing document @ 2017-02-14 11:03:47.000 because user has not finished viewing (status did not change 2 again yet).

i hope i'm making sense in explaining i'm trying accomplish....

so, tried haven't gotten want. can't figure out how join tablea first , last name , also, stuck trying current status of 1 if user has not finished viewing. it's returning every status of 1 throughout day.

select      tblb.viewid,     tblc.logdate, tblc.status,     tblb.userid      tableb tblb inner join      tablec tblc on tblb.viewid =  tblc.viewid     logdate >= cast(current_timestamp date)     , logdate < dateadd(dd, 1, cast(current_timestamp date))     , status = 1

assuming it's impossible have sequence of statuses (1,1,2) i.e. open same doc without closing first, need last status. can figure out users having open docs , [left] join tablea.

select ta.firstn, ta.lastn, tbc.*     (select userid, tblc.*     tableb tblb     cross apply(        select top(1) *         tablec tblc         tblb.viewid =  tblc.viewid          , logdate >= cast(current_timestamp date)          , logdate < dateadd(dd, 1, cast(current_timestamp date))        order logdate desc        ) tblc     status = 1     ) tbc  left join tablea ta on ta.userid = tbc.userid;

-

Comments

Post a Comment

Popular posts from this blog

python - How to insert QWidgets in the middle of a Layout? -

Image
i'm using qt framework build graphical user interface. use qgridlayout position qwidgets neatly. gui looks this: my application regularly adds new widgets gui @ runtime. these new widgets not added @ end of qlayout, somewhere in middle. the procedure bit cumbersome. applied on figure above, need take out widg_c , widg_d , ... qgridlayout. next, add widg_x and widg_y , , put other widgets again. how remove widgets qgridlayout: for in reversed(range(mygridlayout.count())): self.itemat(i).widget().setparent(none) ### as long you're dealing small amount of widgets, procedure not disaster. in application display lot of small widgets - perhaps 50 or more! application freezes second while procedure ongoing, annoying user. there way insert widgets somewhere in qlayout, without need take out other widgets? edit: apparently solution qvboxlayout simple. use function insertwidget(..) instead of addwidget(..) . docs can found link: http://doc.qt.io/qt-5/qboxlayou
Read more

python - serve multiple gunicorn django instances under nginx ubuntu -

i need serve 2 webs apps (django) on gunicorn under different domain names using nginx on ubuntu. started using tutorial: https://www.digitalocean.com/community/tutorials/how-to-set-up-django-with-postgres-nginx-and-gunicorn-on-ubuntu-16-04 it worked fine 1 using that. tried same thing second gives me 502 gateway second domain. tried following tutorial: http://michal.karzynski.pl/blog/2013/10/29/serving-multiple-django-applications-with-nginx-gunicorn-supervisor/ i'm @ part run gunicorn start script: sudo bin/gunicorn_start i response: starting webuildblack root traceback (most recent call last): file "/home/devin/webuildblack/bin/gunicorn", line 7, in <module> gunicorn.app.wsgiapp import run importerror: no module named 'gunicorn.app'; 'gunicorn' not package you have named program gunicorn.py gunicorn -package. rename file , remove gunicorn.pyc file.
Read more

module - Prestashop displayPaymentReturn hook url -

i new prestashop developer , trying create paymentmodule. have got show payment method can not proceed purchase because not know hot works. does know should redirect run hooddisplaypaymentreturn method? i happy if explain me complete navigation map make purchase. anyway, can find relation between hooks , pages? when have new module payment rely on simplest provided prestashop: bankwire. inside can find 3 hooks. hookpayment: public function hookpayment($params) { if (!$this->active) return; if (!$this->checkcurrency($params['cart'])) return; $this->smarty->assign(array( 'this_path' => $this->_path, 'this_path_bw' => $this->_path, 'this_path_ssl' => tools::getshopdomainssl(true, true).__ps_base_uri__.'modules/'.$this->name.'/' )); return $this->display(__file__, 'payment.tpl'); } hookdisplaypaymenteu: public fun
Read more