sql server - Select Newest Record -


i have 2 tables (journal , incident). each incident may have more 1 journal entry. want select record , recent journal data.

the section @ bottom filters incidents want see. of those, want journal values associated recent journal entry.

this amalgam of code i've found on here, when run "query execution failed dataset 'dataset1'. unfortunately, don't have access log files see if there clues there.

any appreciated. think may have nested wrong.

select b.incidentnumber ,a.subject ,a.createddatetime ,b.subcategory ,b.effectiveduedate ,b.nextactiondate ,b.profilefullname   ( select   b.incidentnumber  ,a.subject  ,a.createddatetime  ,rn = row_number() on (partition b.incidentnumber order     a.createddatetime desc)  ,b.subcategory  ,b.effectivedate  ,b.nextactiondate  ,b.profilefullname    journal left join incident b on  a.parentrecordnumber = b.incidentnumber  a.category '%kanban%' , (b.status like' %waiting%' or b.status '%active%') , b.subcategory <> 'user termination' , b.subcategory <> 'res temp termination' , a.subject 'up |%' ) x rn = 1

few things:

  • outer selected values should inline view aliased "x" no a. or b. alias in scope in inner query. (except when using coorlation that's 1 level believe)
  • you either need right join instead of left or change order of tables. believe want incidents , recent journal; not journals , related incident if 1 exists. changed order.
  • lastly when using outer joins, can put limits on records table of outer join. clause criteria outer joined tables cause null records generated outer join excluded. resolve must move limiting criteria join or use 'or' statement check null (it's cleaner move join). think of applying limit before join occurs null records incident kept. otherwise outer join simulates inner join excluding records not in both tables (or in case in incident not in journal)

.

select x.incidentnumber  --alias x not a/b aliased 'x'      , x.subject      , x.createddatetime      , x.subcategory      , x.effectiveduedate      , x.nextactiondate      , x.profilefullname (select b.incidentnumber            , a.subject            , a.createddatetime            , rn = row_number() on (partition b.incidentnumber                                       order a.createddatetime desc)            , b.subcategory            , b.effectivedate            , b.nextactiondate            , b.profilefullname         incident b --switched order think want incidents , if journal exists it's value.        left join journal            on a.parentrecordnumber = b.incidentnumber          -- since on if match found b, need move join or lose records created outer join.         , a.category '%kanban%'         , a.subject 'up |%'          --moved clause criteria join since b on "all records side" of outer join can leave b in clause.         (b.status like' %waiting%' or b.status '%active%')           , b.subcategory <> 'user termination'           , b.subcategory <> 'res temp termination') x rn = 1

if not getting records here, i'd start removing of limiting criteria ensure query functioning desired , add in limits see what's causing no records found.


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