sql - MySQL Select only one row from each patient diagnosed according the the first date -


edit

i cannot group by patient_id, error of sql_mode=only_full_group_by...

i have following query helping me list of patient has been diagnosed diabetes:

select t1.patient_id, convert(aes_decrypt(t4.patient_name_en, 'key1') using utf8mb4) patient_name_en, t3.date_of_visit date_of_visit,  t2.diagnosis_name, max(ifnull(t5.date_of_assessment, 'n/a')) date_of_assessment, ifnull(t5.assessment_result, 0) assessment_result  consultation t1 left join diagnosis t2 on t1.diagnosis_id = t2.diagnosis_id left join visit t3 on t3.visit_id = t1.visit_id left join patient t4 on t4.patient_id = t3.patient_id left join diabetes_assessment t5 on t5.patient_id = t4.patient_id t2.diagnosis_name '%diabetes%' , t1.clinic_id = '361' , t3.visit_status="active" group t1.patient_id, t5.date_of_assessment, t4.patient_name_en, t3.date_of_visit, t2.diagnosis_name, t5.assessment_result order t5.date_of_assessment desc

the result is:

enter image description here

as see if patient diagnosed multiple times different types or level of diabetes, rows shown. want first time diagnosed it, result these 2 rows highlighted in blue:

enter image description here

here patient 0361 first diagnised diabetes in 2017-04-06. row patient.

i tried use `min(t3.date_of_visit) didn't worked properly.

i think want first visit , last diabetes_assessment. have assumed first field in tables auto_increment field , consultation_id in fiddle incorrectly typed.

given above

mariadb [sandbox]> select p.patient_name_en,v.*,c.diagnosis_id,d.diagnosis_name,da.date_of_assessment,da.assessment_result     -> visit v     -> join patient p on p.patient_id = v.patient_id     -> join consultation c on c.patient_id = v.patient_id , c.visit_id = v.visit_id     -> join diagnosis d on d.diagnosis_id = c.diagnosis_id     -> left join     -> (     -> select da.patient_id, da.date_of_assessment,da.assessment_result     -> diabetes_assessment da     -> da.diabetes_assessment_id  = (select max(da1.diabetes_assessment_id) diabetes_assessment da1 da1.patient_id = da.patient_id)     -> ) da on da.patient_id = v.patient_id     -> v.visit_id = (select min(visit_id) consultation c c.patient_id = v.patient_id)     -> , c.diagnosis_id in (1,2)     -> , v.clinic_id = 361     -> ; +-----------------+----------+------------+-----------+---------------+--------------+--------------+---------------------------------------------+--------------------+-------------------+ | patient_name_en | visit_id | patient_id | clinic_id | date_of_visit | visit_status | diagnosis_id | diagnosis_name                              | date_of_assessment | assessment_result | +-----------------+----------+------------+-----------+---------------+--------------+--------------+---------------------------------------------+--------------------+-------------------+ | abc             |        1 | 361-9001   |       361 | 2017-03-03    | active       |            1 | diabetes mellitus diabetic nephropathy | 2017-05-05         |             40.00 | | xyz             |        3 | 361-0361   |       361 | 2017-10-03    | active       |            2 | e01 diabetes mellitus kidney disease   | 2017-03-10         |             30.50 | +-----------------+----------+------------+-----------+---------------+--------------+--------------+---------------------------------------------+--------------------+-------------------+ 2 rows in set (0.00 sec)

Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more