calculate car parking hours in sql server

Question

I have one doubt in sql server. How to calculate car login and logout time details?

table: cardetails

Here need to calculate each car how many hours spend in parking area.

creatE TABLE [dbo].[CarDetails](
    [carid] [int] NULL,
    [DateTimeDetail] [datetime] NULL,
    [Flag] [varchar](50) NULL
) 
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (1, CAST(N'2019-01-20T19:05:00.000' AS DateTime), N'in')
GO
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (1, CAST(N'2019-01-20T22:30:00.000' AS DateTime), N'out')
GO
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (2, CAST(N'2019-01-20T20:30:10.000' AS DateTime), N'in')
GO
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (2, CAST(N'2019-01-21T02:10:10.000' AS DateTime), N'out')
GO
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (3, CAST(N'2019-01-23T07:07:40.000' AS DateTime), N'in')
GO
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (3, CAST(N'2019-01-23T10:50:40.000' AS DateTime), N'out')
GO
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (3, CAST(N'2019-01-23T11:00:10.000' AS DateTime), N'in')
GO
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (3, CAST(N'2019-01-23T14:15:30.000' AS DateTime), N'out')
GO
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (2, CAST(N'2019-01-21T08:20:10.000' AS DateTime), N'in')
GO
INSERT [dbo].[CarDetails] ([carid], [DateTimeDetail], [Flag]) VALUES (2, CAST(N'2019-01-21T10:20:10.000' AS DateTime), N'out')
GO

These are the records:

carid   DateTimeDetail              Flag
1       2019-01-20 19:05:00.000     in
1       2019-01-20 22:30:00.000     out
2       2019-01-20 20:30:10.000     in
2       2019-01-21 02:10:10.000     out
3       2019-01-23 07:07:40.000     in
3       2019-01-23 10:50:40.000     out
3       2019-01-23 11:00:10.000     in
3       2019-01-23 14:15:30.000     out
2       2019-01-21 08:20:10.000     in
2       2019-01-21 10:20:10.000     out

Based on above data I want output like below :

carid | DateTimeDetails | Totaltime(hh:mm:ss)
  1     |2019-01-20       | 03:25:00
  2     |2019-01-20       | 05 :49:40
  2     |2019-01-21       | 02:00:00
  3     |2019-01-23       | 06:58:20

I tried like below

select a.carid ,  sum(datediff(mm,b.datetimedetail,a.datetimedetail))as totalmm from  CarDetails a join  CarDetails b
on a.carid=b.carid

where a.datetimedetail<=(select max(c.[DateTimeDetail]) from CarDetails c join CarDetails a on a.carid=c.carid
)
group by a.carid 

please tell me how to write query to achive this task in sql server

Answer 1

you can try this

select carid,dateIn, cast(DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', timeout)), '00:00:00.000') as time) timeOut 
from (
    select carid,cast([DateTimeDetail]as DATE) DateIn , 
        (select top 1 [DateTimeDetail] 
            from [CarDetails] b 
            where b.carid=a.carid and b.[DateTimeDetail]>a.[DateTimeDetail] and flag='out' )
        -[DateTimeDetail] timeOut
from [CarDetails] a
where Flag='in') c
group by  carid,dateIn

This gives the results below:

carid   dateIn  timeOut
1   2019-01-20  03:25:00.0000000
2   2019-01-20  05:40:00.0000000
2   2019-01-21  02:00:00.0000000
3   2019-01-23  06:58:20.0000000

Answer 2

This doesn't quite give you the expected results in your post, however, I have good reason for it not to be, and I suspect your expected results are wrong:

--Because, from experience, people can somehow enter things twice before exiting...
WITH Grp AS(
    SELECT CD.carid,
           CD.DateTimeDetail,
           CD.Flag,
           COUNT(CASE Flag WHEN 'Out' THEN 1 END) OVER (PARTITION BY carid ORDER BY CD.DateTimeDetail ASC
                                                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS Grp
    FROM dbo.CarDetails CD),
InOut AS(
    SELECT carid,
           MIN(CASE Flag WHEN 'In' THEN Grp.DateTimeDetail END) AS TimeIn,
           MAX(CASE Flag WHEN 'Out' THEN Grp.DateTimeDetail END) AS [TimeOut]
    FROM Grp
    GROUP BY carid, grp)
SELECT carid,
       CONVERT(date,TimeIn) AS DateTimeDetails,
       CONVERT(time(0),DATEADD(SECOND,SUM(DATEDIFF(SECOND,TimeIn, [TimeOut])),'00:00:00')) AS TotalTime
FROM InOut
GROUP BY carid,
         CONVERT(date,TimeIn)
ORDER BY carid ASC;

This gives the results below:

carid       DateTimeDetails TotalTime
----------- --------------- ----------------
1           2019-01-20      03:25:00
2           2019-01-20      05:40:00
2           2019-01-21      02:00:00
3           2019-01-23      06:58:20

Notice I have a 05:40:00 for car 2 on 2019-01-20. Car 2 enters at 20:30:10 and leaves at 02:10:10, which is 5 hours, and 40 minutes later; not 05 hours 49 minutes and 40 seconds later. if you have that time in your expected results for a reason, you need to explain why.

Note: This will not work if a car stays more than 24 hours! You didn't respond to my question, so I have assumed not. If they can, SQL Server does not support times great than 24 hours, therefore you will be better returning the number of seconds, and having your application display a 24+ hour time.