select TRADENAME as TENTHUOC,VALUE,C_UOM, UNITPRICE_SERVICE
,sum (case HIS_DEPARTMENT_ID when 1000233 then totalquantity else 0 end) LAO_X
,sum (case HIS_DEPARTMENT_ID when 1000234 then totalquantity else 0 end) HSTC_X
,sum (case HIS_DEPARTMENT_ID when 1000235 then totalquantity else 0 end) Noi_TH_X
,sum (case HIS_DEPARTMENT_ID when 1000236 then totalquantity else 0 end) HH_X
,sum (case HIS_DEPARTMENT_ID when 1000237 then totalquantity else 0 end) G_X
,sum (case HIS_DEPARTMENT_ID when 1000238 then totalquantity else 0 end) Ngoai_TH_X
,sum (case HIS_DEPARTMENT_ID when 1000239 then totalquantity else 0 end) NHI_X
,sum (case HIS_DEPARTMENT_ID when 1000247 then totalquantity else 0 end) KKB_X
,sum (case HIS_DEPARTMENT_ID when 1000261 then totalquantity else 0 end) UB_X
,sum (case HIS_DEPARTMENT_ID when 1000264 then totalquantity else 0 end) PNN_X
,sum (case HIS_DEPARTMENT_ID when 1000268 then totalquantity else 0 end) DKTYC_X
,sum (case HIS_DEPARTMENT_ID when 1000269 then totalquantity else 0 end) PMT_X
,sum (case HIS_DEPARTMENT_ID when 1000270 then totalquantity else 0 end) GMHS_X
,sum (case HIS_DEPARTMENT_ID when 1000271 then totalquantity else 0 end) PHCN_X
,sum (case HIS_DEPARTMENT_ID when 1000272 then totalquantity else 0 end) PTLN_X
,sum (case HIS_DEPARTMENT_ID when 1000313 then totalquantity else 0 end) CMU_X
,sum (case HIS_DEPARTMENT_ID when 1000333 then totalquantity else 0 end) CC_X

,sum (case HIS_DEPARTMENT_ID when 1000233 then RETURNEDQUANTITY else 0 end) LAO_T
,sum (case HIS_DEPARTMENT_ID when 1000234 then RETURNEDQUANTITY else 0 end) HSTC_T
,sum (case HIS_DEPARTMENT_ID when 1000235 then RETURNEDQUANTITY else 0 end) Noi_TH_T
,sum (case HIS_DEPARTMENT_ID when 1000236 then RETURNEDQUANTITY else 0 end) HH_T
,sum (case HIS_DEPARTMENT_ID when 1000237 then RETURNEDQUANTITY else 0 end) G_T
,sum (case HIS_DEPARTMENT_ID when 1000238 then RETURNEDQUANTITY else 0 end) Ngoai_TH_T
,sum (case HIS_DEPARTMENT_ID when 1000239 then RETURNEDQUANTITY else 0 end) NHI_T
,sum (case HIS_DEPARTMENT_ID when 1000247 then RETURNEDQUANTITY else 0 end) KKB_T
,sum (case HIS_DEPARTMENT_ID when 1000261 then RETURNEDQUANTITY else 0 end) UB_T
,sum (case HIS_DEPARTMENT_ID when 1000264 then RETURNEDQUANTITY else 0 end) PNN_T
,sum (case HIS_DEPARTMENT_ID when 1000268 then RETURNEDQUANTITY else 0 end) DKTYC_T
,sum (case HIS_DEPARTMENT_ID when 1000269 then RETURNEDQUANTITY else 0 end) PMT_T
,sum (case HIS_DEPARTMENT_ID when 1000270 then RETURNEDQUANTITY else 0 end) GMHS_T
,sum (case HIS_DEPARTMENT_ID when 1000271 then RETURNEDQUANTITY else 0 end) PHCN_T
,sum (case HIS_DEPARTMENT_ID when 1000272 then RETURNEDQUANTITY else 0 end) PTLN_T
,sum (case HIS_DEPARTMENT_ID when 1000313 then RETURNEDQUANTITY else 0 end) CMU_T
,sum (case HIS_DEPARTMENT_ID when 1000333 then RETURNEDQUANTITY else 0 end) CC_T

,sum(TOTALQUANTITY - RETURNEDQUANTITY) total
 from HIS_BC_XUATTHUOC
 where  1=1
 and (HIS_STORAGE_ID = $P{HIS_Storage_ID} or $P{HIS_Storage_ID} is null)
 and HIS_DEPARTMENT_ID not in (1000273,0) and HIS_DEPARTMENT_ID is not null
 and (HIS_DEPARTMENT_ID = $P{HIS_DEPARTMENT_ID} or $P{HIS_DEPARTMENT_ID} is null)
 and (HIS_MEDICINECLASSIFY_ID = $P{HIS_MedicineClassify_ID} or $P{HIS_MedicineClassify_ID} is null)
 and (HIS_MEDICINEGROUP_ID = $P{HIS_MedicineGroup_ID} or $P{HIS_MedicineGroup_ID} is null)
 and (HIS_PATIENTTYPE_ID = $P{HIS_PATIENTTYPE_ID} or $P{HIS_PATIENTTYPE_ID} is null)
 and (HIS_SERVICE_ID = $P{HIS_Service_ID} or $P{HIS_Service_ID} is null)
 and (INOUT=$P{INOUT} or $P{INOUT} is null)
 AND (ISINPATIENT = $P{ISINPATIENT} OR $P{ISINPATIENT} IS NULL)
 and (ISFREEBYAPPROVAL = $P{ISFREEBYAPPROVAL} or $P{ISFREEBYAPPROVAL} is null)
 and ((ACTDATE between $P{FromDate} and $P{ToDate} AND TYPE in ('In_Import','In_Export')) OR (VERIFIEDDATE between $P{FromDate} and $P{ToDate} AND TYPE in ('Out_Export')))
and  HIS_DEPARTMENT_ID in (1000233,1000234,1000235,1000236,1000237,1000238,1000239,1000247,1000261,
1000264,1000268,1000269,1000270,1000271,1000272,1000313,1000333)
 group by TRADENAME,C_UOM,UNITPRICE_SERVICE,VALUE
order by TRADENAME,UNITPRICE_SERVICE