Interesting Query - SQL Challenge !

[samson_moses]

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

[former_member188958]

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" )

[samson_moses]

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.

[venkateswaran_k]

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')

[samson_moses]

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

[Tulasi_chinni]

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).

[JimSpath]

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)