here is my stored procedure...this stored procedure showing result for fromdate and toate on employee code,an aggregate function for differentiate checkIn and checkout... it shows data for checkout time only for one date,the problem is ,I need this for multiple days...
If any one know what is the problem kindly share your experience with me, and point my mistakes in query... I shall be thankful
here is the tables script
CREATE TABLE [dbo].[ras_AttRecord](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DN] [smallint] NULL,
[DIN] [bigint] NOT NULL,
[Clock] [datetime] NOT NULL,
[VerifyMode] [tinyint] NULL,
[AttTypeId] [char](3) NOT NULL,
[CollectDate] [datetime] NOT NULL,
[LastUpdatedUID] [int] NULL,
[LastUpdatedDate] [datetime] NULL,
[Remark] [nvarchar](64) NULL
)
CREATE TABLE [dbo].[ras_Users](
[UID] [int] NOT NULL,
[DIN] [bigint] NOT NULL,
[PIN] [varchar](32) NOT NULL,
[UserName] [nvarchar](64) NULL,
[Sex] [char](1) NOT NULL,
[Password] [nvarchar](32) NULL,
[PasswordQuestion] [nvarchar](64) NULL,
[PasswordAnswer] [nvarchar](32) NULL,
[IsApproved] [bit] NOT NULL,
[IsLockedOut] [bit] NOT NULL,
[CreateDate] [datetime] NULL,
[LastLoginDate] [datetime] NULL,
[DeptId] [varchar](64) NOT NULL,
[AttId] [char](4) NULL,
[RuleId] [char](4) NULL,
[WeekendId] [char](4) NULL,
[LastUpdatedUID] [int] NULL,
[LastUpdatedDate] [datetime] NOT NULL,
[Comment] [nvarchar](128) NULL
)
I'm using this stored procedure
ALTER PROCEDURE [dbo].[GetLateComersmonthly]
@FromDate DATE='05/22/2017',
@ToDate DATE='06/06/2017',
@Code varchar(6) ='3155'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @Code = '0'
SET @Code = null
-- Insert statements for procedure here
select
T1.UserName,
T1.DIN as [DIN],
min(T1.Date) as [Date],
min(T1.Time) as [Time],
CASE
WHEN max(T2.Time) = max(T1.Time) THEN ''
ELSE max(T2.Time) end as [Timee], min(T1.Day) as [Day],
CASE
WHEN concat(CONVERT(varchar,DATEDIFF(MINUTE,min(T1.Time),max(t2.Time)),108)/60,
':',
CONVERT(varchar,DATEDIFF(MINUTE,min(T1.Time),max(t2.Time))%60,108)) = '0:0'
THEN 'No Check Out'
ELSE concat(CONVERT(varchar,DATEDIFF(MINUTE,min(T1.Time),max(t2.Time)),108)/60,
':',
CONVERT(varchar,DATEDIFF(MINUTE,min(T1.Time),max(t2.Time))%60,108))
END AS hrs
from
(SELECT
UserName ,
ru.DIN,
CONVERT(varchar, Att.Clock, 101) AS Date,
CONVERT(varchar, Att.Clock, 108) AS Time,
att.VerifyMode as Mode, DATENAME(dw, Att.Clock) AS Day
FROM
ras_Users as ru
inner join ras_AttRecord as att ON ru.DIN = att.Din
where CONVERT(DATE,Clock) >= @FromDate
and CONVERT(DATE,Clock)<=@ToDate
and att.DIN = COALESCE (@Code, att.DIN)
and att.VerifyMode = 15
) as T1
left join
(SELECT
ru.DIN,
max(CONVERT(varchar, Att.Clock, 108)) AS Time
FROM
ras_Users as ru
inner join ras_AttRecord as att ON ru.DIN = att.Din
where CONVERT(DATE,Clock) >= @FromDate
and CONVERT(DATE,Clock)<=@ToDate
and att.DIN = COALESCE (@Code, att.din)
group by ru.DIN, att.Clock
) as T2 on T1.DIN = T2.DIN --and DATEDIFF(MINUTE,T1.Time,t2.Time)/60 < 9
group by
T1.Username,
T1.din,
T1.Day
-- having CONVERT(varchar, min(T1.Time), 108) > '09:30:00'
order by T1.din
END
its shows the following result
Current Record displaying
Userid Date CheckIn CheckOut Day total hours
3155 05/26/2017 09:15:10 17:15:00 Friday 8:0
3155 05/29/2017 09:08:36 17:15:00 Monday 8:7
3155 05/25/2017 09:34:28 17:15:00 Thursday 7:41
3155 05/30/2017 09:15:12 17:15:00 Tuesday 8:0
3155 05/31/2017 09:27:37 17:15:00 Wednesday 7:48
and I need the following result Expected Result
Userid Date CheckIn CheckOut day total hours
3155 05/26/2017 09:15:10 17:00:00 Friday 8:0
3155 05/29/2017 09:08:36 17:05:00 Monday 8:7
3155 05/25/2017 09:34:28 17:30:00 Thursday 7:41
3155 05/30/2017 09:15:12 17:18:00 Tuesday 8:0
3155 05/31/2017 09:27:37 17:10:00 Wednesday 7:48
