违约金 滞纳金 计算公式

Question

create table #a(userid nvarchar(12),cbtdate datetime,mete int,latefee decimal(5),paydate datetime,payyesno int) 
insert #a VALUES ('00206977','2011-09-01','30','0','2011-09-13','2')
insert #a VALUES ('00206977','2011-12-02','38','0','2011-12-13','2')
insert #a VALUES ('00206977','2012-03-01','30','0','','0')
select *  from #a
结果
当payyesno=0时latefee= 
create table #a(userid nvarchar(12),cbtdate datetime,mete int,latefee decimal(5),paydate datetime,payyesno int) 
insert #a VALUES ('00206977','2011-09-01','30','0','2011-09-13','2')
insert #a VALUES ('00206977','2011-12-02','38','0','2011-12-13','2')
insert #a VALUES ('00206977','2012-03-01','30','(getdate()-cbtdate下月1日)*mete*0.005','','0')
select *  from #a

Answer 1

cbtdate是输入数据的日期如'2012-03-01' '2012-04-01'开始收取违约金=0.005*mete*天数

Answer 2

cbtdate是输入数据的日期如'2012-03-01'也可能是'2012-03-09'但都是隔月的第一天开始收违约金

Answer 3

if object_id('tempdb..#a')is not null drop table #a
create table #a(userid nvarchar(12),cbtdate datetime,mete int,latefee decimal(5),paydate datetime,payyesno int) 
insert #a VALUES ('00206977','2011-09-01','30','0','2011-09-13','2')
insert #a VALUES ('00206977','2011-12-02','38','0','2011-12-13','2')
insert #a VALUES ('00206977','2012-02-09','30','0','','0')
违约金公式 
select ((datediff(day,convert(char(4),year(cbtdate))+'-'+convert(char(2),month(dateadd(month,1,cbtdate)))+'-1',getdate()))+1)*0.005*mete as latefee from #a where payyesno=0 and convert(char(7),cbtdate)<convert(char(7),getdate())
可以用case when then end 吗？

Answer 4

create table #a(userid nvarchar(12),cbtdate datetime,mete int,latefee decimal(18,3),paydate datetime,payyesno int)  
insert #a VALUES ('00206977','2011-09-01','30','0','2011-09-13','2')
insert #a VALUES ('00206977','2011-12-02','38','0','2011-12-13','2')
insert #a VALUES ('00206977','2012-03-01','30','0',null,'0')
goselect t.userid,
       t.cbtdate,
       t.mete,
       case when t.payyesno <> 0 then t.latefee
            else datediff(dd,getdate(),convert(varchar(8),dateadd(mm,1,cbtdate),120)+'01') * mete * 0.005
       end latefee ,
       t.paydate,
       t.payyesno    
from #a tdrop table #a/*
userid       cbtdate                                                mete        latefee              paydate                                                payyesno    
------------ ------------------------------------------------------ ----------- -------------------- ------------------------------------------------------ ----------- 
00206977     2011-09-01 00:00:00.000                                30          .000                 2011-09-13 00:00:00.000                                2
00206977     2011-12-02 00:00:00.000                                38          .000                 2011-12-13 00:00:00.000                                2
00206977     2012-03-01 00:00:00.000                                30          1.950                NULL                                                   0（所影响的行数为 3 行）
*/