让我头晕的查询问题~

Question

这个公式看不太懂。你直接用业务语言描述一下。否则不好写SQL语句。

Answer 1

  在INVMAIN中小李的投资总金额是120000
  在INVSUB中2008上半年和之前有三次使用了小李的钱,分别是10000,50000,20000
  今年上半年的分红就是没有用到的钱(120000-50000-20000-10000)乘与2%
  加上每一笔投资的20%,即10000*20%+50000*20%+20000*20%,但是这三笔投资中50000和20000是不满半年的,所以要去掉不满半年的那部分
  

Answer 2

其实不难，就是麻烦！！下面供参考下--> --> (Ben)生成測試數據
 
declare @INVMain table([EMPNO] nvarchar(3),[EMPNAME] nvarchar(2),[INVSUM] int)
Insert @INVMain
select N'012',N'小李',120000 union all
select N'013',N'小张',100000
 
--Select * from @INVMain
--> --> (Ben)生成測試數據
 
declare @INVSub table([EMPNO] nvarchar(3),[EMPNAME] nvarchar(2),[INVUSED] int,[INVDATE] Datetime)
Insert @INVSub
select N'012',N'小李',10000,'2007/10/12' union all
select N'012',N'小李',50000,'2008/1/25' union all
select N'012',N'小李',20000,'2008/3/9' union all
select N'012',N'小李',30000,'2008/7/25' union all
select N'013',N'小张',60000,'2008/2/17'--Select * from @INVSubdeclare @d1 datetime,@d2 datetime,@d3 datetime
declare @n1 int,@n2 int
set @d1=getdate()-datepart(dy,getdate())+1
set @d2=dateadd(mm,6,@d1)-1
set @d3=dateadd(mm,12,@d1)-1
set @n1=datepart(dy,@d2)
set @n2=datepart(dy,@d3)select a.empno,a.empname,0.02*(a.INVSUM-b.上半年)+上半年提层 as 上半年,
0.02*(a.INVSUM-b.下半年)+下半年提层 as 下半年
from @INVMain a inner join (
select empno,sum(case when INVDATE<=@d2 and INVDATE>=@d1 then INVUSED else 0 end) 上半年,
       sum(case when INVDATE<=@d3 and INVDATE>@d2 then INVUSED else 0 end) 下半年,
       sum(case when INVDATE>=@d1 and INVDATE<=@d2 then 
            INVUSED*0.2*(@n1-datepart(dy,INVDATE))/@n1 
            when INVDATE<@d1 then INVUSED*0.2 else 0 end) 上半年提层,
       sum(case when INVDATE >@d2 and INVDATE<=@d3 then 
            INVUSED*0.2*(@n2-datepart(dy,INVDATE))/(@n2-@n1) else INVUSED*0.2 end) 下半年提层
from @INVSub
group by empno 
) b on a.empno=b.empno
012 70000 30000 13109.890109890109 21184.782608695652
013 60000 0 8835.164835164835 12000.000000000000

Answer 3

declare @d1 datetime,@d2 datetime,@d3 datetime
declare @n1 int,@n2 int
set @d1=getdate()-datepart(dy,getdate())+1
set @d2=dateadd(mm,6,@d1)-1
set @d3=dateadd(mm,12,@d1)-1
set @n1=datepart(dy,@d2)
set @n2=datepart(dy,@d3)这里主要是处理 上半年 下半年等信息 ，lz看着调整！！

Answer 4

-->生成测试数据
 
declare @INVMain table([EMPNO] nvarchar(3),[EMPNAME] nvarchar(2),[INVSUM] int)
Insert @INVMain
select N'012',N'小李',120000 union all
select N'013',N'小张',100000
--Select * from @INVMain
declare @INVSub table([EMPNO] nvarchar(3),[EMPNAME] nvarchar(2),[INVUSED] int,[INVDATE] Datetime)
Insert @INVSub
select N'012',N'小李',10000,'2007/10/12' union all
select N'012',N'小李',50000,'2008/1/25' union all
select N'012',N'小李',20000,'2008/3/9' union all
select N'012',N'小李',30000,'2008/7/25' union all
select N'013',N'小张',60000,'2008/2/17'
--Select * from @INVSubdeclare @dt datetime
set @dt = '20080701'SELECT [EMPNO],[EMPNAME],       
        ([INVSUM] - (SELECT isnull(SUM([INVUSED]),0) FROM @INVSub WHERE [EMPNO] = M.[EMPNO] AND [INVDATE] < @dt)) *0.02 
      +
       (SELECT isnull(SUM([INVUSED]),0) FROM @INVSub WHERE [EMPNO] = M.[EMPNO] AND [INVDATE] < dateadd(month,-6,@dt) )*0.2
       +
       (SELECT isnull(
                       SUM( [INVUSED]* (datediff(day,[INVDATE],@dt)+1)*1.0/datediff(day,dateadd(month,-6,@dt),@dt))
                      ,0) FROM @INVSub 
                           WHERE [EMPNO] = M.[EMPNO] AND [INVDATE] BETWEEN dateadd(month,-6,@dt) AND @dt )*0.2 AS bonus
       
FROM @INVMain M 
/*
EMPNO EMPNAME bonus
----- ------- -------------------
012   小李      14063.73626373626
013   小张      9767.03296703297
*/

Answer 5

declare @dt datetime
set @dt = '20090101'SELECT [EMPNO],[EMPNAME],       
        ([INVSUM] - (SELECT isnull(SUM([INVUSED]),0) FROM @INVSub WHERE [EMPNO] = M.[EMPNO] AND [INVDATE] < @dt)) *0.02 
      +
       (SELECT isnull(SUM([INVUSED]),0) FROM @INVSub WHERE [EMPNO] = M.[EMPNO] AND [INVDATE] < dateadd(month,-6,@dt) )*0.2
       +
       (SELECT isnull(
                       SUM( [INVUSED]* (datediff(day,[INVDATE],@dt)+1)*1.0/datediff(day,dateadd(month,-6,@dt),@dt))
                      ,0) FROM @INVSub 
                           WHERE [EMPNO] = M.[EMPNO] AND [INVDATE] BETWEEN dateadd(month,-6,@dt) AND @dt )*0.2 AS bonus
       
FROM @INVMain M 
/*
EMPNO EMPNAME bonus
----- ------- -------------------
012   小李      21450.00000000000
013   小张      12800.00000000000
*/

Answer 6

--简化一下 :)
declare @dt datetime
set @dt = '20090101'
SELECT [EMPNO],[EMPNAME],       
        ([INVSUM] - (SELECT isnull(SUM([INVUSED]),0) FROM @INVSub WHERE [EMPNO] = M.[EMPNO] AND [INVDATE] < @dt)) *0.02 
      +
       (SELECT ISNULL( SUM( [INVUSED]* CASE WHEN [INVDATE]< dateadd(month,-6,@dt) THEN 1.0 ELSE (datediff(day,[INVDATE],@dt)+1)*1.0/datediff(day,dateadd(month,-6,@dt),@dt) END) ,0)                                        
                       FROM @INVSub WHERE [EMPNO] = M.[EMPNO] AND [INVDATE] < @dt )*0.2 AS bonus
       
FROM @INVMain M 
/*
EMPNO EMPNAME bonus
----- ------- ---------------------------------------
012   小李      21450.00000000000
013   小张      12800.00000000000
*/