January 31, 2018 at 9:05 pm
Hi Freinds,I have one doubt in sql server .
how to calculated time difference with order by empid and time. if time differnce is more than 5 hours then status show 1 else 0.
CREATE TABLE [dbo].[Timecal](
[Emp ID] [float] NULL,
[time] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-02T09:00:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-02T10:30:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-03T09:30:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-03T12:30:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-03T12:40:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-03T17:10:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-03T06:30:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-03T08:30:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-05T23:30:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-06T01:55:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-06T02:15:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-06T06:10:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-02T11:00:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-02T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-02T13:00:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-06T14:01:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-06T15:01:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-06T15:20:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (1, CAST(N'2017-08-06T20:01:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (5, CAST(N'2017-08-02T23:30:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (5, CAST(N'2017-08-03T01:30:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (5, CAST(N'2017-08-03T01:40:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (5, CAST(N'2017-08-03T04:00:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (5, CAST(N'2017-08-03T04:30:00.000' AS DateTime))
GO
INSERT [dbo].[Timecal] ([Emp ID], [time]) VALUES (5, CAST(N'2017-08-03T06:00:00.000' AS DateTime))
GO
based on above data I want output like below:
Emp ID |time |status
1 |2017-08-02 09:00:00.000 |1
1 |2017-08-02 10:30:00.000 |0
1 |2017-08-02 11:00:00.000 |0
1 |2017-08-02 12:00:00.000 |0
1 |2017-08-02 13:00:00.000 |0
1 |2017-08-03 06:30:00.000 |1
1 |2017-08-03 08:30:00.000 |0
1 |2017-08-03 09:30:00.000 |0
1 |2017-08-03 12:30:00.000 |0
1 |2017-08-03 12:40:00.000 |0
1 |2017-08-03 17:10:00.000 |0
1 |2017-08-05 23:30:00.000 |1
1 |2017-08-06 01:55:00.000 |0
1 |2017-08-06 02:15:00.000 |0
1 |2017-08-06 06:10:00.000 |0
1 |2017-08-06 14:01:00.000 |1
1 |2017-08-06 15:01:00.000 |0
1 |2017-08-06 15:20:00.000 |0
1 |2017-08-06 20:01:00.000 |0
5 |2017-08-02 23:30:00.000 |1
5 |2017-08-03 01:30:00.000 |0
5 |2017-08-03 01:40:00.000 |0
5 |2017-08-03 04:00:00.000 |0
5 |2017-08-03 04:30:00.000 |0
5 |2017-08-03 06:00:00.000 |0
I tried like below :
select i.*
,case when datediff(hh, i.Time , o.Time)<= 5 then 0 else 1 end status
from Timecal o join Timecal i on i.[emp id] = o.[emp id] AND o.time <i.time
order by i.time
its not given expected output.
please tell me how to achive this task in sql server .
January 31, 2018 at 9:45 pm
Since this is a SQL 2008 board, I am assuming that you are 2008.
The following should give you the correct resultsWITH cteTime AS (
SELECT [Emp ID], [time]
, [rn] = ROW_NUMBER() OVER (PARTITION BY [Emp ID] ORDER BY [time])
FROM [dbo].[Timecal]
)
SELECT t1.[Emp ID], t1.[time]
--, [prev time] = t2.[time]
, [status] = CASE WHEN ISNULL(DATEDIFF(hh, t2.[time], t1.[time]), 10) >5 THEN 1 ELSE 0 END
FROM cteTime AS t1
LEFT JOIN cteTime as t2
ON t1.[Emp ID] = t2.[Emp ID]
AND t1.[rn] = t2.[rn] + 1
ORDER BY t1.[Emp ID], t1.[time];
January 31, 2018 at 9:47 pm
The LAG function was introduced in SQL 2012, which would allow you to do it like this
SELECT [Emp ID], [time]
, [prev time] = LAG([time]) OVER (PARTITION BY [Emp ID] ORDER BY [time])
, [status] = CASE WHEN ISNULL(DATEDIFF(hh, LAG([time]) OVER (PARTITION BY [Emp ID] ORDER BY [time]), [time]), 10) >5 THEN 1 ELSE 0 END
FROM [dbo].[Timecal]
ORDER BY [Emp ID], [time];
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply