PiolotTH_ReportFrom/派诺报表SQL文件/应收对账表(简易)更新数据.sql

ALTER PROCEDURE UpdateYSDZBJY_GZTH            
AS            
BEGIN            
          
TRUNCATE TABLE YingShouZhanKuanJY_GZTH          
          
INSERT INTO YingShouZhanKuanJY_GZTH(FID,FSaleOrgId,FSalerId,[DATE],ContractNumber,ProjectName,OrderAmount,FALLAmountFor,PaymentAmount,ShippedDebt,InvoicedDebt,OwedTickets,IsEnded,Remark,[K3רƱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[K3<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],ZPFALLAmountFor,PPFALLAmountFor
,[K3<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[K3<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[K3<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[K3<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD>ǿշ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD>ǿշ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD>ǿ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],[<EFBFBD>ǿ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],ShipmentsAmount,FBillAllAmount)          
SELECT FID,FSaleOrgId,FSalerId,[Date],CONTRACTNUMBER,ProjectName,SUM(OrderAmount) OrderAmount,SUM(BillingAmount) BillingAmount,SUM(PaymentAmount) PaymentAmount,SUM(ShippedDebt) ShippedDebt
,SUM(InvoicedDebt) InvoicedDebt,SUM(OwedTickets) OwedTickets,null,Remark,SUM([K3רƱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [K3רƱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM([K3<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [K3<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM(ZPBillingAmount) [רƱ<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM(PPBillingAmount) [<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>] 
,SUM([K3<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [K3<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM([K3<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [K3<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM([K3<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [K3<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM([K3<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [K3<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM([<EFBFBD>ǿշ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [<EFBFBD>ǿշ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM([<EFBFBD>ǿշ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [<EFBFBD>ǿշ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM([<EFBFBD>ǿ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [<EFBFBD>ǿ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>],SUM([<EFBFBD>ǿ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>]) [<EFBFBD>ǿ<EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] 
,SUM(ShipmentsAmount) <EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,SUM(ReturnAmount) <EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>   
FROM YingShouZhanKuanMX_GZTH  
WHERE Number <> '<EFBFBD>ϼ<EFBFBD>' OR Number IS NULL
GROUP BY FID,FSaleOrgId,FSalerId,[Date],CONTRACTNUMBER,ProjectName,Remark
          
UPDATE YingShouZhanKuanJY_GZTH SET IsEnded= b.IsEnded
FROM (SELECT DISTINCT CONTRACTNUMBER,IsEnded FROM YingShouZhanKuanMX_GZTH aa 
 WHERE IsEnded IS NOT NULL) b
 WHERE  b.CONTRACTNUMBER=YingShouZhanKuanJY_GZTH.CONTRACTNUMBER

----<EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD>δ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݽ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>          
--select FID,sum(shipmentsamount) as '<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',          
--sum(ReturnAmount) AS '<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',          
--SUM(PPBillingAmount) AS '<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',          
--SUM(ZPBillingAmount) AS 'רƱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>'          
--INTO #TEMP1          
--from yingshouzhankuanmx_gzth          
--group by fid          
          
--UPDATE A          
--SET A.ShipmentsAmount = B.<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,          
--A.FBillAllAmount = B.<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,          
--A.PPFALLAmountFor = B.<EFBFBD><EFBFBD>Ʊ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,          
--A.ZPFALLAmountFor = B.רƱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>          
--FROM YingShouZhanKuanJY_GZTH A          
--INNER JOIN #TEMP1 B ON A.FID = B.FID          
                                
SELECT        
*,           
ROW_NUMBER() OVER (PARTITION BY FID ORDER BY FID) as row_num1 
INTO #CFTEMP1
FROM YingShouZhanKuanJY_GZTH          

UPDATE A
SET A.row_num = B.row_num1
FROM YingShouZhanKuanJY_GZTH A
INNER JOIN #CFTEMP1 B On A.FID = B.FID 

          
DELETE FROM YingShouZhanKuanJY_GZTH          
WHERE row_num <> 1; 

UPDATE A
SET A.Number = B.row_num1
FROM YingShouZhanKuanJY_GZTH A
INNER JOIN (
	SELECT        
	FID,           
	ROW_NUMBER() OVER (ORDER BY [DATE] ,[FID]) as row_num1 
	FROM YingShouZhanKuanJY_GZTH  
	) B On A.FID = B.FID 
    
      
--<EFBFBD><EFBFBD><EFBFBD><EFBFBD>רƱ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ʊ<EFBFBD>Ŀ<EFBFBD>Ʊʱ<EFBFBD><EFBFBD>          
UPDATE A          
SET A.PPBillingDate = B.PPBillingDate,A.ZPBillingDate = B.ZPBillingDate          
FROM YingShouZhanKuanJY_GZTH A          
INNER JOIN YingShouZhanKuanMX_GZTH B ON A.FID = B.FID          
                       
--DROP TABLE #TEMP1          
        
END