sql - Most recent record value with multiple joins and subqueries -


i'm using ms sql server 2016. i'm trying recent measurement value row_number function still getting multiple records instead of recent measurement value. i'm bit outside of sql comfort zone , assistance appreciated. possible use row_number function inside of subquery? 

select     distinct      v_pat_fact.pat_id,     v_pat_fact.pat_name,      cast(patient.birth_date date) "birth date",      v_pat_fact.sex_name "gender",      v_pat_fact.cur_pcp_name,      v_pat_fact.pat_mrn_id,      cast(dateadd(d,cast(fmid7947.meas_value numeric),'12/31/1840') date) "restart date",     cast(dateadd(d,cast(fmid7948.meas_value numeric),'12/31/1840') date) "initial date",     floor(cast(left(cast(fmid7739.meas_value numeric), 4) numeric (8,3))/16) "init goal wt",     floor(cast(left(cast(fmid7912.meas_value numeric), 4) numeric (8,3))/16) "init restart wt",     fmid8794.meas_value "tgt maint wt",     floor(cast(fmid14.meas_value numeric(8,3))/16) "wt lbs",     fmidbmi.meas_value "bmi",     floor(cast(left(cast(fmid8796.fsd_id numeric), 4) numeric (8,3))/16)"total loss",     fmid7752.meas_value "bariatric med"      v_pat_fact v_pat_fact     left  outer join patient        on v_pat_fact.pat_id = patient.pat_id     inner join       ip_flwsht_rec  on v_pat_fact.pat_id = ip_flwsht_rec.pat_id      inner join       ip_flwsht_meas on         ip_flwsht_rec.fsd_id = ip_flwsht_meas.fsd_id          ,         ip_flwsht_meas.flt_id = '343'         ,         ip_flwsht_meas.flo_meas_id in ( '7947', '7948', '7739', '7912', '8794', '8481', '14', '301070', '8796','7752' )     inner join       ip_flo_gp_data on ip_flwsht_meas.flo_meas_id = ip_flo_gp_data.flo_meas_id     left outer join     (         select             distinct             mes.flo_meas_id,             mes.meas_value,             vp.pat_id,             rsdate.meas_value "something"                     pat_enc vp             inner join ip_flwsht_rec rec on vp.pat_id=rec.pat_id             inner join ip_flwsht_meas mes on rec.fsd_id=mes.fsd_id             inner join (                 select                     m.*,                     row_number() on (partition fsd_id  order entry_time desc) rn                                     ip_flwsht_meas m                                     m.flo_meas_id = '7948'             ) rsdate on mes.fsd_id = rsdate.fsd_id                     mes.flo_meas_id ='7948'     ) fmid7948 on v_pat_fact.pat_id = fmid7948.pat_id


Comments

Post a Comment

Popular posts from this blog

c# - Update a combobox from a presenter (MVP) -

i using mvp in project, new in mvp. have 2 comboboxes. when select option in combobox, combobox should filled new data. this action in presenter. view 'view1' in presenter, , introduced combobox1 , combobox2 properties in 'view1', because need 'datasource', 'displaymember', 'valuemember' , 'refresh()' in method below. but, when using pattern, enough send property like public string combobox2 { { return combobox1.selectedvalue.tosstring(); } } into presenter not whole combobox. how can solve problem? public void onselectedindexchangedcombobox1() { if (view1.combobox1.selectedindex == -1) { return; } datatable dt = tools.getdatatable("a path"); var query = (from o in dt.asenumerable() o.field<string>("afield") == farmerview.combobox1.selectedvalue.tostring() orderby o.field<string>("anotherfield") select...
Read more

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -

i trying put lock on table while writing on table , if happened in between roll-back . trying convert below code lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code? @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening . thanks edit-1 attaching code , doing in jdbcdaoimpl.java , mentioned problem comment above test() . app.java package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applic...
Read more