Jeff S
asked on
CASE STATEMENT SQL 2005
In crystal reports, I had a formula like this:
if (
{cusASIISC_ImmunizationsGe t.Category } = "Diphtheria, Tetanus, Pertussis" or
{cusASIISC_ImmunizationsGe t.ImmunCod e} = "90696" or
{cusASIISC_ImmunizationsGe t.ImmunCod e} = "90723" or
{cusASIISC_ImmunizationsGe t.ImmunCod e} = "90698"
) then
{cusASIISC_ImmunizationsGe t.ImmunDte } else
date(9999,9,9)
In SQL, I want to create the same as a field. Would this be the equivalent?
CASE WHEN #temp.Sequence = 0 AND
#temp.Category = 'Diphtheria, Tetanus, Pertussis' OR
#temp.ImmunCode = '90696' OR
#temp.ImmunCode = '90723' OR
#temp.ImmunCode = '90698'
THEN #temp.ImmunDte
END AS DTPInjectionDate1
if (
{cusASIISC_ImmunizationsGe
{cusASIISC_ImmunizationsGe
{cusASIISC_ImmunizationsGe
{cusASIISC_ImmunizationsGe
) then
{cusASIISC_ImmunizationsGe
date(9999,9,9)
In SQL, I want to create the same as a field. Would this be the equivalent?
CASE WHEN #temp.Sequence = 0 AND
#temp.Category = 'Diphtheria, Tetanus, Pertussis' OR
#temp.ImmunCode = '90696' OR
#temp.ImmunCode = '90723' OR
#temp.ImmunCode = '90698'
THEN #temp.ImmunDte
END AS DTPInjectionDate1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Split points to be fair! THANKS!!!!
ASKER
CASE WHEN #temp.Sequence = 3 AND
(
#temp.Category = 'Diphtheria, Tetanus, Pertussis' OR
#temp.ImmunCode = '90696' OR
#temp.ImmunCode = '90723' OR
#temp.ImmunCode = '90698' ) THEN #temp.ImmunDte
ELSE ''
END AS DTP#1,
CASE WHEN #temp.Sequence = 2 AND
(
#temp.Category = 'Diphtheria, Tetanus, Pertussis' OR
#temp.ImmunCode = '90696' OR
#temp.ImmunCode = '90723' OR
#temp.ImmunCode = '90698' ) THEN #temp.ImmunDte
ELSE ''
END AS DTP#2,
CASE WHEN #temp.Sequence = 1 AND
(
#temp.Category = 'Diphtheria, Tetanus, Pertussis' OR
#temp.ImmunCode = '90696' OR
#temp.ImmunCode = '90723' OR
#temp.ImmunCode = '90698' ) THEN #temp.ImmunDte
ELSE ''
END AS DTP#3,
CASE WHEN #temp.Sequence = 0 AND
(
#temp.Category = 'Diphtheria, Tetanus, Pertussis' OR
#temp.ImmunCode = '90696' OR
#temp.ImmunCode = '90723' OR
#temp.ImmunCode = '90698' ) THEN #temp.ImmunDte
ELSE ''
END AS DTP#4
A patient can have 1 to 4 injection Dates the client needs populated on a form. I use the Sequence to tell me which one occurred first. In my demo db I entered in 2 injections on two distinct dates. Because there was only 2 done my logic is flawed.
I need a way to check if the other case statements have a value and if null, move it down from the other. So in this scenario, I only have 2 injection dates, not 4, therefore whats printing in DTP#3 and DTP#4 need to move down to DTP#1 and DTP#2.
Full SQL below.
Open in new window