Question

我使用以下代码计算一个月的保险费,这个月是9月。现行法典规定,在9月30日之前将全部所得保险费减去截至8月31日的全部收入溢价,以便单单单在9月份回扣保险费。

成果就是这样:

eprem        coveragecode  
0.0211       AD  
277970.8291  BI  
245700.6741  COLL  
86997.5694   COMP  
85.0083      CustomParts  
848.7873     Death  
0.00         ECL  
0.00         GPIP  
692.3802     Income  
2410.5513    MED  
267670.1099  PD  
387628.504   PIP  
26.8767      PU  
11736.2762   Rental  
4304.3367    Towing   
4211.2574    UIMBI  
19804.8964   UMBI  
15145.3211   UMPD

当我增加所有保险的溢价,即第13253399号保费,这是平时月的准确保费。

现在,我试图做的是,也因此而增加编码。如果在《法典》中没有任何具体规定的编码编码栏下运行以下代码,那么我得出上述结果是正确的,但是,如果加上“计算代码”一栏,即退回到预期的数千个牢房,但如果加上总保值,即达到132 3608.401美元,而不是正确的总数为13253 999美元。我认为,正在发生的情况是,在1个月(上表)和另一个月(上表)存在某些保险和保险套组合(即与我的2个衍生表格合并)。当我有权利加入时,我会收到不同数额的退款,但是,如果加上所有保费,则总是等于132 3608.401美元,而不是13233.399美元。我认为,解决我的问题的办法是,全面加入,从而恢复所有成果(甚至那些不匹配的结果),这样,我的总保费将增加到第13233.399号,但不管加入哪一类尝试使用,我还是只得到第1323608.401号。是否有任何想法可以在此讨论? 任何援助都将受到高度赞赏。感谢!

    SELECT   sept.eprem-aug.eprem as eprem , sept.coveragecode, zipcode 
from

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate =  09-30-2011  
and decpagetypecode !=  x 
GROUP BY evaluationdate ,coveragecode, zipcode

) as sept full outer join 

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate = CONVERT(DATETIME, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0, 09-30-2011 ),0)), 102)  
and decpagetypecode !=  x 
GROUP BY evaluationdate ,coveragecode, zipcode

) as aug on sept.coveragecode = aug.coveragecode and sept.zipcode = aug.zipcode
where sept.coveragecode is not null and sept.coveragecode <>   
order by coveragecode, sept.evaluationdate

Answer 1

我必须做的是,把任何无价溢价转换为0。

SELECT   isnull(sept.eprem,0)-isnull(aug.eprem,0) as eprem , sept.coveragecode, zipcode 
from

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate =  09-30-2011  
and decpagetypecode !=  x 
GROUP BY evaluationdate ,coveragecode, zipcode

) as sept full outer join 

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate = CONVERT(DATETIME, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0, 09-30-2011 ),0)), 102)  
and decpagetypecode !=  x 
GROUP BY evaluationdate ,coveragecode, zipcode

) as aug on sept.coveragecode = aug.coveragecode and sept.zipcode = aug.zipcode
where sept.coveragecode is not null and sept.coveragecode <>   
order by coveragecode, sept.evaluationdate

Answer 2

阁下的“未来”状况应如此:

SELECT   sept.eprem-aug.eprem as eprem , sept.coveragecode, zipcode 
from

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate =  09-30-2011  
and decpagetypecode !=  x 
-- Migrated WHERE Condition
AND COALESCE(eprem.coveragecode,   ) >   
GROUP BY evaluationdate ,coveragecode, zipcode

) as sept FULL OUTER join 

(select 
SUM(EarnedPremium) AS eprem,coveragecode, evaluationdate, zipcode                       
FROM         dbo.StatRateSummary                            
where evaluationdate = CONVERT(DATETIME, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0, 09-30-2011 ),0)), 102)  
and decpagetypecode !=  x 
GROUP BY evaluationdate ,coveragecode, zipcode

) as aug on sept.coveragecode = aug.coveragecode and sept.zipcode = aug.zipcode
/*
|| See SEPT derived table for elimination of NULL and    coverage codes
|| where sept.coveragecode is not null and sept.coveragecode <>   
*/
order by coveragecode, sept.evaluationdate

考虑的一个问题是,当你使用<代码>时。 OUTER JOIN. WHERE criteria , 从而使最佳机构得以重新作为INNER JOIN。

希望这一帮助。

友情链接