SQL如下:
Select
'PT->FT' AS Staff_Status,
a.Staff_ID,
CONVERT(CHAR(12),DATEADD(m,-1,@dt),3)+'- '+CONVERT(CHAR(12),(@dt-DAY(@dt)),3) AS Date,
(SELECT SUM(e.Confirmed_Hours) FROM dbo.Attendance_Reports e
WHERE a.Staff_ID=e.Staff_ID
AND e.EffectiveDate>=DATEADD(m,-1,@dt) AND e.EffectiveDate< @dt-DAY(@dt)+1) AS Hours,
(SELECT COUNT(c.Day_Type) FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=DATEADD(m,-1,@dt) AND c.EffectiveDate< @dt-DAY(@dt)+1) AS Leave,
'PT' AS Status
from dbo.Attendance_Reports AS a
WHERE (SELECT TOP 1 b.Staff_Status FROM dbo.Attendance_Reports b WHERE b.Staff_ID=a.Staff_ID AND b.EffectiveDate>=DATEADD(m,-1,@dt) AND b.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
ORDER BY b.EffectiveDate ASC)='PT'
AND a.EffectiveDate >= DATEADD(m,-1,@dt) AND a.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY a.Staff_ID HAVING COUNT(DISTINCT a.Staff_Status)>1
---------01/09-30/09 Date---------
UNION ALL
Select
'PT->FT' AS Staff_Status,
a.Staff_ID,
CONVERT(CHAR(12),(@dt-DAY(@dt)+1),3)+'- '+CONVERT(CHAR(12),DATEADD(m,1,(@dt-DAY(@dt))),3) AS Date,
(SELECT FTCHTotal+PTCH0120 FROM dbo.SMW_Consolidate
WHERE [Month]=MONTH(@dt) AND [Year]=YEAR(@dt)
AND Staff_no=a.Staff_ID
AND Re<>'Part Time') AS Hours,
CAST((SELECT COUNT(c.Day_Type) FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID -- AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=@dt-DAY(@dt)+1 AND c.EffectiveDate<=(
ISNULL((SELECT TOP 1 ResignDate
FROM dbo.Attendance_HRM_Excel
WHERE ResignDate IS NOT NULL
AND ResignDate<DATEADD(m,1,@dt-DAY(@dt))
AND StaffNo=c.Staff_ID
ORDER BY ResignDate),DATEADD(m,1,@dt-DAY(@dt)))
)
AND c.Day_Type NOT LIKE 'H%' AND c.Day_Type<>'ND')+
(SELECT COUNT(c.Day_Type)*0.5 FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID -- AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=@dt-DAY(@dt)+1 AND c.EffectiveDate<=(
ISNULL((SELECT TOP 1 ResignDate
FROM dbo.Attendance_HRM_Excel
WHERE ResignDate IS NOT NULL
AND ResignDate<DATEADD(m,1,@dt-DAY(@dt))
AND StaffNo=c.Staff_ID
ORDER BY ResignDate),DATEADD(m,1,@dt-DAY(@dt)))
)
AND c.Day_Type LIKE 'H%') AS INT) AS Leave,
'FT' AS Status
from dbo.Attendance_Reports AS a
WHERE (SELECT top 1 b.Staff_Status FROM dbo.Attendance_Reports b WHERE b.Staff_ID=a.Staff_ID AND b.EffectiveDate>=DATEADD(m,-1,@dt) AND b.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
ORDER BY b.EffectiveDate ASC)='PT'
AND a.EffectiveDate >= DATEADD(m,-1,@dt) AND a.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY a.Staff_ID HAVING COUNT(DISTINCT a.Staff_Status)>1?谁知道怎么优化?详情?
Select
'PT->FT' AS Staff_Status,
a.Staff_ID,
CONVERT(CHAR(12),DATEADD(m,-1,@dt),3)+'- '+CONVERT(CHAR(12),(@dt-DAY(@dt)),3) AS Date,
(SELECT SUM(e.Confirmed_Hours) FROM dbo.Attendance_Reports e
WHERE a.Staff_ID=e.Staff_ID
AND e.EffectiveDate>=DATEADD(m,-1,@dt) AND e.EffectiveDate< @dt-DAY(@dt)+1) AS Hours,
(SELECT COUNT(c.Day_Type) FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=DATEADD(m,-1,@dt) AND c.EffectiveDate< @dt-DAY(@dt)+1) AS Leave,
'PT' AS Status
from dbo.Attendance_Reports AS a
WHERE (SELECT TOP 1 b.Staff_Status FROM dbo.Attendance_Reports b WHERE b.Staff_ID=a.Staff_ID AND b.EffectiveDate>=DATEADD(m,-1,@dt) AND b.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
ORDER BY b.EffectiveDate ASC)='PT'
AND a.EffectiveDate >= DATEADD(m,-1,@dt) AND a.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY a.Staff_ID HAVING COUNT(DISTINCT a.Staff_Status)>1
---------01/09-30/09 Date---------
UNION ALL
Select
'PT->FT' AS Staff_Status,
a.Staff_ID,
CONVERT(CHAR(12),(@dt-DAY(@dt)+1),3)+'- '+CONVERT(CHAR(12),DATEADD(m,1,(@dt-DAY(@dt))),3) AS Date,
(SELECT FTCHTotal+PTCH0120 FROM dbo.SMW_Consolidate
WHERE [Month]=MONTH(@dt) AND [Year]=YEAR(@dt)
AND Staff_no=a.Staff_ID
AND Re<>'Part Time') AS Hours,
CAST((SELECT COUNT(c.Day_Type) FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID -- AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=@dt-DAY(@dt)+1 AND c.EffectiveDate<=(
ISNULL((SELECT TOP 1 ResignDate
FROM dbo.Attendance_HRM_Excel
WHERE ResignDate IS NOT NULL
AND ResignDate<DATEADD(m,1,@dt-DAY(@dt))
AND StaffNo=c.Staff_ID
ORDER BY ResignDate),DATEADD(m,1,@dt-DAY(@dt)))
)
AND c.Day_Type NOT LIKE 'H%' AND c.Day_Type<>'ND')+
(SELECT COUNT(c.Day_Type)*0.5 FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID -- AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=@dt-DAY(@dt)+1 AND c.EffectiveDate<=(
ISNULL((SELECT TOP 1 ResignDate
FROM dbo.Attendance_HRM_Excel
WHERE ResignDate IS NOT NULL
AND ResignDate<DATEADD(m,1,@dt-DAY(@dt))
AND StaffNo=c.Staff_ID
ORDER BY ResignDate),DATEADD(m,1,@dt-DAY(@dt)))
)
AND c.Day_Type LIKE 'H%') AS INT) AS Leave,
'FT' AS Status
from dbo.Attendance_Reports AS a
WHERE (SELECT top 1 b.Staff_Status FROM dbo.Attendance_Reports b WHERE b.Staff_ID=a.Staff_ID AND b.EffectiveDate>=DATEADD(m,-1,@dt) AND b.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
ORDER BY b.EffectiveDate ASC)='PT'
AND a.EffectiveDate >= DATEADD(m,-1,@dt) AND a.EffectiveDate<DATEADD(m,1,@dt-DAY(@dt)+1)
GROUP BY a.Staff_ID HAVING COUNT(DISTINCT a.Staff_Status)>1?谁知道怎么优化?详情?
CAST((SELECT COUNT(c.Day_Type) FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID -- AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=@dt-DAY(@dt)+1 AND c.EffectiveDate<=(
ISNULL((SELECT TOP 1 ResignDate
FROM dbo.Attendance_HRM_Excel
WHERE ResignDate IS NOT NULL
AND ResignDate<DATEADD(m,1,@dt-DAY(@dt))
AND StaffNo=c.Staff_ID
ORDER BY ResignDate),DATEADD(m,1,@dt-DAY(@dt)))
)
AND c.Day_Type NOT LIKE 'H%' AND c.Day_Type<>'ND')+
(SELECT COUNT(c.Day_Type)*0.5 FROM dbo.Attendance_Reports c
WHERE a.Staff_ID=c.Staff_ID -- AND c.Staff_Status NOT IN ('PT')
AND c.EffectiveDate>=@dt-DAY(@dt)+1 AND c.EffectiveDate<=(
ISNULL((SELECT TOP 1 ResignDate
FROM dbo.Attendance_HRM_Excel
WHERE ResignDate IS NOT NULL
AND ResignDate<DATEADD(m,1,@dt-DAY(@dt))
AND StaffNo=c.Staff_ID
ORDER BY ResignDate),DATEADD(m,1,@dt-DAY(@dt)))
)
AND c.Day_Type LIKE 'H%') AS INT) AS Leave,
我有一个表A,里面记录着员工每天的数据,其中表A有给字段标识该员工是临时工,还是正式工
表结构如下:
Staff_ID EffectiveDate Status
CH001 2012-10-01 PT
CH001 2012-10-02 PT
CH001 2012-10-03 PT
CH001 2012-10-04 FT
... ... ..我想写个语句获取2012-10-01到2012-10-31期间,Status由PT转变为FT所有员工的数据,应该怎样获取?
一定要是PT转变为FT的
WHERE a.Staff_Status IS NOT NULL AND a.EffectiveDate >= '2012-10-21' AND a.EffectiveDate<'2012-11-30'
GROUP BY a.Staff_ID HAVING COUNT(DISTINCT a.Staff_Status) > 1这句可以查出在规定时间范围内符合有PT转为FT或,FT转为PT的数据,但是在怎么筛选出必须是PT转为FT?
select a.Staff_ID from Attendance_Reports a,
(select Staff_ID,max(EffectiveDate) mrq from Attendance_Reports
where b.EffectiveDate>='2012-10-21' and a.EffectiveDate<'2012-11-30'
group by a.Staff_ID HAVING COUNT(Distinct a.staff_status)>1) b
where a.Staff_ID=b.Staff_ID and a.EffectiveDate=b.mrq and a.staff_status='FT'或者select a.Staff_ID from Attendance_Reports a
where exists(select 1 from
(select Staff_ID,max(EffectiveDate) mrq from Attendance_Reports
where b.EffectiveDate>='2012-10-21' and a.EffectiveDate<'2012-11-30'
group by a.Staff_ID HAVING COUNT(Distinct a.staff_status)>1) b
where a.Staff_ID=b.Staff_ID and a.EffectiveDate=b.mrq and a.staff_status='FT'
)
(select Staff_ID,max(EffectiveDate) mrq from Attendance_Reports
where EffectiveDate>='2012-10-21' and EffectiveDate<'2012-11-30'
group by Staff_ID HAVING COUNT(Distinct staff_status)>1) b
where a.Staff_ID=b.Staff_ID and a.EffectiveDate=b.mrq and a.staff_status='FT'或者
select a.Staff_ID from Attendance_Reports a
where exists(select 1 from
(select Staff_ID,max(EffectiveDate) mrq from Attendance_Reports
where EffectiveDate>='2012-10-21' and EffectiveDate<'2012-11-30'
group by Staff_ID HAVING COUNT(Distinct staff_status)>1) b
where a.Staff_ID=b.Staff_ID and a.EffectiveDate=b.mrq and a.staff_status='FT'
)