question

Hi All, Please share your thoughts on this critical scenario. when Dosage range between [100-199] or [500-599] or [900-999] then check value is NULL or blank for medicine value for a particular disease in medical table if it is blank or null then pull record in output For eg:- Bloodcount value for Malaria should not be null or blank when dosage is in the given range here 876 is not in range we are not checking value for Expiry in medical table IGNORE other Dosage in the Range of like [0-99] or [200 TO 399] OR [600 TO 899] Table Structure: DECLARE @DRIVER TABLE ( Patient_Disease VARCHAR (50), Medicine VARCHAR (50), Dosage VARCHAR (50) ) INSERT INTO @DRIVER SELECT 'Malaria', 'BloodCount', '100' UNION ALL SELECT 'diarrhea', 'Urine', '101' UNION ALL SELECT 'Cancer', 'Color Group','1500' UNION ALL SELECT 'AIDS', 'HIV', '500' union all SELECT 'Malaria', 'Expiry', '876' union all SELECT 'Malaria', 'Fever', '145' UNION ALL SELECT 'diarrhea', 'dehydration','156' DECLARE @Medical TABLE ( MRN VARCHAR (50), Patient_Disease VARCHAR (50), Description_1 VARCHAR (50) , Description_1_Value VARCHAR (50), Description_2 VARCHAR (50), Description_2_Value VARCHAR (50), Description_3 VARCHAR (50), Description_3_Value VARCHAR (50), Description_4 VARCHAR (50), Description_4_Value VARCHAR (50) ) INSERT @Medical SELECT 'AMONI65A','Malaria','BloodCount','','WBC','20000','PLATELATES','30M','Fever','102' UNION ALL--op SELECT 'AMONI65A','Malaria','BloodCount','11.2','WBC','20000','PLATELATES','','Fever','101' UNION ALL SELECT 'ZXSNI65A','Malaria','BloodCount','12.5','WBC','20000','PLATELATES','30M','Fever','96' union all SELECT 'KPSTA65A','Malaria','BloodCount','12.5','WBC','20000','PLATELATES','30M','Fever','' UNION ALL -- op SELECT 'KPTS396AZ','Diarrhea','nausea','>20','Urine','Yellow','dehydration','16gl','Fever','96' union all SELECT 'ZAFRD96AZ','Diarrhea','nausea','>20','Urine','Yellow','dehydration','16gl','Fever','' union all SELECT 'SNTPO5986','Diarrhea','nausea','>30.5','Urine','','dehydration','25GL','Fever','' UNION ALL -- op SELECT 'SNTPO5986','Diarrhea','nausea','>30.5','Urine','PALE','dehydration','25GL','Fever','' UNION ALL SELECT 'TPMS5T986','Diarrhea','nausea','>30.5','Urine','PALE','dehydration','','Fever','' UNION ALL -- OP SELECT 'LTMOHT986','Cancer','Color Group','','Urine','WHITE','WBC','20000','Fever','' UNION ALL SELECT 'KSTAPRSW','AIDS','RBC','','Urine','WHITE','WBC','20000','HIV','Negative' **I tried something like this** SELECT * FROM @DRIVER D INNER JOIN @Medical A ON A.Patient_Disease = D.Patient_Disease WHERE ( ( D.Patient_Disease = A.Description_1 AND (A.Description_1_Value IS NULL OR A.Description_1_Value = '') ) OR ( D.Patient_Disease = A.Description_2 AND (A.Description_2_Value IS NULL OR A.Description_2_Value = '') ) OR ( D.Patient_Disease = A.Description_3 AND (A.Description_3_Value IS NULL OR A.Description_3_Value = '') ) ) AND D.Dosage BETWEEN 100 AND 599 but unfortunately not giving expected Output. Expected Output 'AMONI65A','Malaria','BloodCount','','WBC','20000','PLATELATES','30M','Fever','102' 'KPSTA65A','Malaria','BloodCount','12.5','WBC','20000','PLATELATES','30M','Fever','' 'SNTPO5986','Diarrhea','nausea','>30.5','Urine','','dehydration','25GL','Fever','' 'TPMS5T986','Diarrhea','nausea','>30.5','Urine','PALE','dehydration','','Fever','' Thanks a lot