sql - 在查询中计算错误的金额

Question

我有一个包含一些记录的表，现在想用一些逻辑重复这个表的内容。我有两个日期开始日期和终止日期，意味着记录从 start_date 开始到终止日期结束，它会工作正常但问题是计算它的数量，逻辑是金额计算公式

basesalary / 12 * ( SUTARate / 100 ) * ( x.num+1) 

如果此金额小于 SUTAMaximumAmount，则使用此金额，否则为 0。还有一件事，如果金额将保留且年份已完成，则从明年开始重新计算。x.num 是临时表，其中包含从 1 到 90 的 90 个数字

表格

 BaseSalary|  S_Date  |  T_Date     | SUTARate| SUTAMaximumAmount |A_S_Percent
   48000   | 7-1-2013 |  3-15-2015  | 1.1     | 300               | 5

我的结果是

     DAte                     amount
2013-07-01 00:00:00.000         44
2013-08-01 00:00:00.000         44
2013-09-01 00:00:00.000         44
2013-10-01 00:00:00.000         44
2013-11-01 00:00:00.000         44
2013-12-01 00:00:00.000         44
2014-01-01 00:00:00.000         36
2014-02-01 00:00:00.000         -8
2014-03-01 00:00:00.000        -52
2014-04-01 00:00:00.000        -96
2014-05-01 00:00:00.000       -140
2014-06-01 00:00:00.000       -184
2014-07-01 00:00:00.000       -228
2014-08-01 00:00:00.000       -272
2014-09-01 00:00:00.000       -316
2014-10-01 00:00:00.000       -360
2014-11-01 00:00:00.000       -404
2014-12-01 00:00:00.000       -448
2015-01-01 00:00:00.000       -492
2015-02-01 00:00:00.000       -536
2015-03-01 00:00:00.000       -580

我想要这样的结果

Date       |  Amount
7-1-2013       44
8-1-2013       44
9-1-2013       44
10-1-2013      44
11-1-2013      44
12-1-2013      44
1-1-2014       44
2-1-2014       44
3-1-2014       44
4-1-2014       44
5-1-2014       44
6-1-2014       44
7-1-2014       36
1-1-2015       44
2-1-2015       44
3-1-2015       44

查询

SELECT dateadd(M, (x.num),d.StartDate) AS TheDate,            

Round( case when ((convert(float,d.SUTARate)/100* convert(integer,d.BaseSalary) / 12)*(x.num+1)) <=CONVERT(money,d.SUTAMaximumAmount)       
then (convert(float,d.SUTARate)/100* convert(integer,d.BaseSalary)* / 12)       
else (CONVERT(money,d.SUTAMaximumAmount)-((convert(float,d.SUTARate)/100* (convert(integer,d.BaseSalary) / 12)*x.num)))*Power((1+convert(float,d.AnnualSalaryIncreasePercent)/100),Convert(int,x.num/12)) end, 2) AS Amount,         
FROM #Table AS x,  myTbl AS d            
WHERE (x.num >= 0) AND (x.num <= (DateDiff(M, d.StartDate, d.TerminationDate)) ) 

临时表

create TABLE #Table (               
num int NOT NULL,              
);               
;WITH Nbrs ( n ) AS (              
    SELECT 0 UNION ALL              
    SELECT 1 + n FROM Nbrs WHERE n < 99 )              
    INSERT #Table(num)              
    SELECT n FROM Nbrs              
OPTION ( MAXRECURSION 99 )  

此表在上述查询中用作 x

Answer 1

我创建了 this SQLFiddle .

-- Numbers table is probably a good idea
WITH Nbrs ( num ) AS 
(              
    SELECT 0 UNION ALL              
    SELECT 1 + num FROM Nbrs WHERE num < 99 
)

-- All columns, except for 'num' come from myTbl
SELECT dateadd(M, (num),S_Date) AS TheDate,
Round( 
  CASE 
  WHEN (SUTARate / 100) * (BaseSalary / 12) <= SUTAMaximumAmount
  THEN (SUTARate / 100) * (BaseSalary / 12)
  ELSE 0
  END
, 2) As Amount
-- This may be the number you were trying to multiply
,DatePart(Month, dateadd(M, (num),S_Date)) As PotentialMultiiplier
FROM Nbrs AS x,  myTbl AS d            
WHERE (num >= 0) 
AND (num <= (DateDiff(M, S_Date, T_Date)) ) 

我不完全确定您的目标是什么，但您可能在数字表的正确轨道上。由于您要查询的结果不会随着时间的推移发生太大变化(即几乎每个月的金额为 44 美元)，因此很难确定查询的正确代码。因此，我建议您提供一组不同的数据以便更好地检查结果。

如果您修改了提供的链接中的 SQL，您可以重新发布更好的代码，然后我们可以更好地解决您的问题。