Hi ,
can you please give suggestion on below part. basically that case statement part
XCOPRI_Nbrx:
select * from (
select * from (
SELECT
XREF.ims_prescriber_id AS IMS_ID,
XREF.hcp_id AS SKLSI_ID,
--XREF.hcp_name AS HCP_NAME,
MCA.address_line_1_text AS ADDRESS_LINE_1,
MCA.adress_line_2_text AS ADDRESS_LINE_2,
MCA.city_name AS CITY,
MCA.state_name AS STATE,
MCA.postal_code_number AS ZIP,
MCA.territory_id AS TERRITORY_ID,
MCA.territory_name AS TERRITORY_NAME,
MCA.region_id AS REGION_ID,
MCA.region_name AS REGION_NAME,
MCA.area_id AS AREA_ID,
MCA.area_name AS AREA_NAME,
MKT.cstm_prod_grp_value,
F.prod_group_id,
MKT.PRODUCT_GROUP,
XREF.PDRP_FLAG,
P.trimester_of_year_cal,
CASE
When XREF.PDRP_FLAG='Y' THEN 'PDRP' Else XREF.hcp_name End as HCP_NAME1,
CASE
WHEN MKT.mft_def_desc = 'Total Epilepsy Market'
AND P.rnk BETWEEN '1' AND '128' THEN Sum(total_rx)else 0
END AS TOTAL_EPI_MKT_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'APTIOM' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS APTIOM_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'BRIVIACT' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS BRIVIACT_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'FYCOMPA' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS FYCOMPA_TRX,
CASE
WHEN MKT.mft_def_desc = 'Target Brand'
AND cstm_prod_grp_value = 'VIMPAT' and P.RNK BETWEEN '1' AND '128' THEN
Sum(total_rx) else 0
END AS VIMPAT_TRX,
CASE
WHEN
cstm_prod_grp_value = 'XCOPRI'
AND P.rnk BETWEEN '1' AND '128' THEN Sum(total_rx) else 0
END AS XCOPRI_TRX,
CAST(NULL AS INT) AS TOTAL_EPI_MKT_NBRX,
CAST(NULL AS INT) AS APTIOM_NBRX,
CAST(NULL AS INT) AS BRIVIACT_NBRX,
CAST(NULL AS INT) AS FYCOMPA_NBRX,
CAST(NULL AS INT) AS VIMPAT_NBRX ,
P.week_ending_date AS DATA_WEEK_DATE,
case when XREF.priority_text = 'Target' or IncludeIMS.Include_IMS_PRESCRIBER_ID is not null then '1' else '0' end flag,
F.CHANNEL_INDICATOR //uncomment
FROM $(v_Revised_Dremio_Path)VDS_V_FCT_TRX_WEEKLY_ALL F;
