topic Re: Interesting Query - SQL Challenge ! in Application Development and Automation Discussions

Interesting Query - SQL Challenge !

samson_moses — Sun, 15 Mar 2020 02:15:01 GMT

Hi Experts,

In one of my project requirements , I came upon a challenging SQL scenario.. ( which I am still working on to get the best possible option ).

So I wanted to share this interesting query for others to try and see the approaches we can have . (Apologies but I am not able to share the DDL scripts ). We have the following tables for Symptoms Master(YSAM_SYMPTOMS), Sickness Master(YSAM_SICKNESS) which tells which all the symptoms for a sickness and Patient Test Results transaction table(YSAM_PAT_TESTS).

The task is to write a SQL query for "Getting the list of all Patient IDs and Patient Names having ALL THE symptoms of Corono Virus".

The solution should be in single open SQL only ( no ABAP or AMDP logic. Also the table YSAM_SYMPTOMS master table is for reference and its not needed in the Query ) . I have one solution for this but I don't think its that elegant. So request the community to share their thoughts on the SQL Query.

Note : My current system is SAP_ABAP - 7.50 ( so no CTEs also) with HANA as DB.

Regards,

Samson

Re: Interesting Query - SQL Challenge !

former_member188958 — Sun, 15 Mar 2020 05:01:29 GMT

While I can understand not being allowed to provide the full DDL and real data from the system, I'm not at all clear why you don't think you can provide simplified ddl and sample data for this query.

I've simplified it down a bit more. My system is SAP Adaptive Server Enterprise, so the TSQL may need a little tweaking for HANA.

create table YSAM_PAT_TESTS (PATIENTID int, SYMPTOMID int)

create table YSAM_SICKNESS (SICKNESSNAME varchar(20), SYMPTOMID int)

insert YSAM_PAT_TESTS values (1001, 01) insert YSAM_PAT_TESTS values (1001, 03)

insert YSAM_PAT_TESTS values (1002, 01) insert YSAM_PAT_TESTS values (1002, 02)
insert YSAM_PAT_TESTS values (1003, 01) insert YSAM_PAT_TESTS values (1003, 02)
insert YSAM_PAT_TESTS values (1003, 06) insert YSAM_PAT_TESTS values (1004, 02)
insert YSAM_PAT_TESTS values (1004, 04) insert YSAM_PAT_TESTS values (1004, 07)
insert YSAM_PAT_TESTS values (1005, 06) insert YSAM_PAT_TESTS values (1005, 07)
insert YSAM_SICKNESS values ("Common Flu", 01)
insert YSAM_SICKNESS values ("Corona Virus", 01)
insert YSAM_SICKNESS values ("Corona Virus", 02)

SELECT
p.PATIENTID
FROM YSAM_PAT_TESTS p, YSAM_SICKNESS s
WHERE p.SYMPTOMID = s.SYMPTOMID
AND s.SICKNESSNAME = "Corona Virus"
GROUP BY p.PATIENTID
HAVING count(*) = (
SELECT count(*)
FROM YSAM_SICKNESS
WHERE SICKNESSNAME = "Corona Virus" )

Re: Interesting Query - SQL Challenge !

venkateswaran_k — Sun, 15 Mar 2020 07:16:11 GMT

SELECT PATIENTID, PATIENTNAME,
  CASE WHEN SYMPTOMID = '01' THEN 'FEVER' ELSE '' END AS "FEVER",
  CASE WHEN SYMPTOMID = '02' THEN 'COLD'  ELSE '' END AS "COLD",
FROM
(SELECT PATIENTID, PATIENTNAME, SYMPTOMID FROM YSAM_PAT_TESTS  
      WHERE SYMPTOMID IN 
      (SELECT SYMPTOMID FROM YSAM_SICKNESS WHERE SICKNESSID = '02' )) AS Z
HAVING COUNT(*) = (SELECT COUNT(*) FROM YSAM_SICKNESS WHERE SICKNESSID = '02')

Re: Interesting Query - SQL Challenge !

samson_moses — Sun, 15 Mar 2020 18:13:57 GMT

Hi Venkat,

Thanks for the response.

I too had the same kind of solution ( as below ).

But somehow I wanted to avoid the sub-query on YSAM_SICKNESS being done twice. So I am going to keep the thread open for now to see other options.

Regards,

Samson

Re: Interesting Query - SQL Challenge !

samson_moses — Sun, 15 Mar 2020 18:18:15 GMT

Hi Brad,

Sincere apologies for not thinking of providing the simplified DDL. Also thanks for sharing the DDL and taking out your time for responding.

Your query would definitely work, but I wanted to avoid the referencing on YSAM_SICKNESS being done twice (either via JOIN or Sub-Query, like Venkat's response ) .

So I am going to keep the thread open for now to see other options.

Regards,

Samson.

Re: Interesting Query - SQL Challenge !

Tulasi_chinni — Mon, 19 Aug 2024 22:12:20 GMT

select p~patientid, p~patientname

from YSAM_PAT_TESTS AS p

join ysam_sickness as s

on p~symptomid = s~symptomid

and sicknessid = '02'

GROUP BY patientid , patientname

having count(*) = ( SELECT count(*)

FROM YSAM_SICKNESS

WHERE sicknessid = '02' )

into table @DATA(wlt_pat).

Re: Interesting Query - SQL Challenge !

JimSpath — Sat, 24 Aug 2024 23:03:53 GMT

Understanding this question was posted and answered during the beginning of COVID-19, we're in another wave so this query hit my search results. I have a similar algorithm, though not yet avoiding this hurdle:

avoid the referencing on YSAM_SICKNESS being done twice

Instead of a JOIN, I have nested select clauses. Entering column names instead of "*" in a count function makes your intent more clear.

having count(patientid) = ( select count(symptomid)

instead of:

having 
  count(*) = (
    select
      count(*)

select patientid, count(patientid), min(symptomid), max(symptomid) from ysam_pat_tests where symptomid in ( select symptomid from ysam_sickness where sicknessname = 'Corona Virus' ) group by patientid having count(patientid) = ( select count(symptomid) from ysam_sickness where sicknessname = 'Corona Virus' ) ;

results locally (not HANA):

select patientid, min(patientname) as nm, count(patientid), min(symptomid), max(symptomid) from ysam_pat_tests where symptomid in (select symptomid from ysam_sickness where sicknessname = 'Corona Virus') group by patientid having count(patientid) = (select count(symptomid) from ysam_sickness where sicknessname = 'Corona Virus'); patientid | nm | count | min | max -----------+----+-------+-----+----- 1002 | B | 2 | 1 | 2 1003 | C | 2 | 1 | 2 (2 rows)