|
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[get_yuangong_chidao_xx_cdzt]
(
@name varchar(100),
@date datetime,
@type varchar(100)
)
AS
declare @Year int,@Month int,@Day int,@Minute int,@Minute2 int
set @Year=year(@date)
set @Month=month(@date)
set @Minute=9*60+3
set @Minute2=18*60+3
if @type='迟到统计'
begin
select [姓名],[编号],[日期时间],[type],[type2],DATEDIFF(minute,cast((convert(char(10),[日期时间],120) + ' 09:00:00') as datetime),日期时间) as '分钟数'
from
(
select *,'迟到' as [type],day(DATEADD("Day",1,[日期时间]))-day(DATEADD("Minute", 0-@Minute, DATEADD("Day",1,[日期时间]))) as [type2]
from [考勤报表] a
where
ID =
(
select top 1 ID
from [考勤报表] b
where year(b.[日期时间])=@Year and month(b.[日期时间])=@Month and day(b.[日期时间])=day(a.[日期时间]) and b.[编号]=a.[编号]
order by b.[日期时间] asc
)
)c
where [type2]=0
and [姓名]=@name
and year([日期时间])=year(@date)
and month([日期时间])=month(@date)
order by [日期时间]
end
else
begin
select [姓名],[编号],[日期时间],[type],[type2],DATEDIFF(minute,[日期时间],cast((convert(char(10),[日期时间],120) + ' 18:00:00') as datetime)) as '分钟数'
from
(
select *,'早退' as [type],1-(day(DATEADD("Day",1,[日期时间]))-day(DATEADD("Minute", 0-@Minute2, DATEADD("Day",1,[日期时间])))) as [type2]
from [考勤报表] a
where ID =
(
select top 1 ID
from [考勤报表] b
where year(b.[日期时间])=@Year and month(b.[日期时间])=@Month and day(b.[日期时间])=day(a.[日期时间]) and b.[编号]=a.[编号]
order by b.[日期时间] desc
)
)c
--where [编号]=208
--and day(a.[日期时间])=1
--order by [编号],[日期时间],[type]
where [type2]=0
and [姓名]=@name
and year([日期时间])=year(@date)
and month([日期时间])=month(@date)
order by [日期时间]
end |
|