sql - Select minimum value from column A where column B is not in an array -

-

i'm trying select accesses patients d11.xblood minimum value grouped d11.xpid - , d11.xcaccess_type not 288, 289, or 292. (d11.xblood chronological index of accesses.)

  • d11.xpid: patient id (int)
  • d11.xblood: unique chronological index of patients' accesses (int)
  • d11.xcaccess_type: unique identifier accesses (int)

i want report 1 row each d11.xpid d11.xblood minimum (initial access) respective d11.xpid . moreover, want exclude row if initial access d11.xpid has d11.xcaccess_type value of 288, 289 or 292.

i have tried several variations of in select expert:

{d11.xblood} = minimum({d11.xblood},{d11.xpid}) , not ({d11.xcaccess_type} in [288, 289, 292])

this correctly selects rows initial access eliminates rows current access not in array. want eliminate rows initial access not in array. how can accomplish this?

sample table:

xpid   xblood   xcaccess_type ----   ------   ------------- 1       98       400 1       49       300 1       152      288 2       33       288 2       155      300 2       70       400 3       40       300 3       45       400

sample desired output:

xpid   xblood   xcaccess_type ----   ------   ------------- 1       49       300 3       40       300

see xpid = 2 not in output because minimum value of xblood had xcaccess_type = 288 excluded. see though xpid = 1 has xcaccess_type = 288, because there lower value of xblood xpid = 1 xcaccess_type not in (288,289,292) still included.

if don't want write stored procedure or custom sql handle this, add group. assuming deepest group (the 1 closest details section) sorting based on xpid, add group inside 1 sorts xcaccess_type lowest highest.

suppress header , footer new group add clause details section:

({d11.xpid} = previous({d11.xpid}) or ({d11.xcaccess_type} in [288, 289, 292])

this should modify report ever display records lowest access value per person. , if lowest access value 1 of 3 forbidden values, no records show xpid.


Comments

Post a Comment

Popular posts from this blog

Android volley - avoid multiple requests of the same kind to the server? -

-
i using volley upload list of images server. happens within service. once image uploaded remove list. problem arises when internet connection breaks. when internet connection breaks , comes again thinking of adding images yet uploaded request queue. this can result in more 1 copy of same image saved on server request same image might have gone through before getting rseponse. how can address scenario? i'm not quite sure why you're using service schedule requests volley automatically runs it's request in seperate thread. nevertheless can listen both request results, successful , unsuccessful adding listeners. you can remove image list schedule request using volley. if succeeds can carry on desired, if fails can add again list. public static request getimageuploadrequest(final string image) { response.listener<t> responselistener = new response.listener<t>() { @override public void onresponse(t response...
Read more

Combining PHP Registration and Login into one class with multiple functions in one PHP file -

-
i'm trying combine registration, activation , login scripts php website backend script front end developer can pass variables different forms to. my question whether appropriate approach this. don't want have lot of php files different pieces of application developing. far have written following 2 functions login, register , activate user front end developer can call: <?php /** * created phpstorm. * user: karl * date: 26/07/2016 * time: 02:25 */ class users { function register_user($email, $password, $user_name) { $server_name = "localhost"; $u_name = "root"; $db_password = "root"; $db_name = "betamath_graspe"; //email notification variable $from_address="info@slack.com"; //registration form $msg_reg_user='username taken. please choose different username'; $msg_reg_email='email registered'; $msg_reg_active=...
Read more

magento2 - Magento 2 admin grid add filter to collection -

-
i created grid in admin xml ui components. need filter collection via url parameter , dont know how achieve that. tried inject requestinterface collection, filter didnt work. di.xml <virtualtype name="slidelistingdataprovider" type="magento\framework\view\element\uicomponent\dataprovider\dataprovider"> <arguments> <argument name="collection" xsi:type="object" shared="false">xxx\xxx\model\resourcemodel\grid\slide\collection</argument> <argument name="filterpool" xsi:type="object" shared="false">slidelistingfilterpool</argument> <!-- define new object filters --> </arguments> </virtualtype> <virtualtype name="slidelistingfilterpool" type="magento\framework\view\element\uicomponent\dataprovider\filterpool"> <arguments> <argument name="applier...
Read more