bwanagat
asked on
SQL Error - An expression of non-boolean type
Complete error:
An expression of non-boolean type specified in a context where a condition is expected, near 'and'.
An expression of non-boolean type specified in a context where a condition is expected, near 'and'.
SQL Code:
set nocount on
Select
Record_Type = 'UD',
right('00000000000' + cast (row_number() over (order by DrugIdentifier.Identifier) as varchar (11)),11) Line_number,
Data_Level = 'PP',
Plan_ID_Qualifier = 'C',
Plan_ID_Code = right(PrimaryPlan.ThirdPartyId,17), -- short id for ins company length of 17 alpha/numeric
Plan_Name = left(cast(PrimaryPlan.PlanName as char (30))+' ',30), --length of 30 alpha/numeric
Pharmacy_ID_Qualifier = 'N',
Pharmacy_ID_Code = cast(StoreProfile.ProviderId as char (17)), -- length of 17
Pharmacy_Zip_Code = cast(StoreProfile.Zip as char (9)), -- length of 9 numeric
Product_Code_Qualifier = 'N',
Product_Code = cast(DrugIdentifier.Identifier as char (17)), -- length of 17
Prduct_Description = cast(Drug.Description as char (30)), --length of 30
DAW = ' ',
Total_Units = RIGHT(REPLACE('00000000000000' + cast(disp.DispensedQuantity as varchar(13)),'.',''),14), -- length of 14 total number of vials per script numeric + sign = 15
Total_Units_sign =
Case
when disp.status = 7 then ' '
when disp.status = 10 then '-'
end,
Unit = 'EA',
Dosage = ' ', -- will be blank
Diagnosis = ' ', -- will be blank
Rebate_Days =
Case
When disp.DaysSupply < 28 then '028'
else right('000' + cast(disp.DaysSupply as char (2)),3)
end,
Rebate_Days_Sign =
Case
when claim.ClaimState = 2 and claim.RequestTransactionCode = 'B1' then ' '
when claim.ClaimState = 3 and claim.RequestTransactionCode = 'B2' then '-'
end,
Prescription_Type = --- checks for new/refill = 1 , if reversal then -1 numeric
Case
When claim.ClaimState = 2 and claim.RequestTransactionCode = 'B1' then '1'
When claim.ClaimState = 3 and claim.RequestTransactionCode = 'B2' then '-1'
end,
Num_of_script_sign =
Case
when claim.ClaimState = 2 and claim.RequestTransactionCode = 'B1' then ' '
when claim.ClaimState = 3 and claim.RequestTransactionCode = 'B2' then '-'
end,
Number_of_Prescriptions = '0000001', -- length of 7 + sign = 8
Script_num_sign =
Case
when claim.ClaimState = 2 and claim.RequestTransactionCode = 'B1' then ' '
when claim.ClaimState = 3 and claim.RequestTransactionCode = 'B2' then '-'
end,
Prescription_Number = right('0000000' + cast(rx.RxNumber as char (7)),7), -- length of 7 + sign = 8
convert(varchar,disp.DateFilled,112) as Date_Filled,
Reimbursement_Date = ' ', -- will be blank (8)
Therapeutic_Class_Qualifier = ' ', -- will be blank (1)
Therapeutic_Class = ' ', -- length of 17 blank
Therapeutic_Class_Description = ' ', --length of 30 blank
Plan_Reimbursement = ' ', -- will be blank
Plan_Reimbursement_Amount = ' ', -- length of 12 blank
Patient_Amount = ' ', -- length of 12 blank
New_Refill_Code =
Case
when (disp.refillnumber = 0) then '00'
else right('00' + cast(disp.refillnumber as char (1)),2)
end,
Record_Purpose_Ind = 'R',
Rebate_Per_Unit = ' ', -- LENGTH OF 12 blank
Requested_Rebate = ' ', -- length of 12 blank
Formulary_Code = ' ', --length of 17 blank
Prescriber_ID_Qualifier = ' ', -- will be blank
Prescriber_ID = ' ', -- length of 10 blank
Encrypted_Patient_ID_Code = ' ', -- length of 17 blank
Claim_Number = ' ' -- length of 20 blank
From rx rx
Join refill disp on rx.rxid = disp.rxid
Join claim claim on disp.refillid = claim.refillid
join drug on drug.drugid = disp.drugid
join drugidentifier on drugidentifier.drugid = drug.drugid and identifiercode = 3
join vw_Reporting_FirstPaidClaim PrimaryClaim on PrimaryClaim.refillid = disp.refillid
join ThirdParty PrimaryPlan on PrimaryPlan.ThirdPartyId = PrimaryClaim.ThirdPartyId
left outer join vw_Reporting_LastPaidClaim SecondaryClaim on SecondaryClaim.refillid = disp.refillid and
SecondaryClaim.ClaimId <> PrimaryClaim.ClaimId
left outer join ThirdParty SecondaryPlan on SecondaryPlan.ThirdPartyId = SecondaryClaim.ThirdPartyId
Inner Join StoreProfile on StoreProfile.IsPrimary = 1
Where dateadd(mm,-3,cast(cast(month(getdate()) as varchar)+'/01/'+cast(year(getdate()) as varchar) as datetime)) --first of 3 months ago
and cast(cast(month(getdate()) as varchar)+'/01/'+cast(year(getdate()) as varchar) as datetime) --first of this month
and drugidentifier.Identifier in ('57844071319')
and claim.IsRelevant = 1
and (PrimaryPlan.PlanName not like 'medco%'
and PrimaryPlan.PlanName not like 'optima%'
and PrimaryPlan.PlanName not like 'PCS%'
and PrimaryPlan.PlanName not like 'tricare%'
and PrimaryPlan.PlanName not like 'Wellcare%')
order by disp.DateFilled
ASKER
i'm trying to systematically compare data that falls within a querter period of time
You'll have to introduce a comparison along the lines of:
where SomeDate between ( your calc #1 ) and ( your calc #2 )
You are constructing two dates but not comparing them to anything. That is the source of the error.
where SomeDate between ( your calc #1 ) and ( your calc #2 )
You are constructing two dates but not comparing them to anything. That is the source of the error.
ASKER
can you show me example
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THank you!
Open in new window