January 28, 2016 at 6:35 am
Hi all. I would like to calculate session time. The database records the following: LoggedInDate,LoggedOutDate (datetime).
I would like to show session length as HH:MM:SS. If there is no LoggedOutDate I want to show current session length.
I currently have:
SELECT u.FirstName + ' ' + LastName AS UserName, LoggedInDate, LoggedOutDate,
DATEPART(YEAR, LoggedInDate) AS YearLoggedIn,
DATEPART(YEAR, LoggedOutDate) AS YearLoggedOut,
DATENAME(MONTH,ual.LoggedInDate) AS [MonthIn],
DATENAME(MONTH,ual.LoggedOutDate) AS [MonthOut],
DATEPART(WEEK, LoggedInDate) AS WeekNumberLoggedIn,
DATEPART(WEEK, LoggedOutDate) AS WeekNumberLoggedOut,
DATENAME(dw,ual.LoggedInDate) AS [DayOfWeekIn],
DATENAME(dw,ual.LoggedOutDate) AS [DayOfWeekOut],
CONVERT(varchar(12), DATEADD(hh, DATEDIFF(hh, ual.LoggedInDate,ual.LoggedOutDate), 0), 114) AS SessionLength
FROM UsersAccessLogs AS ual
INNER JOIN Users As u ON u.UserId = ual.UserId WHERE u.Active = 1
I came across this in a post but not sure how to integrate with above code:
select
*,
Days = datediff(dd,0,DateDif),
Hours = datepart(hour,DateDif),
Minutes = datepart(minute,DateDif),
Seconds = datepart(second,DateDif),
MS = datepart(ms,DateDif)
from
(
select
DateDif = EndDate-StartDate,
aa.*
from
( -- Test Data
Select
StartDate = convert(datetime,'20090213 02:44:37.923'),
EndDate = convert(datetime,'20090715 13:24:45.837')
) aa
) a
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 28, 2016 at 7:05 am
have a read here
http://www.sqlservercentral.com/articles/T-SQL/103343
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 28, 2016 at 7:27 am
Thanks for the link I will have a read.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 28, 2016 at 8:19 am
I have ended up with:
SELECT u.FirstName + ' ' + LastName AS UserName, LoggedInDate, LoggedOutDate,
DATEPART(YEAR, LoggedInDate) AS YearLoggedIn,
DATEPART(YEAR, LoggedOutDate) AS YearLoggedOut,
DATENAME(MONTH,ual.LoggedInDate) AS [MonthIn],
DATENAME(MONTH,ual.LoggedOutDate) AS [MonthOut],
DATEPART(WEEK, LoggedInDate) AS WeekNumberLoggedIn,
DATEPART(WEEK, LoggedOutDate) AS WeekNumberLoggedOut,
DATENAME(dw,ual.LoggedInDate) AS [DayOfWeekIn],
DATENAME(dw,ual.LoggedOutDate) AS [DayOfWeekOut],
CASE
WHEN ual.LoggedOutDate <> ' ' THEN STUFF(CONVERT(VARCHAR(20),ual.LoggedOutDate-ual.LoggedInDate,114),1,2,DATEDIFF(hh,0,ual.LoggedOutDate-ual.LoggedInDate))
ELSE STUFF(CONVERT(VARCHAR(20),GETDATE()-ual.LoggedInDate,114),1,2,DATEDIFF(hh,0,GETDATE()-ual.LoggedInDate))
END AS SessionLength
FROM UsersAccessLogs AS ual
INNER JOIN Users As u ON u.UserId = ual.UserId WHERE u.Active = 1
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply