troubleshooting Question

SQL 2000 - Returned result pulling date wrong

Avatar of Jeff S
Jeff SFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
6 Comments2 Solutions151 ViewsLast Modified:
Please note, the SQL is handled dynamically by the SQL server, therefore some of the coding will look odd to you. Please disregard as this is not the issue.

What I need help with is this specific section:

          'Insured1DOB' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.BirthDate , '')
                               WHEN (
                                      pi.InsuredSameAsGuarantor = 1
                                      AND pp.PatientSameAsGuarantor = 1
                                    ) THEN ISNULL(pp.BirthDate , '')
                               WHEN (
                                      pi.InsuredSameAsGuarantor = 1
                                      AND pp.PatientSameAsGuarantor = 0
                                    ) THEN ISNULL(g.BirthDate , '')
                               ELSE ISNULL(pi.BirthDate , '')
                          END ,

Currently, if the patient does not have insurance, I am getting back the following:

1900-01-01 00:00:00.000

What I would like to see is nothing. If NULL then ''

Any help is appreciated.
SET NOCOUNT ON
 
SELECT
    	'PatientName' = ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , '') + ' ' + ISNULL(pp.Last , '') + ' ' + ISNULL(pp.Suffix , '') ,
    	'PatLast' = ISNULL(pp.Last , '') ,
    	'PatFirst' = ISNULL(pp.First , '') ,
    	'PatMiddle' = ISNULL(pp.Middle , '') ,
    	'PatientAddr1' = ISNULL(pp.Address1 , '') ,
    	'PatientAddr2' = ISNULL(pp.Address2 , '') ,
    	'PatientCity' = ISNULL(pp.City , '') ,
    	'PatientState' = ISNULL(pp.State , '') ,
    	'PatientZip' = ISNULL(pp.Zip , '') ,
    	'PatientCountry' = ISNULL(pp.Country , '') ,
    	'PatientBirthdate' = pp.Birthdate ,
    	'PatientSex' = CASE WHEN pp.Sex = 'M' THEN 'Male'
    	                    WHEN pp.Sex = 'F' THEN 'Female'
    	                    ELSE ISNULL(pp.Sex , '')
    	               END ,
    	'PatientPhone1' = ISNULL(pp.Phone1 , '') ,
    	'PatientPhone1Type' = ISNULL(pp.Phone1Type , '') ,
    	'PatientPhone2' = ISNULL(pp.Phone2 , '') ,
    	'PatientPhone2Type' = ISNULL(pp.Phone2Type , '') ,
    	'PatientSSN' = ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , '') ,
    	'GuarantorName' = ISNULL(g.First , '') + ' ' + ISNULL(g.Middle , '') + ' ' + ISNULL(g.Last , '') ,
    	'GuarantorAddr1' = ISNULL(g.Address1 , '') ,
    	'GuarantorAddr2' = ISNULL(g.Address2 , '') ,
    	'GuarantorCity' = ISNULL(g.City , '') ,
    	'GuarantorState' = ISNULL(g.State , '') ,
    	'GuarantorZip' = ISNULL(g.Zip , '') ,
    	'GuarantorCountry' = ISNULL(g.Country , '') ,
    	'GuarantorPhone1' = ISNULL(g.Phone1 , '') ,
    	'GuarantorPhone1Type' = ISNULL(g.Phone1Type , '') ,
    	'GuarantorPhone2' = ISNULL(g.Phone2 , '') ,
    	'GuarantorPhone2Type' = ISNULL(g.Phone2Type , '') ,
    	'GuarantorBirthdate' = g.birthdate ,
    	'GuarantorSSN' = ISNULL(SUBSTRING(g.SSN , 1 , 3) + '-' + SUBSTRING(g.SSN , 4 , 2) + '-' + SUBSTRING(g.SSN , 6 , 4) , '') ,
    	'Doctor' = ISNULL(df.First , '') + ' ' + ISNULL(df.Middle , '') + ' ' + ISNULL(df.Last , '') + ' ' + ISNULL(df.Suffix , '') ,
    	'DoctorFirst' = ISNULL(df.First , '') ,
    	'DoctorMiddle' = ISNULL(df.Middle , '') ,
    	'DoctorLast' = ISNULL(df.Last , '') ,
    	'DoctorSuffix' = ISNULL(df.Suffix , '') ,
    	'AppointmentsID' = a.AppointmentsID ,
    	'ApptStart' = a.ApptStart ,
    	'StartHour' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(hour , a.ApptStart)) , '') ,
    	'StartMinute' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(minute , a.ApptStart)) , '') ,
    	'StopHour' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(hour , a.ApptStop)) , '') ,
    	'StopMinute' = ISNULL(CONVERT(VARCHAR(2) , DATEPART(minute , a.ApptStop)) , '') ,
    	'ApptTime' = DATEDIFF(minute , a.ApptStart , a.ApptStop) ,
    	'AppointmentType' = ISNULL(at.Name , '') ,
    	'ApptNotes' = ISNULL(CONVERT(VARCHAR(255) , a.Notes) , '') ,
    	'Carrier1Name' = ISNULL(ic.Name , '') ,
    	'Carrier1ListName' = ISNULL(ic.Listname , '') ,
    	'Carrier1Address1' = ISNULL(ic.Address1 , '') ,
    	'Carrier1Address2' = ISNULL(ic.Address2 , '') ,
    	'Carrier1City' = ISNULL(ic.City , '') ,
    	'Carrier1State' = ISNULL(ic.State , '') ,
    	'Carrier1Zip' = ISNULL(ic.Zip , '') ,
    	'Carrier1Country' = ISNULL(ic.Country , '') ,
    	'Carrier1Contact' = ISNULL(ic.Contact , '') ,
    	'Carrier1Phone' = ISNULL(dbo.formatphone(ic.phone1 , 1) , '') ,
    	'Carrier1FinancialClass' = ISNULL(ml.Description , '') ,
    	'Carrier1AllocationType' = ISNULL(al.Name , '') ,
    	'Carrier1CarrierType' = ISNULL(ml3.Description , '') ,
    	'Carrier1PolicyType' = ISNULL(ml4.Description , '') ,
    	'Carrier1GroupId' = ISNULL(ic.GroupId , '') ,
    	'Carrier1GroupName' = ISNULL(ic.GroupName , '') ,
    	'Insured1SameAsPatient' = ISNULL(pi.InsuredSameAsPatient , 0) ,
    	'Insured1SameAsGuarantor' = ISNULL(pi.InsuredSameAsGuarantor , 0) ,
    	'Insured1PatRelToInsured' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN 'Same as Patient'
    	                                 WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN 'Same as Guarantor'
    	                                 ELSE ISNULL(ml2.Description , '')
    	                            END ,
    	'Insured1First' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.First , '')
    	                       WHEN (
    	                              pi.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 1
    	                            ) THEN ISNULL(pp.First , '')
    	                       WHEN (
    	                              pi.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 0
    	                            ) THEN ISNULL(g.First , '')
    	                       ELSE ISNULL(pi.First , '')
    	                  END ,
    	'Insured1Last' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.Last , '')
    	                      WHEN (
    	                             pi.InsuredSameAsGuarantor = 1
    	                             AND pp.PatientSameAsGuarantor = 1
    	                           ) THEN ISNULL(pp.Last , '')
    	                      WHEN (
    	                             pi.InsuredSameAsGuarantor = 1
    	                             AND pp.PatientSameAsGuarantor = 0
    	                           ) THEN ISNULL(g.Last , '')
    	                      ELSE ISNULL(pi.Last , '')
    	                 END ,
    	'Insured1Middle' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.Middle , '')
    	                        WHEN (
    	                               pi.InsuredSameAsGuarantor = 1
    	                               AND pp.PatientSameAsGuarantor = 1
    	                             ) THEN ISNULL(pp.Middle , '')
    	                        WHEN (
    	                               pi.InsuredSameAsGuarantor = 1
    	                               AND pp.PatientSameAsGuarantor = 0
    	                             ) THEN ISNULL(g.Middle , '')
    	                        ELSE ISNULL(pi.Middle , '')
    	                   END ,
    	'Insured1Address1' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.Address1 , '')
    	                          WHEN (
    	                                 pi.InsuredSameAsGuarantor = 1
    	                                 AND pp.PatientSameAsGuarantor = 1
    	                               ) THEN ISNULL(pp.Address1 , '')
    	                          WHEN (
    	                                 pi.InsuredSameAsGuarantor = 1
    	                                 AND pp.PatientSameAsGuarantor = 0
    	                               ) THEN ISNULL(g.Address1 , '')
    	                          ELSE ISNULL(pi.Address1 , '')
    	                     END ,
    	'Insured1Address2' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.Address2 , '')
    	                          WHEN (
    	                                 pi.InsuredSameAsGuarantor = 1
    	                                 AND pp.PatientSameAsGuarantor = 1
    	                               ) THEN ISNULL(pp.Address2 , '')
    	                          WHEN (
    	                                 pi.InsuredSameAsGuarantor = 1
    	                                 AND pp.PatientSameAsGuarantor = 0
    	                               ) THEN ISNULL(g.Address2 , '')
    	                          ELSE ISNULL(pi.Address2 , '')
    	                     END ,
    	'Insured1City' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.City , '')
    	                      WHEN (
    	                             pi.InsuredSameAsGuarantor = 1
    	                             AND pp.PatientSameAsGuarantor = 1
    	                           ) THEN ISNULL(pp.City , '')
    	                      WHEN (
    	                             pi.InsuredSameAsGuarantor = 1
    	                             AND pp.PatientSameAsGuarantor = 0
    	                           ) THEN ISNULL(g.City , '')
    	                      ELSE ISNULL(pi.City , '')
    	                 END ,
    	'Insured1State' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.State , '')
    	                       WHEN (
    	                              pi.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 1
    	                            ) THEN ISNULL(pp.State , '')
    	                       WHEN (
    	                              pi.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 0
    	                            ) THEN ISNULL(g.State , '')
    	                       ELSE ISNULL(pi.State , '')
    	                  END ,
    	'Insured1Zip' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.Zip , '')
    	                     WHEN (
    	                            pi.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 1
    	                          ) THEN ISNULL(pp.Zip , '')
    	                     WHEN (
    	                            pi.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 0
    	                          ) THEN ISNULL(g.Zip , '')
    	                     ELSE ISNULL(pi.Zip , '')
    	                END ,
    	'Insured1Country' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.Country , '')
    	                         WHEN (
    	                                pi.InsuredSameAsGuarantor = 1
    	                                AND pp.PatientSameAsGuarantor = 1
    	                              ) THEN ISNULL(pp.Country , '')
    	                         WHEN (
    	                                pi.InsuredSameAsGuarantor = 1
    	                                AND pp.PatientSameAsGuarantor = 0
    	                              ) THEN ISNULL(g.Country , '')
    	                         ELSE ISNULL(pi.Country , '')
    	                    END ,
    	'Insured1Phone' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(dbo.formatphone(pp.phone1 , 1) , '')
    	                       WHEN (
    	                              pi.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 1
    	                            ) THEN ISNULL(dbo.formatphone(pp.phone1 , 1) , '')
    	                       WHEN (
    	                              pi.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 0
    	                            ) THEN ISNULL(dbo.formatphone(g.phone1 , 1) , '')
    	                       ELSE ISNULL(dbo.formatphone(pi.phone1 , 1) , '')
    	                  END ,
    	'Insured1ID' = ISNULL(pi.InsuredID , '') ,
    	'Insured1Group' = ISNULL(PI.groupid , '') ,
    	'Ins1_EffDate' = CASE WHEN pi.InsCardEffectiveDate IS NULL THEN ''
    	                      ELSE CONVERT(VARCHAR , pi.InsCardEffectiveDate , 101)
    	                 END ,
    	'Insured1SSN' = CASE WHEN pi.InsuredSameAsPatient = 1
    	                     THEN ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , '')
    	                     WHEN (
    	                            pi.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 1
    	                          ) THEN ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , '')
    	                     WHEN (
    	                            pi.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 0
    	                          ) THEN ISNULL(SUBSTRING(g.SSN , 1 , 3) + '-' + SUBSTRING(g.SSN , 4 , 2) + '-' + SUBSTRING(g.SSN , 6 , 4) , '')
    	                     ELSE ISNULL(SUBSTRING(pi.SSN , 1 , 3) + '-' + SUBSTRING(pi.SSN , 4 , 2) + '-' + SUBSTRING(pi.SSN , 6 , 4) , '')
    	                END ,
    	'Insured1DOB' = CASE WHEN pi.InsuredSameAsPatient = 1 THEN ISNULL(pp.BirthDate , '')
    	                     WHEN (
    	                            pi.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 1
    	                          ) THEN ISNULL(pp.BirthDate , '')
    	                     WHEN (
                	                pi.InsuredSameAsGuarantor = 1
                	                AND pp.PatientSameAsGuarantor = 0
                	              ) THEN ISNULL(g.BirthDate , '')
                	         ELSE ISNULL(pi.BirthDate , '')
                	    END ,
    	'Insured1Employer' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN ISNULL(e.Name , '')
                	              WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN ISNULL(e2.Name , '')
                	              ELSE ISNULL(e3.NAME , '')
                	         END ,
    	'Insured1Occupation' = CASE WHEN COALESCE(pi.InsuredSameAsPatient , 1) = 1 THEN ISNULL(pp.EmpOccup , '')
    	                            WHEN COALESCE(pi.InsuredSameAsGuarantor , 1) = 1 THEN ISNULL(g.EmpOccup , '')
    	                            ELSE ISNULL(pi.EmpOccup , '')
    	                       END ,
    	'Carrier2Name' = ISNULL(ic2.Name , '') ,
    	'Carrier2ListName' = ISNULL(ic2.ListName , '') ,
    	'Carrier2Address1' = ISNULL(ic2.Address1 , '') ,
    	'Carrier2Address2' = ISNULL(ic2.Address2 , '') ,
    	'Carrier2City' = ISNULL(ic2.City , '') ,
    	'Carrier2State' = ISNULL(ic2.State , '') ,
    	'Carrier2Zip' = ISNULL(ic2.Zip , '') ,
    	'Carrier2Country' = ISNULL(ic2.Country , '') ,
    	'Carrier2Contact' = ISNULL(ic2.Contact , '') ,
    	'Carrier2Phone' = ISNULL(dbo.formatphone(ic2.phone1 , 1) , '') ,
    	'Carrier2GroupId' = ISNULL(ic2.GroupId , '') ,
    	'Carrier2GroupName' = ISNULL(ic2.GroupName , '') ,
    	'Insured2SameAsPatient' = ISNULL(pi2.InsuredSameAsPatient , 0) ,
    	'Insured2SameAsGuarantor' = ISNULL(pi2.InsuredSameAsGuarantor , 0) ,
    	'Insured2PatRelToInsured' = CASE WHEN COALESCE(pi2.InsuredSameAsPatient , 1) = 1 THEN 'Same as Patient'
    	                                 WHEN COALESCE(pi2.InsuredSameAsGuarantor , 1) = 1 THEN 'Same as Guarantor'
    	                                 ELSE ISNULL(ml5.Description , '')
    	                            END ,
    	'Insured2First' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.First , '')
    	                       WHEN (
    	                              pi2.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 1
    	                            ) THEN ISNULL(pp.First , '')
    	                       WHEN (
    	                              pi2.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 0
    	                            ) THEN ISNULL(g.First , '')
    	                       ELSE ISNULL(pi2.First , '')
    	                  END ,
    	'Insured2Last' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.Last , '')
    	                      WHEN (
    	                             pi2.InsuredSameAsGuarantor = 1
    	                             AND pp.PatientSameAsGuarantor = 1
    	                           ) THEN ISNULL(pp.Last , '')
    	                      WHEN (
    	                             pi2.InsuredSameAsGuarantor = 1
    	                             AND pp.PatientSameAsGuarantor = 0
    	                           ) THEN ISNULL(g.Last , '')
    	                      ELSE ISNULL(pi2.Last , '')
    	                 END ,
    	'Insured2Middle' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.Middle , '')
    	                        WHEN (
    	                               pi2.InsuredSameAsGuarantor = 1
    	                               AND pp.PatientSameAsGuarantor = 1
    	                             ) THEN ISNULL(pp.Middle , '')
    	                        WHEN (
    	                               pi2.InsuredSameAsGuarantor = 1
    	                               AND pp.PatientSameAsGuarantor = 0
    	                             ) THEN ISNULL(g.Middle , '')
    	                        ELSE ISNULL(pi2.Middle , '')
    	                   END ,
    	'Insured2Address1' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.Address1 , '')
    	                          WHEN (
    	                                 pi2.InsuredSameAsGuarantor = 1
    	                                 AND pp.PatientSameAsGuarantor = 1
    	                               ) THEN ISNULL(pp.Address1 , '')
    	                          WHEN (
    	                                 pi2.InsuredSameAsGuarantor = 1
    	                                 AND pp.PatientSameAsGuarantor = 0
    	                               ) THEN ISNULL(g.Address1 , '')
    	                          ELSE ISNULL(pi2.Address1 , '')
    	                     END ,
    	'Insured2Address2' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.Address2 , '')
    	                          WHEN (
    	                                 pi2.InsuredSameAsGuarantor = 1
    	                                 AND pp.PatientSameAsGuarantor = 1
    	                               ) THEN ISNULL(pp.Address2 , '')
    	                          WHEN (
    	                                 pi2.InsuredSameAsGuarantor = 1
    	                                 AND pp.PatientSameAsGuarantor = 0
    	                               ) THEN ISNULL(g.Address2 , '')
    	                          ELSE ISNULL(pi2.Address2 , '')
    	                     END ,
    	'Insured2City' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.City , '')
    	                      WHEN (
    	                             pi2.InsuredSameAsGuarantor = 1
    	                             AND pp.PatientSameAsGuarantor = 1
    	                           ) THEN ISNULL(pp.City , '')
    	                      WHEN (
    	                             pi2.InsuredSameAsGuarantor = 1
    	                             AND pp.PatientSameAsGuarantor = 0
    	                           ) THEN ISNULL(g.City , '')
    	                      ELSE ISNULL(pi2.City , '')
    	                 END ,
    	'Insured2State' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.State , '')
    	                       WHEN (
    	                              pi2.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 1
    	                            ) THEN ISNULL(pp.State , '')
    	                       WHEN (
    	                              pi2.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 0
    	                            ) THEN ISNULL(g.State , '')
    	                       ELSE ISNULL(pi2.State , '')
    	                  END ,
    	'Insured2Zip' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.Zip , '')
    	                     WHEN (
    	                            pi2.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 1
    	                          ) THEN ISNULL(pp.Zip , '')
    	                     WHEN (
    	                            pi2.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 0
    	                          ) THEN ISNULL(g.Zip , '')
    	                     ELSE ISNULL(pi2.Zip , '')
    	                END ,
    	'Insured2Country' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.Country , '')
    	                         WHEN (
    	                                pi2.InsuredSameAsGuarantor = 1
    	                                AND pp.PatientSameAsGuarantor = 1
    	                              ) THEN ISNULL(pp.Country , '')
    	                         WHEN (
    	                                pi2.InsuredSameAsGuarantor = 1
    	                                AND pp.PatientSameAsGuarantor = 0
    	                              ) THEN ISNULL(g.Country , '')
    	                         ELSE ISNULL(pi2.Country , '')
    	                    END ,
    	'Insured2Phone' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(dbo.formatphone(pp.phone1 , 1) , '')
    	                       WHEN (
    	                              pi2.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 1
    	                            ) THEN ISNULL(dbo.formatphone(pp.phone1 , 1) , '')
    	                       WHEN (
    	                              pi2.InsuredSameAsGuarantor = 1
    	                              AND pp.PatientSameAsGuarantor = 0
    	                            ) THEN ISNULL(dbo.formatphone(g.phone1 , 1) , '')
    	                       ELSE ISNULL(dbo.formatphone(pi2.phone1 , 1) , '')
    	                  END ,
    	'Insured2ID' = ISNULL(pi2.InsuredID , '') ,
    	'Insured2Group' = ISNULL(pi2.groupid , '') ,
    	'Ins2_EffDate' = CASE WHEN pi2.InsCardEffectiveDate IS NULL THEN ''
    	                      ELSE CONVERT(VARCHAR , pi2.InsCardEffectiveDate , 101)
    	                 END ,
    	'Insured2SSN' = CASE WHEN pi2.InsuredSameAsPatient = 1
    	                     THEN ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , '')
    	                     WHEN (
    	                            pi2.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 1
    	                          ) THEN ISNULL(SUBSTRING(pp.SSN , 1 , 3) + '-' + SUBSTRING(pp.SSN , 4 , 2) + '-' + SUBSTRING(pp.SSN , 6 , 4) , '')
    	                     WHEN (
    	                            pi2.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 0
    	                          ) THEN ISNULL(SUBSTRING(g.SSN , 1 , 3) + '-' + SUBSTRING(g.SSN , 4 , 2) + '-' + SUBSTRING(g.SSN , 6 , 4) , '')
    	                     ELSE ISNULL(SUBSTRING(pi2.SSN , 1 , 3) + '-' + SUBSTRING(pi2.SSN , 4 , 2) + '-' + SUBSTRING(pi2.SSN , 6 , 4) , '')
    	                END ,
    	'Insured2DOB' = CASE WHEN pi2.InsuredSameAsPatient = 1 THEN ISNULL(pp.BirthDate , '')
    	                     WHEN (
    	                            pi2.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 1
    	                          ) THEN ISNULL(pp.BirthDate , '')
    	                     WHEN (
    	                            pi2.InsuredSameAsGuarantor = 1
    	                            AND pp.PatientSameAsGuarantor = 0
    	                          ) THEN ISNULL(g.BirthDate , '')
    	                     ELSE ISNULL(pi2.BirthDate , '')
    	                END ,
    	'Operating Doctor' = ISNULL(od.Listname , '') ,
    	'Other Physiscian' = ISNULL(oth.ListName , '') ,
    	'DateOfInjury' = c.DateOfInjury ,
    	'ClaimNumber' = ISNULL(c.ClaimNumber , '') ,
    	'Contact1First' = ISNULL(pc.First , '') ,
    	'Contact1Middle' = ISNULL(pc.Middle , '') ,
    	'Contact1Last' = ISNULL(pc.Last , '')
 
FROM	Appointments a
    	LEFT JOIN ApptChain ac ON a.ApptChainId = ac.ApptChainId
    	LEFT JOIN ApptSet aset ON a.ApptSetId = aset.ApptSetId
    	JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
    	JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
    	JOIN Guarantor g ON pp.GuarantorID = g.GuarantorId
    	JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
    	LEFT JOIN DoctorFacility df ON a.DoctorId = df.DoctorFacilityId
    	JOIN CasesInsurance ci ON ISNULL(a.CasesId , 0) = ISNULL(ci.CasesId , 0)
    	                          AND ci.PatientProfileId = pp.PatientProfileId
    	                          AND ci.OrderForClaims = 1
    	LEFT JOIN InsuranceCarriers ic ON ci.InsuranceCarriersId = ic.InsuranceCarriersId
    	LEFT JOIN PatientInsurance pi ON ci.PatientInsuranceId = pi.PatientInsuranceId
    	LEFT JOIN Medlists ml ON ic.FinancialClassMid = ml.MedListsId
    	LEFT JOIN AllocationSet al ON ic.AllocationSetid = al.AllocationSetid
    	LEFT JOIN Medlists ml3 ON ic.CarrierTypeMid = ml3.MedListsId
    	LEFT JOIN Medlists ml4 ON ic.PolicyTypeMid = ml4.MedListsId
    	LEFT JOIN Medlists ml2 ON pi.PatRelToInsuredMID = ml2.MedListsId
    	LEFT JOIN Cases c ON ci.CasesId = c.CasesId
    	                     AND ISNULL(c.WorkersComp , 0) <> 0
    	LEFT JOIN Employer e ON (
    	                          c.EmployerId = e.EmployerId
    	                          OR (
    	                               c.EmployerId IS NULL
    	                               AND pp.EmployerId = e.EmployerId
    	                             )
    	                        )
    	LEFT JOIN Employer e2 ON (
    	                           c.EmployerId = e2.EmployerId
    	                           OR (
    	                                c.EmployerId IS NULL
    	                                AND g.EmployerId = e2.EmployerId
    	                              )
    	                         )
    	LEFT JOIN Employer e3 ON (
    	                           c.EmployerId = e3.EmployerId
    	                           OR (
    	                                c.EmployerId IS NULL
    	                                AND pi.EmployerId = e3.EmployerId
    	                              )
    	                         )
    	LEFT JOIN CasesInsurance ci2 ON ISNULL(a.CasesId , 0) = ISNULL(ci2.CasesId , 0)
    	                                AND ci2.PatientProfileId = pp.PatientProfileId
    	                                AND ci2.OrderForClaims = 2
    	LEFT JOIN InsuranceCarriers ic2 ON ci2.InsuranceCarriersId = ic2.InsuranceCarriersId
    	LEFT JOIN PatientInsurance pi2 ON ci2.PatientInsuranceId = pi2.PatientInsuranceId
    	LEFT JOIN Medlists ml5 ON pi2.PatRelToInsuredMID = ml5.MedListsId
    	LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
    	LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
    	LEFT JOIN PatientVisit pv ON a.PatientVisitId = pv.PatientVisitId
    	LEFT JOIN DoctorFacility od ON pv.OperatingDoctorId = od.DoctorFacilityId
    	LEFT JOIN DoctorFacility oth ON pv.OtherDoctorId = oth.DoctorFacilityId
    	LEFT JOIN (
    	            SELECT
    	                PatientProfileId ,
    	                MAX(ISNULL([First] , '')) [First] ,
    	                MAX(ISNULL(Middle , '')) Middle ,
    	                MAX(ISNULL([Last] , '')) [Last]
    	            FROM
    	                PatientContacts
    	            GROUP BY
    	                PatientProfileId
    	          ) pc ON pp.PatientProfileId = pc.PatientProfileId
 
WHERE 	ApptKind = 1 AND ISNULL(Canceled,0)  = 0 AND
	a.ApptStart >= ISNULL('12/31/2008','1/1/1900') AND a.ApptStart < dateadd(d, 1, ISNULL('12/31/2008','1/1/3000'))
	AND  --Filter on doctor
	(
	(NULL IS NOT NULL AND a.ResourceID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND a.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros