Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CASE STATEMENT SQL 2005

Posted on 2009-12-30
4
Medium Priority
?
290 Views
Last Modified: 2012-05-08
In crystal reports, I had a formula like this:

if (
{cusASIISC_ImmunizationsGet.Category} = "Diphtheria, Tetanus, Pertussis" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90696" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90723" or
{cusASIISC_ImmunizationsGet.ImmunCode} = "90698"
) then
{cusASIISC_ImmunizationsGet.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
0
Comment
Question by:Jeff S
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
lof earned 1000 total points
ID: 26151115
most likely you want to use brackets there
condition1 and condition2 or condition3
is true when only condition3 is true
condition1 and (condition2 or condition3)
is true only when condition1 and one of the following two is true

in your SQL code you are missing ELSE part

i think your case should look like that:

SELECT

CASE WHEN .Sequence = 0
  AND (
                  Category = 'Diphtheria, Tetanus, Pertussis' OR
                  ImmunCode = '90696' OR
                  ImmunCode = '90723' OR
                  ImmunCode = '90698'
   )
                  THEN #temp.ImmunDte
   ELSE <somevaluehere>
        END AS DTPInjectionDate1

FROM #temp
0
 
LVL 7

Author Comment

by:Jeff S
ID: 26151236
Issue with this section of code:

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.

/*Immunization Report*/

SET NOCOUNT ON

SELECT
        *
INTO
        #temp
FROM
        (
          SELECT
                cipe.PatientProfileID,
                cipe.First,
                cipe.Middle,
                cipe.Last,
                cipe.Birthdate,
                cipe.Category,
                cipe.ImmunCode,
                cipe.ImmunDescription,
                cipe.ImmunDte,
                cipe.ProcCode,
                cipe.ProcCPTCode,
                cipe.ProcDescription,
                cipe.AgeYears,
                cipe.AgeMonths,
                cipe.ListOrder,
                cipe.PatientVisitID,
                cipe.TicketNumber,
                cipe.PatientVisitProcsId,
                cipe.ImmunID,
                cipe.cusImmunCodesID,
                cipe.Created,
                cipe.CreatedBy,
                cipe.LastModified,
                cipe.LastModifiedBy,
                cipe.Source,
                cipe.Last + ', ' + cipe.First + CASE WHEN cipe.middle IS NULL THEN +''
                                                     ELSE ' ' + cipe.middle
                                                END AS Name,
                pp.Address1,
                pp.Address2,
                pp.City,
                pp.State,
                pp.Zip,
                dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS GuarantorName,
                dbo.cusCalcAgeYearsMonths(AgeYears , AgeMonths) AS AgeAtImmun,
                Sequence = 0
          FROM
                cusvImmunPatExternal cipe 
          INNER JOIN PatientProfile pp ON cipe.PatientProfileID = pp.PatientProfileID 
          LEFT JOIN Guarantor g ON pp.GuarantorID = g.GuarantorID
          WHERE
                pp.PatientProfileId = '67'
          UNION
          SELECT
                cipv.PatientProfileID,
                cipv.First,
                cipv.Middle,
                cipv.Last,
                cipv.Birthdate,
                cipv.Category,
                cipv.ImmunCode,
                cipv.ImmunDescription,
                cipv.ImmunDte,
                cipv.ProcCode,
                cipv.ProcCPTCode,
                cipv.ProcDescription,
                cipv.AgeYears,
                cipv.AgeMonths,
                cipv.ListOrder,
                cipv.PatientVisitID,
                cipv.TicketNumber,
                cipv.PatientVisitProcsId,
                cipv.ImmunID,
                cipv.cusImmunCodesID,
                cipv.Created,
                cipv.CreatedBy,
                cipv.LastModified,
                cipv.LastModifiedBy,
                cipv.Source,
                cipv.Last + ', ' + cipv.First + CASE WHEN cipv.middle IS NULL THEN +''
                                                     ELSE ' ' + cipv.middle
                                                END AS Name,
                pp.Address1,
                pp.Address2,
                pp.City,
                pp.State,
                pp.Zip,
                dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS GuarantorName,
                dbo.cusCalcAgeYearsMonths(AgeYears , AgeMonths) AS AgeAtImmun,
                0
          FROM
                cusvImmunPatVisits cipv 
          INNER JOIN PatientProfile pp ON cipv.PatientProfileID = pp.PatientProfileID 
          LEFT JOIN Guarantor g ON pp.GuarantorID = g.GuarantorID
          WHERE
                pp.PatientProfileId = '67'
        ) t

UPDATE
        #temp
SET     
        sequence = (
                     SELECT
                        COUNT(*)
                     FROM
                        #temp t
                     WHERE
                        t.ImmunDte > t1.ImmunDte AND
                        t.PatientProfileId = t1.PatientProfileId AND
                        t.Category = t1.Category
                   )
FROM
        #temp t1

SELECT
        #temp.PatientProfileID,
        #temp.[First],
        #temp.Middle,
        #temp.[Last],
        #temp.Birthdate,
        #temp.Category,
        #temp.ImmunCode,
        #temp.ImmunDescription,
        #temp.ImmunDte,
        #temp.ProcCode,
        #temp.ProcCPTCode,
        #temp.ProcDescription,
        #temp.AgeYears,
        #temp.AgeMonths,
        #temp.ListOrder,
        #temp.PatientVisitID,
        #temp.TicketNumber,
        #temp.PatientVisitProcsId,
        #temp.ImmunID,
        #temp.cusImmunCodesID,
        #temp.Created,
        #temp.CreatedBy,
        #temp.LastModified,
        #temp.LastModifiedBy,
        #temp.Source,
        #temp.[Name],
        #temp.Address1,
        #temp.Address2,
        #temp.City,
        #temp.State,
        #temp.Zip,
        #temp.GuarantorName,
        #temp.AgeAtImmun,
        #temp.Sequence,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Measles, Mumps, Rubella' THEN #temp.ImmunDte
        END AS MMR#4,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Measles, Mumps, Rubella' THEN #temp.ImmunDte
        END AS MMR#3,
        CASE WHEN #temp.Sequence = 2 AND
                  #temp.Category = 'Measles, Mumps, Rubella' THEN #temp.ImmunDte
        END AS MMR#2,
        CASE WHEN #temp.Sequence = 3 AND
                  #temp.Category = 'Measles, Mumps, Rubella' THEN #temp.ImmunDte
        END AS MMR#1,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Hepatitis A' THEN #temp.ImmunDte
        END AS HEP_A#1,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Hepatitis A' THEN #temp.ImmunDte
        END AS HEP_A#2,
        CASE WHEN #temp.Sequence = 2 AND
                  #temp.Category = 'Human Papilloma Virus' THEN #temp.ImmunDte
        END AS HPV#1,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Human Papilloma Virus' THEN #temp.ImmunDte
        END AS HPV#2,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Human Papilloma Virus' THEN #temp.ImmunDte
        END AS HPV#3,
        CASE WHEN #temp.Sequence = 2 AND
                  #temp.Category = 'Rotavirus' THEN #temp.ImmunDte
        END AS Rotavirus#1,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Rotavirus' THEN #temp.ImmunDte
        END AS Rotavirus#2,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Rotavirus' THEN #temp.ImmunDte
        END AS Rotavirus#3,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.ImmunCode = '90733' THEN #temp.ImmunDte
        END AS Meningococcal#1,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Pneumococcal' THEN #temp.ImmunDte
        END AS Prevnar#4,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Pneumococcal' THEN #temp.ImmunDte
        END AS Prevnar#3,
        CASE WHEN #temp.Sequence = 2 AND
                  #temp.Category = 'Pneumococcal' THEN #temp.ImmunDte
        END AS Prevnar#2,
        CASE WHEN #temp.Sequence = 3 AND
                  #temp.Category = 'Pneumococcal' THEN #temp.ImmunDte
        END AS Prevnar#1,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.ImmunCode <> '99999' AND
                  #temp.Category = 'Varicella/Zoster' THEN #temp.ImmunDte
        END AS Varicella#2,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.ImmunCode <> '99999' AND
                  #temp.Category = 'Varicella/Zoster' THEN #temp.ImmunDte
        END AS Varicella#1,
        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
FROM
        #temp
WHERE
        Sequence < 5
DROP TABLE #temp

Open in new window

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 26151474
SELECT
        *
INTO
        #temp
FROM
        (
          SELECT
                cipe.PatientProfileID,
                cipe.First,
                cipe.Middle,
                cipe.Last,
                cipe.Birthdate,
                cipe.Category,
                cipe.ImmunCode,
                cipe.ImmunDescription,
                cipe.ImmunDte,
                cipe.ProcCode,
                cipe.ProcCPTCode,
                cipe.ProcDescription,
                cipe.AgeYears,
                cipe.AgeMonths,
                cipe.ListOrder,
                cipe.PatientVisitID,
                cipe.TicketNumber,
                cipe.PatientVisitProcsId,
                cipe.ImmunID,
                cipe.cusImmunCodesID,
                cipe.Created,
                cipe.CreatedBy,
                cipe.LastModified,
                cipe.LastModifiedBy,
                cipe.Source,
                cipe.Last + ', ' + cipe.First + CASE WHEN cipe.middle IS NULL THEN +''
                                                     ELSE ' ' + cipe.middle
                                                END AS Name,
                pp.Address1,
                pp.Address2,
                pp.City,
                pp.State,
                pp.Zip,
                dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS GuarantorName,
                dbo.cusCalcAgeYearsMonths(AgeYears , AgeMonths) AS AgeAtImmun,
                Sequence = 0
          FROM
                cusvImmunPatExternal cipe
          INNER JOIN PatientProfile pp ON cipe.PatientProfileID = pp.PatientProfileID
          LEFT JOIN Guarantor g ON pp.GuarantorID = g.GuarantorID
          WHERE
                pp.PatientProfileId = '67'
          UNION
          SELECT
                cipv.PatientProfileID,
                cipv.First,
                cipv.Middle,
                cipv.Last,
                cipv.Birthdate,
                cipv.Category,
                cipv.ImmunCode,
                cipv.ImmunDescription,
                cipv.ImmunDte,
                cipv.ProcCode,
                cipv.ProcCPTCode,
                cipv.ProcDescription,
                cipv.AgeYears,
                cipv.AgeMonths,
                cipv.ListOrder,
                cipv.PatientVisitID,
                cipv.TicketNumber,
                cipv.PatientVisitProcsId,
                cipv.ImmunID,
                cipv.cusImmunCodesID,
                cipv.Created,
                cipv.CreatedBy,
                cipv.LastModified,
                cipv.LastModifiedBy,
                cipv.Source,
                cipv.Last + ', ' + cipv.First + CASE WHEN cipv.middle IS NULL THEN +''
                                                     ELSE ' ' + cipv.middle
                                                END AS Name,
                pp.Address1,
                pp.Address2,
                pp.City,
                pp.State,
                pp.Zip,
                dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS GuarantorName,
                dbo.cusCalcAgeYearsMonths(AgeYears , AgeMonths) AS AgeAtImmun,
                0
          FROM
                cusvImmunPatVisits cipv
          INNER JOIN PatientProfile pp ON cipv.PatientProfileID = pp.PatientProfileID
          LEFT JOIN Guarantor g ON pp.GuarantorID = g.GuarantorID
          WHERE
                pp.PatientProfileId = '67'
        ) t

UPDATE
        #temp
SET    
        sequence = (
                     SELECT
                        COUNT(*)
                     FROM
                        #temp t
                     WHERE
                        t.ImmunDte > t1.ImmunDte AND
                        t.PatientProfileId = t1.PatientProfileId AND
                        t.Category = t1.Category
                   )
FROM
        #temp t1
       
---- add another column

ALTER TABLE #temp add  tmpfld bit
update #temp
set tmpfld = case when

                  (
                    Category = 'Diphtheria, Tetanus, Pertussis' OR
                    ImmunCode = '90696' OR
                    ImmunCode = '90723' OR
                    ImmunCode = '90698' ) THEN 1 else 0 end



SELECT
        #temp.PatientProfileID,
        #temp.[First],
        #temp.Middle,
        #temp.[Last],
        #temp.Birthdate,
        #temp.Category,
        #temp.ImmunCode,
        #temp.ImmunDescription,
        #temp.ImmunDte,
        #temp.ProcCode,
        #temp.ProcCPTCode,
        #temp.ProcDescription,
        #temp.AgeYears,
        #temp.AgeMonths,
        #temp.ListOrder,
        #temp.PatientVisitID,
        #temp.TicketNumber,
        #temp.PatientVisitProcsId,
        #temp.ImmunID,
        #temp.cusImmunCodesID,
        #temp.Created,
        #temp.CreatedBy,
        #temp.LastModified,
        #temp.LastModifiedBy,
        #temp.Source,
        #temp.[Name],
        #temp.Address1,
        #temp.Address2,
        #temp.City,
        #temp.State,
        #temp.Zip,
        #temp.GuarantorName,
        #temp.AgeAtImmun,
        #temp.Sequence,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Measles, Mumps, Rubella' THEN #temp.ImmunDte
        END AS MMR#4,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Measles, Mumps, Rubella' THEN #temp.ImmunDte
        END AS MMR#3,
        CASE WHEN #temp.Sequence = 2 AND
                  #temp.Category = 'Measles, Mumps, Rubella' THEN #temp.ImmunDte
        END AS MMR#2,
        CASE WHEN #temp.Sequence = 3 AND
                  #temp.Category = 'Measles, Mumps, Rubella' THEN #temp.ImmunDte
        END AS MMR#1,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Hepatitis A' THEN #temp.ImmunDte
        END AS HEP_A#1,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Hepatitis A' THEN #temp.ImmunDte
        END AS HEP_A#2,
        CASE WHEN #temp.Sequence = 2 AND
                  #temp.Category = 'Human Papilloma Virus' THEN #temp.ImmunDte
        END AS HPV#1,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Human Papilloma Virus' THEN #temp.ImmunDte
        END AS HPV#2,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Human Papilloma Virus' THEN #temp.ImmunDte
        END AS HPV#3,
        CASE WHEN #temp.Sequence = 2 AND
                  #temp.Category = 'Rotavirus' THEN #temp.ImmunDte
        END AS Rotavirus#1,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Rotavirus' THEN #temp.ImmunDte
        END AS Rotavirus#2,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Rotavirus' THEN #temp.ImmunDte
        END AS Rotavirus#3,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.ImmunCode = '90733' THEN #temp.ImmunDte
        END AS Meningococcal#1,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.Category = 'Pneumococcal' THEN #temp.ImmunDte
        END AS Prevnar#4,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.Category = 'Pneumococcal' THEN #temp.ImmunDte
        END AS Prevnar#3,
        CASE WHEN #temp.Sequence = 2 AND
                  #temp.Category = 'Pneumococcal' THEN #temp.ImmunDte
        END AS Prevnar#2,
        CASE WHEN #temp.Sequence = 3 AND
                  #temp.Category = 'Pneumococcal' THEN #temp.ImmunDte
        END AS Prevnar#1,
        CASE WHEN #temp.Sequence = 0 AND
                  #temp.ImmunCode <> '99999' AND
                  #temp.Category = 'Varicella/Zoster' THEN #temp.ImmunDte
        END AS Varicella#2,
        CASE WHEN #temp.Sequence = 1 AND
                  #temp.ImmunCode <> '99999' AND
                  #temp.Category = 'Varicella/Zoster' THEN #temp.ImmunDte
        END AS Varicella#1,
        CASE WHEN #temp.Sequence = 3 AND tmpfld = 1
                                           THEN #temp.ImmunDte
             ELSE ''
        END AS DTP#1,
        CASE WHEN #temp.Sequence = 2 AND tmpfld = 1
 THEN #temp.ImmunDte
             ELSE ''
        END AS DTP#2,
        CASE WHEN #temp.Sequence = 1 AND tmpfld = 1
 THEN #temp.ImmunDte
             ELSE ''
        END AS DTP#3,
        CASE WHEN #temp.Sequence = 0 AND tmpfld = 1
 THEN #temp.ImmunDte
             ELSE ''
        END AS DTP#4
FROM
        #temp
WHERE
        Sequence < 5
DROP TABLE #temp
0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31671470
Split points to be fair! THANKS!!!!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question