sql语句数据运算？

Question

我想实现一种运算，表中有staff_id(编号),staff_time(日期),staff_status(状态),staff_hours(小时)这些字段，
staff_status中每个月最多有两种状态（'FT','PT'）FT为全职，PT为临时工，
一个员工在这个月有可能为PT转到FT，这样的话他的工资就是当前月工资，加上上月21号之后，staff_hours为每天上班时间，我就是想实现累加上班时间。
怎样用SQL语句实现判断，在根据判断结果采用哪种方式累加？
    有三种情况，如果这个(9)月和上个(8)月状态都为PT，9月份累加方式是21/8-20/9,
                如果这个(9)月为PT->FT，上个(8)月状态都为PT，9月份累加方式是21/8-30/9,
                其他就是累加01/9-30/9(这个月的)

Answer 1

staff_id   staff_time     staff_stasus  hours
HK5901 2011-08-01 00:00:00.000 PT 11.00
HK5901 2011-08-02 00:00:00.000 PT 11.00
HK5901 2011-08-03 00:00:00.000 PT 11.00
HK5901 2011-08-04 00:00:00.000 PT 11.00
HK5901 2011-08-05 00:00:00.000 PT 11.00
HK5901 2011-08-06 00:00:00.000 PT 11.00
HK5901 2011-08-07 00:00:00.000 PT 11.00
HK5901 2011-08-08 00:00:00.000 PT 11.00
HK5901 2011-08-09 00:00:00.000 PT 11.00
HK5901 2011-08-10 00:00:00.000 FT 11.00
HK5901 2011-08-11 00:00:00.000 FT 11.00
HK5901 2011-08-12 00:00:00.000 FT 0.00
HK5901 2011-08-13 00:00:00.000 FT 11.00
HK5901 2011-08-14 00:00:00.000 FT 11.00
HK5901 2011-08-15 00:00:00.000 FT 11.00
HK5901 2011-08-16 00:00:00.000 FT 11.00
HK5901 2011-08-17 00:00:00.000 FT 11.00
HK5901 2011-08-18 00:00:00.000 FT 11.00
HK5901 2011-08-19 00:00:00.000 FT 11.00
HK5901 2011-08-20 00:00:00.000 FT 11.00
HK5901 2011-08-21 00:00:00.000 FT 11.00
HK5901 2011-08-22 00:00:00.000 FT 11.00
HK5901 2011-08-23 00:00:00.000 FT 11.00
HK5901 2011-08-24 00:00:00.000 FT 11.00
HK5901 2011-08-25 00:00:00.000 FT 11.00
HK5901 2011-08-26 00:00:00.000 FT 0.00
HK5901 2011-08-27 00:00:00.000 FT 10.30
HK5901 2011-08-28 00:00:00.000 FT 9.00
HK5901 2011-08-29 00:00:00.000 FT 11.00
HK5901 2011-08-30 00:00:00.000 FT 11.00
HK5901 2011-08-31 00:00:00.000 FT 11.00
HK5901 2011-09-01 00:00:00.000 FT 11.00
HK5901 2011-09-02 00:00:00.000 FT 11.00
HK5901 2011-09-03 00:00:00.000 FT 11.00
HK5901 2011-09-04 00:00:00.000 FT 9.75
HK5901 2011-09-05 00:00:00.000 FT 11.00
HK5901 2011-09-06 00:00:00.000 FT 6.75
HK5901 2011-09-07 00:00:00.000 FT 11.00
HK5901 2011-09-08 00:00:00.000 FT 11.00
HK5901 2011-09-09 00:00:00.000 FT 0.00
HK5901 2011-09-10 00:00:00.000 FT 11.00
HK5901 2011-09-11 00:00:00.000 FT 11.00
HK5901 2011-09-12 00:00:00.000 FT 11.00
HK5901 2011-09-13 00:00:00.000 FT 9.50
HK5901 2011-09-14 00:00:00.000 FT 11.00
HK5901 2011-09-15 00:00:00.000 FT 11.00
HK5901 2011-09-16 00:00:00.000 FT 0.00
HK5901 2011-09-17 00:00:00.000 FT 11.00
HK5901 2011-09-18 00:00:00.000 FT 11.00
HK5901 2011-09-19 00:00:00.000 FT 11.00
HK5901 2011-09-20 00:00:00.000 FT 10.00
HK5901 2011-09-21 00:00:00.000 FT 11.00
HK5901 2011-09-22 00:00:00.000 FT 11.00
HK5901 2011-09-23 00:00:00.000 FT 0.00
HK5901 2011-09-24 00:00:00.000 FT 11.00
HK5901 2011-09-25 00:00:00.000 FT 8.75
HK5901 2011-09-26 00:00:00.000 FT 11.00
HK5901 2011-09-27 00:00:00.000 FT 11.00
HK5901 2011-09-28 00:00:00.000 FT 11.00
HK5901 2011-09-29 00:00:00.000 FT 4.00
HK5901 2011-09-30 00:00:00.000 FT 0.00

Answer 2

有几个问题：
1.是不是只可能PT->FT，不可能FT->PT？
2.是不是现在的情况是10月1号来统计9月工作时间，还是说10月1号来统计一个8月21进公司的PT员工的工作时间，而不是所有员工？

Answer 3

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([staff_id] nvarchar(6),[staff_time] Datetime,[staff_stasus] nvarchar(2),[hours] decimal(18,2))
Insert #T
select N'HK5901','2011-08-01',N'PT',11.00 union all
select N'HK5901','2011-08-02',N'PT',11.00 union all
select N'HK5901','2011-08-03',N'PT',11.00 union all
select N'HK5901','2011-08-04',N'PT',11.00 union all
select N'HK5901','2011-08-05',N'PT',11.00 union all
select N'HK5901','2011-08-06',N'PT',11.00 union all
select N'HK5901','2011-08-07',N'PT',11.00 union all
select N'HK5901','2011-08-08',N'PT',11.00 union all
select N'HK5901','2011-08-09',N'PT',11.00 union all
select N'HK5901','2011-08-10',N'FT',11.00 union all
select N'HK5901','2011-08-11',N'FT',11.00 union all
select N'HK5901','2011-08-12',N'FT',0.00 union all
select N'HK5901','2011-08-13',N'FT',11.00 union all
select N'HK5901','2011-08-14',N'FT',11.00 union all
select N'HK5901','2011-08-15',N'FT',11.00 union all
select N'HK5901','2011-08-16',N'FT',11.00 union all
select N'HK5901','2011-08-17',N'FT',11.00 union all
select N'HK5901','2011-08-18',N'FT',11.00 union all
select N'HK5901','2011-08-19',N'FT',11.00 union all
select N'HK5901','2011-08-20',N'FT',11.00 union all
select N'HK5901','2011-08-21',N'FT',11.00 union all
select N'HK5901','2011-08-22',N'FT',11.00 union all
select N'HK5901','2011-08-23',N'FT',11.00 union all
select N'HK5901','2011-08-24',N'FT',11.00 union all
select N'HK5901','2011-08-25',N'FT',11.00 union all
select N'HK5901','2011-08-26',N'FT',0.00 union all
select N'HK5901','2011-08-27',N'FT',10.30 union all
select N'HK5901','2011-08-28',N'FT',9.00 union all
select N'HK5901','2011-08-29',N'FT',11.00 union all
select N'HK5901','2011-08-30',N'FT',11.00 union all
select N'HK5901','2011-08-31',N'FT',11.00 union all
select N'HK5901','2011-09-01',N'FT',11.00 union all
select N'HK5901','2011-09-02',N'FT',11.00 union all
select N'HK5901','2011-09-03',N'FT',11.00 union all
select N'HK5901','2011-09-04',N'FT',9.75 union all
select N'HK5901','2011-09-05',N'FT',11.00 union all
select N'HK5901','2011-09-06',N'FT',6.75 union all
select N'HK5901','2011-09-07',N'FT',11.00 union all
select N'HK5901','2011-09-08',N'FT',11.00 union all
select N'HK5901','2011-09-09',N'FT',0.00 union all
select N'HK5901','2011-09-10',N'FT',11.00 union all
select N'HK5901','2011-09-11',N'FT',11.00 union all
select N'HK5901','2011-09-12',N'FT',11.00 union all
select N'HK5901','2011-09-13',N'FT',9.50 union all
select N'HK5901','2011-09-14',N'FT',11.00 union all
select N'HK5901','2011-09-15',N'FT',11.00 union all
select N'HK5901','2011-09-16',N'FT',0.00 union all
select N'HK5901','2011-09-17',N'FT',11.00 union all
select N'HK5901','2011-09-18',N'FT',11.00 union all
select N'HK5901','2011-09-19',N'FT',11.00 union all
select N'HK5901','2011-09-20',N'FT',10.00 union all
select N'HK5901','2011-09-21',N'FT',11.00 union all
select N'HK5901','2011-09-22',N'FT',11.00 union all
select N'HK5901','2011-09-23',N'FT',0.00 union all
select N'HK5901','2011-09-24',N'FT',11.00 union all
select N'HK5901','2011-09-25',N'FT',8.75 union all
select N'HK5901','2011-09-26',N'FT',11.00 union all
select N'HK5901','2011-09-27',N'FT',11.00 union all
select N'HK5901','2011-09-28',N'FT',11.00 union all
select N'HK5901','2011-09-29',N'FT',4.00 union all
select N'HK5901','2011-09-30',N'FT',0.00
Go
DECLARE @dt DATETIME
SET @dt='2011-09-21';WITH b
AS
(
--如果这个(9)月和上个(8)月状态都为PT，9月份累加方式是21/8-20/9
Select 
[staff_id],
SUM([hours]) AS [hours] ,
TypeID=1--標識計算方法
from #T AS a
WHERE  EXISTS(SELECT 1 FROM #T WHERE [staff_id]=a.[staff_id] AND DATEDIFF(m,@dt,[staff_time])>=-1 HAVING COUNT(DISTINCT [staff_stasus])=1)
AND [staff_stasus]='PT'
AND [staff_time] >= DATEADD(m,-1,@dt) AND [staff_time]<@dt
GROUP BY [staff_id]UNION ALL
--  如果这个(9)月为PT->FT，上个(8)月状态都为PT，9月份累加方式是21/8-30/9,
Select 
[staff_id],
SUM([hours]) AS [hours],
TypeID=2--標識計算方法 
from #T AS a
WHERE EXISTS(SELECT 1 FROM #T WHERE [staff_id]=a.[staff_id] AND DATEDIFF(m,@dt,[staff_time])>=-1 HAVING COUNT(DISTINCT [staff_stasus])>1)
AND [staff_time] >= DATEADD(m,-1,@dt) AND [staff_time]<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY [staff_id]
)
SELECT * FROM b
UNION ALL
--其他就是累加01/9-30/9(这个月的)
SELECT [staff_id],
SUM([hours]) AS [hours], 
TypeID=3
FROM #T AS a 
WHERE NOT EXISTS(SELECT * FROM b WHERE staff_id=a.staff_id) 
AND [staff_time]>=@dt-DAY(@dt)+1 AND [staff_time]<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY [staff_id]/*
staff_id hours TypeID
HK5901 376.05 2
*/

Answer 4

 如果这个(9)月和上个(8)月状态都为PT，9月份累加方式是21/8-20/9,
  如果这个(9)月为PT->FT，上个(8)月状态都为PT，9月份累加方式是21/8-30/9,
----判斷日期範圍是以上月21到這月20號時這樣改use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([staff_id] nvarchar(6),[staff_time] Datetime,[staff_stasus] nvarchar(2),[hours] decimal(18,2))
Insert #T
select N'HK5901','2011-08-01',N'PT',11.00 union all
select N'HK5901','2011-08-02',N'PT',11.00 union all
select N'HK5901','2011-08-03',N'PT',11.00 union all
select N'HK5901','2011-08-04',N'PT',11.00 union all
select N'HK5901','2011-08-05',N'PT',11.00 union all
select N'HK5901','2011-08-06',N'PT',11.00 union all
select N'HK5901','2011-08-07',N'PT',11.00 union all
select N'HK5901','2011-08-08',N'PT',11.00 union all
select N'HK5901','2011-08-09',N'PT',11.00 union all
select N'HK5901','2011-08-10',N'FT',11.00 union all
select N'HK5901','2011-08-11',N'FT',11.00 union all
select N'HK5901','2011-08-12',N'FT',0.00 union all
select N'HK5901','2011-08-13',N'FT',11.00 union all
select N'HK5901','2011-08-14',N'FT',11.00 union all
select N'HK5901','2011-08-15',N'FT',11.00 union all
select N'HK5901','2011-08-16',N'FT',11.00 union all
select N'HK5901','2011-08-17',N'FT',11.00 union all
select N'HK5901','2011-08-18',N'FT',11.00 union all
select N'HK5901','2011-08-19',N'FT',11.00 union all
select N'HK5901','2011-08-20',N'FT',11.00 union all
select N'HK5901','2011-08-21',N'FT',11.00 union all
select N'HK5901','2011-08-22',N'FT',11.00 union all
select N'HK5901','2011-08-23',N'FT',11.00 union all
select N'HK5901','2011-08-24',N'FT',11.00 union all
select N'HK5901','2011-08-25',N'FT',11.00 union all
select N'HK5901','2011-08-26',N'FT',0.00 union all
select N'HK5901','2011-08-27',N'FT',10.30 union all
select N'HK5901','2011-08-28',N'FT',9.00 union all
select N'HK5901','2011-08-29',N'FT',11.00 union all
select N'HK5901','2011-08-30',N'FT',11.00 union all
select N'HK5901','2011-08-31',N'FT',11.00 union all
select N'HK5901','2011-09-01',N'FT',11.00 union all
select N'HK5901','2011-09-02',N'FT',11.00 union all
select N'HK5901','2011-09-03',N'FT',11.00 union all
select N'HK5901','2011-09-04',N'FT',9.75 union all
select N'HK5901','2011-09-05',N'FT',11.00 union all
select N'HK5901','2011-09-06',N'FT',6.75 union all
select N'HK5901','2011-09-07',N'FT',11.00 union all
select N'HK5901','2011-09-08',N'FT',11.00 union all
select N'HK5901','2011-09-09',N'FT',0.00 union all
select N'HK5901','2011-09-10',N'FT',11.00 union all
select N'HK5901','2011-09-11',N'FT',11.00 union all
select N'HK5901','2011-09-12',N'FT',11.00 union all
select N'HK5901','2011-09-13',N'FT',9.50 union all
select N'HK5901','2011-09-14',N'FT',11.00 union all
select N'HK5901','2011-09-15',N'FT',11.00 union all
select N'HK5901','2011-09-16',N'FT',0.00 union all
select N'HK5901','2011-09-17',N'FT',11.00 union all
select N'HK5901','2011-09-18',N'FT',11.00 union all
select N'HK5901','2011-09-19',N'FT',11.00 union all
select N'HK5901','2011-09-20',N'FT',10.00 union all
select N'HK5901','2011-09-21',N'FT',11.00 union all
select N'HK5901','2011-09-22',N'FT',11.00 union all
select N'HK5901','2011-09-23',N'FT',0.00 union all
select N'HK5901','2011-09-24',N'FT',11.00 union all
select N'HK5901','2011-09-25',N'FT',8.75 union all
select N'HK5901','2011-09-26',N'FT',11.00 union all
select N'HK5901','2011-09-27',N'FT',11.00 union all
select N'HK5901','2011-09-28',N'FT',11.00 union all
select N'HK5901','2011-09-29',N'FT',4.00 union all
select N'HK5901','2011-09-30',N'FT',0.00
Go
DECLARE @dt DATETIME
SET @dt='2011-09-21';WITH b
AS
(
--如果这个(9)月和上个(8)月状态都为PT，9月份累加方式是21/8-20/9
Select 
[staff_id],
SUM([hours]) AS [hours] ,
TypeID=1--標識計算方法
from #T AS a
WHERE  EXISTS(SELECT 1 FROM #T WHERE [staff_id]=a.[staff_id] AND [staff_time]>=DATEADD(m,-1,@dt) AND [staff_time]<@dt HAVING COUNT(DISTINCT [staff_stasus])=1)
AND [staff_stasus]='PT'
AND [staff_time] >= DATEADD(m,-1,@dt) AND [staff_time]<@dt
GROUP BY [staff_id]UNION ALL
--  如果这个(9)月为PT->FT，上个(8)月状态都为PT，9月份累加方式是21/8-30/9,
Select 
[staff_id],
SUM([hours]) AS [hours],
TypeID=2--標識計算方法 
from #T AS a
WHERE EXISTS(SELECT 1 FROM #T WHERE [staff_id]=a.[staff_id] AND [staff_time]>=DATEADD(m,-1,@dt) AND [staff_time]<@dt  HAVING COUNT(DISTINCT [staff_stasus])>1)
AND [staff_time] >= DATEADD(m,-1,@dt) AND [staff_time]<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY [staff_id]
)
SELECT * FROM b
UNION ALL
--其他就是累加01/9-30/9(这个月的)
SELECT [staff_id],
SUM([hours]) AS [hours], 
TypeID=3
FROM #T AS a 
WHERE NOT EXISTS(SELECT * FROM b WHERE staff_id=a.staff_id) 
AND [staff_time]>=@dt-DAY(@dt)+1 AND [staff_time]<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY [staff_id]/*
staff_id hours TypeID
HK5901 268.75 3
*/

Answer 5

一条SQL语句可以实现吗？正常只统计当月的，如果这月是PT->FT就要把上个月的21号之后一起算在这个月，还有就是上个月不能只是FT状态

Answer 6

把條件列清楚，以上有注釋改改就行了
這一段是改日期的判斷
EXISTS(SELECT 1 FROM #T WHERE [staff_id]=a.[staff_id] AND [staff_time]>=DATEADD(m,-1,@dt) AND [staff_time]<@dt  HAVING COUNT(DISTINCT [staff_stasus])>1)

Answer 7

楼上的roy_88，太感谢你啦！

Answer 8

还有一列，统计Leave(假期)天数，方式跟那差不多，但是，PT状态的Leave全忽略，Leave(假期)里面的状态除了ND和XX还有空的，状态不为PT，全部统计