Member_2_7969213
asked on
SQL query
declare @bcbs_load_date char(8)
INSERT INTO tbl_claims_bcbs_pharmacy_t est
(MASTER_CONSUMER_ID,
CLAIM_NUMBER,
CLAIM_ADJUSTMENT_NUMBER,
CLAIM_DISPOSITION_CODE,
FILLED_DATE,
NDC_NUMBER,
DEA_NUMBER,
PHARMACY_NUMBER,
PHARMACY_NAME,
THERAPEUTIC_CLASS,
QUANTITY_DISPENSED,
DAYS_SUPPLIED,
FORMULARY_INDICATOR,
RETAIL_MAIL_INDICATOR,
BILLED_CHARGES,
[YEAR],
QUARTER,
[MONTH],
PATIENT_RESPONSIBILITY_AMO UNT,
PAID_AMOUNT,
SCRIPT_WRITTEN_DATE,
ALLOWED_AMT,
RX_NUMBER,
INGREDIENT_COST,
COPAY,
DRUG_NAME,
DISPENSED_BRAND_GENERIC_CO DE,
GENERIC_PRODUCT_IDENTIFIER ,
OTHER_PARTY_LIABILITY_AMOU NT,
PAID_DATE,
CLAIM_SOURCE_SYSTEM,
COINSRN_AMT,
DDCTBL_AMT,
MBR_SNCTNS_PNLTYS_AMT,
PAT_PAID_DFRNTL_AMT,
DISPENSE_AS_WRITTEN,
DISPENSE_DAYS_COUNT,
DRUG_STRENGTH,
MANUFACTURER_NAME,
INCURRED_YEAR_MONTH,
CLAIM_MAX_REVISION_DATE,
LAST_CLAIM_ADJUSTMENT,
PRIMARY_CARRIER_RESPONSIBL E_FOR_CLAI M,
ADJUDICATION_DATE,
CLAIM_PROCESSING_DATE,
CLAIM_ACTION_TYPE,
NPI,
PATIENT_ACCOUNT_NUMBER,
STATUS_CODE,
month_ending
)
SELECT MASTER_CONSUMER_ID ,
CLAIM_NUMBER ,
CLAIM_ADJUSTMENT_NUMBER ,
CLAIM_DISPOSITION_CODE ,
FILLED_DATE ,
NDC_NUMBER ,
DEA_NUMBER ,
PHARMACY_NUMBER ,
PHARMACY_NAME ,
THERAPEUTIC_CLASS ,
SUM(cast(isnull(QUANTITY_D ISPENSED ,'0.00') as float)) as [QUANTITY_DISPENSED],
SUM(cast(isnull(DAYS_SUPPL IED ,'0') as float)) as [DAYS_SUPPLIED],
FORMULARY_INDICATOR ,
RETAIL_MAIL_INDICATOR ,
SUM(cast(isnull(BILLED_CHA RGES, '0.00') as money)) as [BILLED_CHARGES] ,
[YEAR] ,
QUARTER ,
[MONTH] ,
SUM(cast(isnull(PATIENT_RE SPONSIBILI TY_AMOUNT, '0.00') as money)) as [PATIENT_RESPONSIBILITY_AM OUNT] ,
SUM(cast(isnull(PAID_AMOUN T, '0.00') as money)) as [PAID_AMOUNT] ,
SCRIPT_WRITTEN_DATE ,
SUM(cast(isnull(ALLOWED_AM T, '0.00') as money)) as [ALLOWED_AMT] ,
RX_NUMBER ,
sum(cast(isnull(INGREDIENT _COST,'0.0 0') as money)) as [INGREDIENT_COST],
sum(cast(isnull(COPAY,'0.0 0') as money)) as [COPAY],
DRUG_NAME ,
DISPENSED_BRAND_GENERIC_CO DE ,
GENERIC_PRODUCT_IDENTIFIER ,
sum(cast(isnull(OTHER_PART Y_LIABILIT Y_AMOUNT ,'0.00')as money)) as [OTHER_PARTY_LIABILITY_AMO UNT],
PAID_DATE ,
CLAIM_SOURCE_SYSTEM ,
sum(cast(isnull(COINSRN_AM T ,'0.00')as money)) as [COINSRN_AMT],
sum(cast(isnull(DDCTBL_AMT ,'0.00')as money)) as [DDCTBL_AMT],
sum(cast(isnull(MBR_SNCTNS _PNLTYS_AM T ,'0.00')as money)) as [MBR_SNCTNS_PNLTYS_AMT],
sum(cast(isnull(PAT_PAID_D FRNTL_AMT ,'0.00')as money)) as [PAT_PAID_DFRNTL_AMT],
DISPENSE_AS_WRITTEN ,
DISPENSE_DAYS_COUNT ,
DRUG_STRENGTH ,
MANUFACTURER_NAME ,
INCURRED_YEAR_MONTH ,
CLAIM_MAX_REVISION_DATE ,
LAST_CLAIM_ADJUSTMENT ,
PRIMARY_CARRIER_RESPONSIBL E_FOR_CLAI M ,
ADJUDICATION_DATE ,
CLAIM_PROCESSING_DATE
CLAIM_ACTION_TYPE ,
NPI ,
PATIENT_ACCOUNT_NUMBER ,
STATUS_CODE,
@bcbs_load_date
FROM tbl_claims_bcbs_pharmacy_s tage_test AS a
WHERE NOT EXISTS ( SELECT MASTER_CONSUMER_ID ,
CLAIM_NUMBER ,
CLAIM_ADJUSTMENT_NUMBER ,
CLAIM_DISPOSITION_CODE
FROM tbl_claims_bcbs_pharmacy AS b
WHERE a.MASTER_CONSUMER_ID = b.MASTER_CONSUMER_ID
AND a.CLAIM_NUMBER = b.CLAIM_NUMBER
AND a.CLAIM_ADJUSTMENT_NUMBER = b.CLAIM_ADJUSTMENT_NUMBER
AND a.CLAIM_DISPOSITION_CODE = b.CLAIM_DISPOSITION_CODE )
Group by MASTER_CONSUMER_ID, claim_number, claim_adjustment_number, claim_disposition_code, filled_date, ndc_number, claim_processing_date, pharmacy_number , pharmacy_name ,therapeutic_class,
QUANTITY_DISPENSED,DAYS_SU PPLIED,dea _number, formulary_indicator ,retail_mail_indicator,[YE AR] ,quarter ,[MONTH], script_written_date , rx_number ,drug_name , dispensed_brand_generic_co de ,generic_product_identifie r,paid_dat e ,
claim_source_system, dispense_as_written , dispense_days_count , drug_strength , manufacturer_name , incurred_year_month, claim_max_revision_date , last_claim_adjustment ,
primary_carrier_responsibl e_for_clai m , adjudication_date, claim_action_type , npi , patient_account_number , status_code
Error:
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
INSERT INTO tbl_claims_bcbs_pharmacy_t
(MASTER_CONSUMER_ID,
CLAIM_NUMBER,
CLAIM_ADJUSTMENT_NUMBER,
CLAIM_DISPOSITION_CODE,
FILLED_DATE,
NDC_NUMBER,
DEA_NUMBER,
PHARMACY_NUMBER,
PHARMACY_NAME,
THERAPEUTIC_CLASS,
QUANTITY_DISPENSED,
DAYS_SUPPLIED,
FORMULARY_INDICATOR,
RETAIL_MAIL_INDICATOR,
BILLED_CHARGES,
[YEAR],
QUARTER,
[MONTH],
PATIENT_RESPONSIBILITY_AMO
PAID_AMOUNT,
SCRIPT_WRITTEN_DATE,
ALLOWED_AMT,
RX_NUMBER,
INGREDIENT_COST,
COPAY,
DRUG_NAME,
DISPENSED_BRAND_GENERIC_CO
GENERIC_PRODUCT_IDENTIFIER
OTHER_PARTY_LIABILITY_AMOU
PAID_DATE,
CLAIM_SOURCE_SYSTEM,
COINSRN_AMT,
DDCTBL_AMT,
MBR_SNCTNS_PNLTYS_AMT,
PAT_PAID_DFRNTL_AMT,
DISPENSE_AS_WRITTEN,
DISPENSE_DAYS_COUNT,
DRUG_STRENGTH,
MANUFACTURER_NAME,
INCURRED_YEAR_MONTH,
CLAIM_MAX_REVISION_DATE,
LAST_CLAIM_ADJUSTMENT,
PRIMARY_CARRIER_RESPONSIBL
ADJUDICATION_DATE,
CLAIM_PROCESSING_DATE,
CLAIM_ACTION_TYPE,
NPI,
PATIENT_ACCOUNT_NUMBER,
STATUS_CODE,
month_ending
)
SELECT MASTER_CONSUMER_ID ,
CLAIM_NUMBER ,
CLAIM_ADJUSTMENT_NUMBER ,
CLAIM_DISPOSITION_CODE ,
FILLED_DATE ,
NDC_NUMBER ,
DEA_NUMBER ,
PHARMACY_NUMBER ,
PHARMACY_NAME ,
THERAPEUTIC_CLASS ,
SUM(cast(isnull(QUANTITY_D
SUM(cast(isnull(DAYS_SUPPL
FORMULARY_INDICATOR ,
RETAIL_MAIL_INDICATOR ,
SUM(cast(isnull(BILLED_CHA
[YEAR] ,
QUARTER ,
[MONTH] ,
SUM(cast(isnull(PATIENT_RE
SUM(cast(isnull(PAID_AMOUN
SCRIPT_WRITTEN_DATE ,
SUM(cast(isnull(ALLOWED_AM
RX_NUMBER ,
sum(cast(isnull(INGREDIENT
sum(cast(isnull(COPAY,'0.0
DRUG_NAME ,
DISPENSED_BRAND_GENERIC_CO
GENERIC_PRODUCT_IDENTIFIER
sum(cast(isnull(OTHER_PART
PAID_DATE ,
CLAIM_SOURCE_SYSTEM ,
sum(cast(isnull(COINSRN_AM
sum(cast(isnull(DDCTBL_AMT
sum(cast(isnull(MBR_SNCTNS
sum(cast(isnull(PAT_PAID_D
DISPENSE_AS_WRITTEN ,
DISPENSE_DAYS_COUNT ,
DRUG_STRENGTH ,
MANUFACTURER_NAME ,
INCURRED_YEAR_MONTH ,
CLAIM_MAX_REVISION_DATE ,
LAST_CLAIM_ADJUSTMENT ,
PRIMARY_CARRIER_RESPONSIBL
ADJUDICATION_DATE ,
CLAIM_PROCESSING_DATE
CLAIM_ACTION_TYPE ,
NPI ,
PATIENT_ACCOUNT_NUMBER ,
STATUS_CODE,
@bcbs_load_date
FROM tbl_claims_bcbs_pharmacy_s
WHERE NOT EXISTS ( SELECT MASTER_CONSUMER_ID ,
CLAIM_NUMBER ,
CLAIM_ADJUSTMENT_NUMBER ,
CLAIM_DISPOSITION_CODE
FROM tbl_claims_bcbs_pharmacy AS b
WHERE a.MASTER_CONSUMER_ID = b.MASTER_CONSUMER_ID
AND a.CLAIM_NUMBER = b.CLAIM_NUMBER
AND a.CLAIM_ADJUSTMENT_NUMBER = b.CLAIM_ADJUSTMENT_NUMBER
AND a.CLAIM_DISPOSITION_CODE = b.CLAIM_DISPOSITION_CODE )
Group by MASTER_CONSUMER_ID, claim_number, claim_adjustment_number, claim_disposition_code, filled_date, ndc_number, claim_processing_date, pharmacy_number , pharmacy_name ,therapeutic_class,
QUANTITY_DISPENSED,DAYS_SU
claim_source_system, dispense_as_written , dispense_days_count , drug_strength , manufacturer_name , incurred_year_month, claim_max_revision_date , last_claim_adjustment ,
primary_carrier_responsibl
Error:
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and i counted 48 vs 50
to troubleshoot, but both lists i t an excel file, side by side, to see which columns are mismatching
ASKER
Thank u Hengel
I counted and checked in excel too all there are 50 columns in both insert and select
I counted and checked in excel too all there are 50 columns in both insert and select
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Friends thank You!! Yes Comma was missing