if OBJECT_ID('tempdb..#temp') is not null drop table #temp; declare @domain smallint; select @domain=domain from domain where current_flag=1; DECLARE @stone_places INT; SELECT @stone_places = CAST(value AS INT) FROM ops_default WHERE comp_code = 56 AND code = 6; DECLARE @total INT; CREATE TABLE #temp ( domain smallint, comp_code smallint, order_no int, sub_order_no smallint, job_order_no smallint, split_order_no smallint, dec_stock_type INT, batch_no int, dec_date DATETIME, qty INT, total_weight DECIMAL(18, 4), export_amount MONEY, total_labour MONEY, total_labour_USD MONEY, total_labour_CNY MONEY, receive_district SMALLINT, receive_district_desc NVARCHAR(50), ver_no SMALLINT, adding_labour_cost MONEY, adding_labour_cost_USD MONEY, adding_labour_cost_CNY MONEY, export_amount_HKD MONEY, export_amount_CNY MONEY, total int, aes_export_amount MONEY, aes_mark_good_value MONEY, other_accessory_export_amount MONEY, export_amount_sum MONEY, gold_export_amount MONEY, sync_flag int, last_sync_date datetime, last_sync_by varchar(10) ); insert into #temp(domain,comp_code , order_no , sub_order_no , job_order_no , split_order_no , dec_date,receive_district,ver_no, adding_labour_cost,total_labour,qty,dec_stock_type,batch_no,receive_district_desc,sync_flag,last_sync_date,last_sync_by,total_weight) select @domain,comp_code , order_no , sub_order_no , job_order_no , split_order_no , dec_date,rd.receive_district,ver_no, adding_labour_cost,total_labour,qty,dec_stock_type,batch_no,rd.description as receive_district_desc,sync_flag,last_sync_date,last_sync_by, ROUND(gold_weight + accessories_weight + other_accessories_weight, 2) from customs_master cm left join receive_district rd on cm.receive_district = rd.receive_district WHERE cm.info_number = '' and synchro_mid_flag = 1 and comp_code = 56 ; UPDATE t SET adding_labour_cost_USD = ROUND(adding_labour_cost * rate_value_us, 2), total_labour_USD = ROUND(total_labour * rate_value_us, 2) from #temp t inner join rate r on r.date_from <= t.dec_date AND r.date_to >= t.dec_date; UPDATE t SET adding_labour_cost_CNY = ROUND(adding_labour_cost_USD * rate_value_cn, 2), total_labour_CNY = ROUND(total_labour_USD * rate_value_cn, 2) from #temp t inner join rate r on r.date_from <= t.dec_date AND r.date_to >= t.dec_date; UPDATE #temp SET gold_export_amount =gold_result.gold_export_amount FROM #temp AS temp INNER JOIN ( select [domain],[comp_code],[order_no],[sub_order_no],[job_order_no],[split_order_no],[batch_no], sum(gold_export_amount) as gold_export_amount from (SELECT #temp.[domain], #temp.[comp_code], #temp.[order_no], #temp.[sub_order_no], #temp.[job_order_no], #temp.[split_order_no], #temp.[batch_no], dec_gold_accessories.dec_gold_no , ROUND( ROUND( SUM( CASE WHEN ISNULL(dec_stock_type_ref.gold_loss_rate, 0) = 1 THEN 0 ELSE dec_gold_accessories.gold_weight / (1 - ISNULL(dec_stock_type_ref.gold_loss_rate, 0)) END ), 4 )* dec_gold_accessories.dec_unit_price,2) AS gold_export_amount FROM #temp INNER JOIN dbo.dec_stock_type ON dec_stock_type.dec_stock_type = #temp.dec_stock_type INNER JOIN dbo.dec_hk_stock_type ON dec_hk_stock_type.dec_hk_stock_type = dec_stock_type.dec_hk_stock_type INNER JOIN dbo.dec_gold_accessories ON dec_gold_accessories.domain = #temp.domain AND dec_gold_accessories.comp_code = #temp.comp_code AND dec_gold_accessories.order_no = #temp.order_no AND dec_gold_accessories.sub_order_no = #temp.sub_order_no AND dec_gold_accessories.job_order_no = #temp.job_order_no AND dec_gold_accessories.split_order_no = #temp.split_order_no AND dec_gold_accessories.batch_no = #temp.batch_no LEFT JOIN dbo.product_gold_type ON dec_gold_accessories.dec_gold_no = product_gold_type.gold_type AND gold_group2 <> '0' LEFT JOIN dbo.dec_stock_type_ref ON dec_stock_type_ref.dec_stock_type = #temp.dec_stock_type AND dec_stock_type_ref.dec_gold_no = dec_gold_accessories.dec_gold_no GROUP BY #temp.[domain], #temp.[comp_code], #temp.[order_no], #temp.[sub_order_no], #temp.[job_order_no], #temp.[split_order_no], #temp.[batch_no], dec_gold_accessories.dec_gold_no,dec_gold_accessories.dec_unit_price ) AS gold_detail_result group by [domain],[comp_code],[order_no],[sub_order_no],[job_order_no],[split_order_no],[batch_no] ) AS gold_result ON temp.[domain] = gold_result.[domain] AND temp.[comp_code] = gold_result.[comp_code] AND temp.[order_no] = gold_result.[order_no] AND temp.[sub_order_no] = gold_result.[sub_order_no] AND temp.[job_order_no] = gold_result.[job_order_no] AND temp.[split_order_no] = gold_result.[split_order_no] AND temp.[batch_no] = gold_result.[batch_no]; IF OBJECT_ID('tempdb..#temp_accessories') IS NOT NULL DROP TABLE #temp_accessories; SELECT a.*, CAST(0 AS SMALLINT) AS dec_item_type, itm.item_type, single_wgt = ROUND( a.weight / (CASE (a.qty * a.packing_qty) WHEN 0 THEN 1 ELSE (a.qty * a.packing_qty) END ), @stone_places ) INTO #temp_accessories FROM #temp AS temp INNER JOIN dbo.accessories AS a ON temp.domain = a.domain AND temp.comp_code = a.comp_code AND temp.order_no = a.order_no AND temp.sub_order_no = a.sub_order_no AND temp.job_order_no = a.job_order_no AND temp.split_order_no = a.split_order_no AND a.cancel_flag = 0 LEFT JOIN item_code_master itm ON a.item_code = itm.item_code; UPDATE #temp_accessories SET dec_item_type = ditr.dec_item_type FROM #temp_accessories aes INNER JOIN dec_item_type_ref ditr ON aes.item_code = ditr.item_code WHERE aes.item_type <> 'A'; UPDATE #temp_accessories SET dec_item_type = ditr.dec_item_type FROM #temp_accessories aes INNER JOIN dec_item_type_ref ditr ON aes.item_code = ditr.item_code AND single_wgt <= ditr.max_wgt AND single_wgt >= ditr.min_wgt WHERE aes.item_type = 'A'; UPDATE #temp SET aes_mark_good_value = aes_result.aes_mark_good_value, aes_export_amount = round(aes_result.aes_export_amount,2) FROM #temp AS temp INNER JOIN (select [domain],[comp_code],[order_no],[sub_order_no],[job_order_no],[split_order_no],[batch_no], sum(aes_export_amount) as aes_export_amount,SUM(cost) AS aes_mark_good_value from (SELECT #temp.[domain], #temp.[comp_code], #temp.[order_no], #temp.[sub_order_no], #temp.[job_order_no], #temp.[split_order_no], #temp.[batch_no], dec_item_type_ref.dec_item_type, ROUND(ROUND(SUM(a.weight * convert_weight_times),4) * dec_item_type.dec_unit_price,2) AS aes_export_amount, SUM(cost) AS cost FROM #temp INNER JOIN dbo.dec_stock_type ON dec_stock_type.dec_stock_type = #temp.dec_stock_type INNER JOIN dbo.dec_hk_stock_type ON dec_hk_stock_type.dec_hk_stock_type = dec_stock_type.dec_hk_stock_type INNER JOIN #temp_accessories AS a ON #temp.domain = a.domain AND #temp.order_no = a.order_no AND #temp.sub_order_no = a.sub_order_no AND #temp.job_order_no = a.job_order_no AND #temp.split_order_no = a.split_order_no AND a.cancel_flag = 0 LEFT JOIN dbo.item_code_master AS icm ON icm.item_code = a.item_code LEFT JOIN dbo.dec_item_type_ref ON dec_item_type_ref.item_code = a.item_code and ( round(a.weight/(case (a.qty*a.packing_qty) when 0 then 1 else (a.qty*a.packing_qty) end ), icm.decimal_place) <= case when icm.item_type='A' then dec_item_type_ref.max_wgt end or dec_item_type_ref.max_wgt = case when icm.item_type <> 'A' then dec_item_type_ref.max_wgt end ) and ( round(a.weight/(case (a.qty*a.packing_qty) when 0 then 1 else (a.qty*a.packing_qty) end ), icm.decimal_place) >= case when icm.item_type='A' then dec_item_type_ref.min_wgt end or dec_item_type_ref.min_wgt = case when icm.item_type <> 'A' then dec_item_type_ref.min_wgt end ) LEFT JOIN dbo.dec_item_type ON dec_item_type.dec_item_type = dec_item_type_ref.dec_item_type GROUP BY #temp.[domain], #temp.[comp_code], #temp.[order_no], #temp.[sub_order_no], #temp.[job_order_no], #temp.[split_order_no], #temp.[batch_no], dec_item_type.dec_unit_price,dec_item_type_ref.dec_item_type )AS aes_Detail_Result group by [domain],[comp_code],[order_no],[sub_order_no],[job_order_no],[split_order_no],[batch_no] ) AS aes_result ON temp.[domain] = aes_result.[domain] AND temp.[comp_code] = aes_result.[comp_code] AND temp.[order_no] = aes_result.[order_no] AND temp.[sub_order_no] = aes_result.[sub_order_no] AND temp.[job_order_no] = aes_result.[job_order_no] AND temp.[split_order_no] = aes_result.[split_order_no] AND temp.[batch_no] = aes_result.[batch_no]; UPDATE #temp SET other_accessory_export_amount = other_accessory_result.other_accessory_export_amount FROM #temp AS temp INNER JOIN (select [domain],[comp_code],[order_no],[sub_order_no],[job_order_no],[split_order_no],[batch_no], sum(other_accessory_export_amount) as other_accessory_export_amount, sum(other_accessory_mark_good_value) as other_accessory_mark_good_value from ( SELECT #temp.[domain], #temp.[comp_code], #temp.[order_no], #temp.[sub_order_no], #temp.[job_order_no], #temp.[split_order_no], #temp.[batch_no], dec_other_accessory.other_type, ROUND(SUM(dec_other_accessory.weight * other_type.dec_unit_price), 2) AS other_accessory_export_amount, ROUND(SUM(dec_other_accessory.weight * other_type.dec_unit_price), 2) AS other_accessory_mark_good_value FROM #temp INNER JOIN dbo.dec_stock_type ON dec_stock_type.dec_stock_type = #temp.dec_stock_type INNER JOIN dbo.dec_hk_stock_type ON dec_hk_stock_type.dec_hk_stock_type = dec_stock_type.dec_hk_stock_type INNER JOIN dbo.dec_other_accessory ON dec_other_accessory.domain = #temp.domain AND dec_other_accessory.comp_code = #temp.comp_code AND dec_other_accessory.order_no = #temp.order_no AND dec_other_accessory.sub_order_no = #temp.sub_order_no AND dec_other_accessory.job_order_no = #temp.job_order_no AND dec_other_accessory.split_order_no = #temp.split_order_no AND dec_other_accessory.batch_no = #temp.batch_no LEFT JOIN dbo.other_type ON other_type.other_type = dec_other_accessory.other_type GROUP BY #temp.[domain], #temp.[comp_code], #temp.[order_no], #temp.[sub_order_no], #temp.[job_order_no], #temp.[split_order_no], #temp.[batch_no], dec_other_accessory.other_type )AS other_accessory_detail_result group by [domain],[comp_code],[order_no],[sub_order_no],[job_order_no],[split_order_no],[batch_no] ) AS other_accessory_result ON temp.[domain] = other_accessory_result.[domain] AND temp.[comp_code] = other_accessory_result.[comp_code] AND temp.[order_no] = other_accessory_result.[order_no] AND temp.[sub_order_no] = other_accessory_result.[sub_order_no] AND temp.[job_order_no] = other_accessory_result.[job_order_no] AND temp.[split_order_no] = other_accessory_result.[split_order_no] AND temp.[batch_no] = other_accessory_result.[batch_no]; UPDATE #temp SET export_amount_sum = t.export_amount_sum from #temp inner join ( select (SUM(total_labour_USD) + SUM(adding_labour_cost_USD) + SUM(gold_export_amount) + SUM(aes_export_amount) + SUM(other_accessory_export_amount)) as export_amount_sum, comp_code,order_no,sub_order_no,job_order_no,split_order_no,batch_no from #temp group by comp_code,order_no,sub_order_no,job_order_no,split_order_no,batch_no ) as t on #temp.comp_code = t.comp_code and #temp.order_no = t.order_no and #temp.sub_order_no = t.sub_order_no and #temp.job_order_no = t.job_order_no and #temp.split_order_no = t.split_order_no and #temp.batch_no = t.batch_no; WITH GroupedData AS ( select dec_date , ver_no , receive_district , receive_district_desc , rtrim(last_sync_by) AS last_sync_by, last_sync_date, fr.flag_desc_cn as syncFlagDesc, sum(qty) OVER(PARTITION BY dec_date, ver_no,receive_district) as qty , sum(adding_labour_cost) OVER(PARTITION BY dec_date, ver_no,receive_district) as adding_labour_cost , sum(total_labour) OVER(PARTITION BY dec_date, ver_no,receive_district) as total_labour , sum(total_weight) OVER(PARTITION BY dec_date, ver_no,receive_district) as total_weight , sum(total_labour_USD) OVER(PARTITION BY dec_date, ver_no,receive_district) as total_labour_USD , sum(total_labour_CNY) OVER(PARTITION BY dec_date, ver_no,receive_district) as total_labour_CNY , sum(adding_labour_cost_USD) OVER(PARTITION BY dec_date, ver_no,receive_district) as adding_labour_cost_USD , sum(adding_labour_cost_CNY) OVER(PARTITION BY dec_date, ver_no,receive_district) as adding_labour_cost_CNY , sum(export_amount_sum) OVER(PARTITION BY dec_date, ver_no,receive_district) as export_amount_sum , COUNT(*) OVER(PARTITION BY dec_date, ver_no,receive_district) AS total, min(sync_flag) OVER(PARTITION BY dec_date, ver_no,receive_district) as sync_flag, ROW_NUMBER() OVER( PARTITION BY dec_date, ver_no,receive_district ORDER BY last_sync_date DESC ) AS time_rank from #temp t inner join flag_reference fr on fr.flag_id = t.sync_flag and fr.flag_group = 'sync_flag' ) select dec_date , ver_no , receive_district , receive_district_desc , last_sync_by, syncFlagDesc, qty , adding_labour_cost , total_labour , total_weight , total_labour_USD , total_labour_CNY , adding_labour_cost_USD , adding_labour_cost_CNY , export_amount_sum , total, sync_flag, last_sync_date from GroupedData where time_rank = 1 OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY ; if OBJECT_ID('tempdb..#temp') is not null drop table #temp;sqlserver 会报什么错
最新发布
扫一扫
1113
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
