sql server - SELECT DISTINCT creates multiple rows -

-

complete sql newbie please kind. have following statement:

select distinct      ttmpo."operationrecordid"     , ttmpo."casenotenumber"     , ttmpo."datetimebooked"     , tspps."startdate"     , aeadm."visit date"     , aeadm."visit time"     , aeadm."episode number"     , aeadm.[visit number]        (("theatrelive"."dbo"."tblspplannedsession" tspps  left outer join      "theatrelive"."dbo"."tbltmplannedoperation" ttmpo on tspps."sessionrecordid" = ttmpo."sessionrecordid")  left outer join      "theatrelive"."dbo"."tbltmactualoperation" ttmao on ttmpo."operationrecordid" = ttmao."operationrecordid")  left outer join      "theatrelive"."dbo"."cavaeadmissions" aeadm on ttmao."casenotenumber"=aeadm."crn" collate database_default      tspps."startdate" < dateadd(day, 2, aeadm."visit date")      , aeadm."visit date" < tspps."startdate"

i used distinct notice multiple rows operationrecordid appearing when more 1 episode number appears. tried adding select top 1 complete logic found in where clause need added ran exceptionally slowly.

the where clause there narrow down results set , looking dates within 2 days of each other.

essentially, each distinct operationrecordid need both latest episode number , if more 1 visit number appears, highest visit number also. both of these should able selected max clause.

thoughts?

the distinct keyword applies columns selected - not column appears before. sounds might need max function group or, depending on how complex criteria are, inline table.

something like

select      ttmpo."operationrecordid"     , ttmpo."casenotenumber"     , ttmpo."datetimebooked"     , tspps."startdate"     , aeadm."visit date"     , aeadm."visit time"     , max(aeadm."episode number")     , max(aeadm.[visit number])        (("theatrelive"."dbo"."tblspplannedsession" tspps  left outer join      "theatrelive"."dbo"."tbltmplannedoperation" ttmpo on tspps."sessionrecordid" = ttmpo."sessionrecordid")  left outer join      "theatrelive"."dbo"."tbltmactualoperation" ttmao on ttmpo."operationrecordid" = ttmao."operationrecordid")  left outer join      "theatrelive"."dbo"."cavaeadmissions" aeadm on ttmao."casenotenumber"=aeadm."crn" collate database_default      tspps."startdate" < dateadd(day, 2, aeadm."visit date")      , aeadm."visit date" < tspps."startdate"  group ttmpo."operationrecordid"     , ttmpo."casenotenumber"     , ttmpo."datetimebooked"     , tspps."startdate"     , aeadm."visit date"     , aeadm."visit time"

Comments

Post a Comment

Popular posts from this blog

android - Gradle sync Error:Configuration with name 'default' not found -

-
i want add external library guillotinemenu-android in application. followed steps given in upvoted answer existing question how add library project android studio? facing error when try build project after step 6 i.e. when added dependency in app/build.gradle compile project(":guillotinemenu") . tried stackoverflow links related error not working out. have made folder named libs in app directory , copied project folder guillotine menu there. here build.gradle(module:app) file apply plugin: 'com.android.application' android { compilesdkversion 22 buildtoolsversion "22.0.1" defaultconfig { applicationid "com.sunshine.bbreaker.appet_i" minsdkversion 14 targetsdkversion 22 versioncode 1 versionname "1.0" } buildtypes { release { minifyenabled false proguardfiles getdefaultproguardfile('proguard-android.txt'), 'proguard-rules.p
Read more

java - Andrioid studio start fail: Fatal error initializing 'null' -

-
i don't know happened android studio，when start ，it shows me error message below. , try uninstall/reinstall it,but doesn't work.who can me!!! internal error. please report https://code.google.com/p/android/issues java.lang.runtimeexception: com.intellij.ide.plugins.pluginmanager$startupabortedexception: fatal error initializing 'null' @ com.intellij.idea.ideaapplication.run(ideaapplication.java:178) @ com.intellij.idea.mainimpl$1$1$1.run(mainimpl.java:52) @ java.awt.event.invocationevent.dispatch(invocationevent.java:209) @ java.awt.eventqueue.dispatcheventimpl(eventqueue.java:715) @ java.awt.eventqueue.access$400(eventqueue.java:82) @ java.awt.eventqueue$2.run(eventqueue.java:676) @ java.awt.eventqueue$2.run(eventqueue.java:674) @ java.security.accesscontroller.doprivileged(native method) @ java.security.accesscontrolcontext$1.dointersectionprivilege(accesscontrolcontext.java:86) @ java.awt.eventqueue.dispatchevent(eventqu
Read more

html - jQuery UI Sortable - Remove placeholder after item is dropped -

-
i'm trying nested sortable , i'm kind'a succeeding, there's 1 small little inconvenience bugs me. i want placeholder disappear after i've dropped dragged item (on mouseup) , can't quite figure out how it. i want because when sort downwards, deletion of placeholder affects parent's height, in turn creates small bug, check jsfiddle here. html <div class="container"> <h1>menu</h1> <ul class="list-group striped nest"> <li class="list-group-item">home <ul class="list-group striped nest"></ul></li> <li class="list-group-item">about <ul class="list-group striped nest"></ul></li> <li class="list-group-item"> services <ul class="list-group striped nest"> <li class="list-group-item">design <ul class=
Read more