Question

我要问一下的是,我要将税收额从A10501的细目上分配。这一工作,但我想在更新的最后一段中增加任何剩余数额。

考虑A20601,共计14.14美元,需要在A10501中分四个项目。每条线路如果平均分布,就会达到3.535,但我希望更新的是:

Line 1: 3.53
Line 2: 3.53
Line 3: 3.53
Line 4: 3.55 = total of 14.14

这里,我要问:

-- Replace  your_invcnmbr_value  with the specific INVCNMBR you want to allocate
DECLARE @target_invcnmbr NVARCHAR(50) =  INV001 ;

-- Create a local temporary table to store the total amount and row count from A20601
CREATE TABLE #temp_table (
    RowNum INT,
    INVCNMBR NVARCHAR(50),
    SumTXDTLAMT DECIMAL(18, 2),
    rowz INT
);

INSERT INTO #temp_table (RowNum, INVCNMBR, SumTXDTLAMT, rowz)
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
    INVCNMBR,
    ROUND(SUM(TXDTLAMT), 2) AS SumTXDTLAMT,
    COUNT(*) AS rowz
FROM
    A20601
WHERE
    INVCNMBR = @target_invcnmbr
GROUP BY
    INVCNMBR;

-- Update A10501 table
UPDATE A10501
SET
FEETXAMT = ROUND(FEETXAMT + (t.SumTXDTLAMT / t.rowz) + 
             CASE WHEN t.RowNum = t.rowz THEN t.SumTXDTLAMT % t.rowz ELSE 0 END, 2),
OTFTXAMT = ROUND(OTFTXAMT + (t.SumTXDTLAMT / t.rowz) +
             CASE WHEN t.RowNum = t.rowz THEN t.SumTXDTLAMT % t.rowz ELSE 0 END, 2)
FROM
P10501 p
JOIN #temp_table t ON p.INVCNMBR = t.INVCNMBR
WHERE
p.INVCNMBR = @target_invcnmbr;

-- Drop the temporary table when done
DROP TABLE #temp_table;

我想有两句子来更新表格,其余部分在更新的最后一行中增加。现在,它将产生三个错误,我们不想这样做。

Answer 1

To allocate the tax amount over the lines in A10501 while ensuring two decimal places and adding any remainder to the last line, you can modify your query as follows:

-- Replace  your_invcnmbr_value  with the specific INVCNMBR you want to allocate
DECLARE @target_invcnmbr NVARCHAR(50) =  INV001 ;

-- Create a local temporary table to store the total amount and row count from A20601
CREATE TABLE #temp_table (
    RowNum INT,
    INVCNMBR NVARCHAR(50),
    SumTXDTLAMT DECIMAL(18, 2),
    rowz INT
);

INSERT INTO #temp_table (RowNum, INVCNMBR, SumTXDTLAMT, rowz)
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
    INVCNMBR,
    SUM(TXDTLAMT) AS SumTXDTLAMT,
    COUNT(*) AS rowz
FROM
    A20601
WHERE
    INVCNMBR = @target_invcnmbr
GROUP BY
    INVCNMBR;

-- Calculate the total tax amount and the remainder
DECLARE @total_tax DECIMAL(18, 2), @remainder DECIMAL(18, 2);

SELECT @total_tax = SumTXDTLAMT FROM #temp_table WHERE RowNum = 1;
SELECT @remainder = @total_tax % rowz FROM #temp_table WHERE RowNum = rowz;

-- Update A10501 table
UPDATE A10501
SET
    FEETXAMT = ROUND(FEETXAMT + (SumTXDTLAMT / rowz) + 
                 CASE WHEN RowNum = rowz THEN @remainder ELSE 0 END, 2),
    OTFTXAMT = ROUND(OTFTXAMT + (SumTXDTLAMT / rowz) +
                 CASE WHEN RowNum = rowz THEN @remainder ELSE 0 END, 2)
FROM
    P10501 p
JOIN #temp_table t ON p.INVCNMBR = t.INVCNMBR
WHERE
    p.INVCNMBR = @target_invcnmbr;

-- Drop the temporary table when done
DROP TABLE #temp_table;

友情链接