SQL 2000 - Returned result pulling date wrong
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.
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)
)
ASKER
If I read this right, I changed to this:
'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(CONVERT(VARCHAR(10)
END ,
However, I still get the 1900-01-01 00:00:00.000 returned in my dataset. Ultimately this is what I would like to see '' (nothing).
'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(CONVERT(VARCHAR(10)
END ,
However, I still get the 1900-01-01 00:00:00.000 returned in my dataset. Ultimately this is what I would like to see '' (nothing).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you angel and TommyTupa - I used angels coding help and read on Tommys. I really appreciate the help on this one!
ASKER
Split points to be fair as both assisted. More so with angel, but the reference article and explanation was great from Tommy - again thanks!
ISNULL(pi.BirthDate , '')
into this for example:
ISNULL(CONVERT(VARCHAR(10)
the 1900-01-01 comes from the implicit conversion from '' into datetime.
you have to explicitly run a explicit conversion from the datetime value into varchar.