关于海关税率的一些sql计算

关于税率系统做的计算：
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,'')!=''

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！