关于税率系统做的计算:
SELECT id, business_no, trade_total 总价, Rate, g_amount 关税
, isnull(copG_amount, 0) AS copG_amount
, SUM(copG_amount) OVER (PARTITION BY business_no ) AS TotalG_amount, SUM(copZG_amount) OVER (PARTITION BY business_no ) AS TotalZG_amount
, isnull(copUSD_amount, 0) AS copUSD_amount
, isnull(copZG_amount, 0) AS copZG_amount
FROM (
SELECT dp.id, dh.business_no, dp.trade_total 总价, GRA.Rate, g_amount 关税(关务录入)
, CONVERT(DECIMAL(18, 2), GRA.Rate * 0.01 * dp.trade_total) AS copG_amount 总金额乘以税号的税率
, Convert(decimal(18, 2), dp.trade_total * GRA.RATE) AS copUSD_amount 转换成美金的税率
, Convert(decimal(18, 2), dp.trade_total * 0.13) AS copZG_amount 增值税
FROM dbo.wlxt_delegate_head dh
INNER JOIN wlxt_delegate_list_pre dp ON dh.business_no = dp.business_no
LEFT JOIN base_GamountRate GRA ON dp.hs_code = GRA.hscode
WHERE isnull(flag, '') != 'D'
AND dh.business_no = 'JIT2019060003802557'
AND GRA.Rate > 0
) a
SELECT ROW_NUMBER() Over(order by a.id desc) as row_number
,COUNT(1) OVER(PARTITION BY 1) TotalNum,
entry_ids=stuff((select ','+entry_id from wlxt_delegate_2_entry d1 where d1.business_no=a.business_no for xml path('')), 1, 1, '')
,a.id,a.business_no [委托编号] , bill_no [分单号],bk_11,traf_name,trade_name,a.note [备注], case a.i_e_flag when '3' then '进境' end '进出口类型',delegate_type [委托类型]
,a.column_2,a.create_date [创建日期],a.company_id [经营单位],a.dept_id [部门ID],a.column_5,
pre.material_no,pre.qty,pre.unit_name,pre.trade_total,pre.curr,pre.country_name,pre.wt
FROM wlxt_delegate_head a
left join wlxt_delegate_2_entry b on a.business_no=b.business_no
left join wlxt_delegate_list_pre pre on a.business_no=pre.business_no
where a.Flag!='D' and i_e_flag='3' and isnull(b.entry_id,'')!=''