Hi
How do I clculate time duration if I have a column like this
2008-04-19 15:00:55.000
2008-04-19 15:01:07.000
2008-04-19 16:33:55.000
2008-04-19 16:35:07.000
2008-04-19 16:37:00.000
2008-04-19 16:37:01.000
2008-04-19 16:37:13.000
2008-04-19 16:37:14.000
2008-04-19 16:39:35.000
2008-04-19 16:49:05.000
to know the session user login/logout?
How is the query in SQL?
tq
nma
13 14987 -
select Dte,NextDte,datediff(ss,Dte,NextDte) as Seconds
-
from
-
( select DateTime as Dte,(select min(Datetime)
-
from YourTable b
-
where b.Datetime>c.Datetime)as NextDte
-
from YourTable c
-
)a
-
-
select Dte,NextDte,datediff(ss,Dte,NextDte) as Seconds
-
from
-
( select DateTime as Dte,(select min(Datetime)
-
from YourTable b
-
where b.Datetime>c.Datetime)as NextDte
-
from YourTable c
-
)a
-
Hi
I'm using data from one table name LogCombined2008 only
and the colum name is date
I also have colum name userID
Thanks
substitite with your field and table names -
select userID,Date,NextDate,datediff(ss,Date,NextDate) as Seconds
-
from
-
( select UserID,Date,(select min(Datetime)
-
from LogCombined2008 b
-
where b.userID=c.userID and b.date>c.date
-
) as NextDate
-
from LogCombined2008 c
-
)a
-
substitite with your field and table names -
select userID,Date,NextDate,datediff(ss,Date,NextDate) as Seconds
-
from
-
( select UserID,Date,(select min(Datetime)
-
from LogCombined2008 b
-
where b.userID=c.userID and b.date>c.date
-
) as NextDate
-
from LogCombined2008 c
-
)a
-
thanks Delerna
it works, I can get duration is seconds, then I will calculate the total seconds for specific user login (say userID= 12, total seconds is (sum all the row). maybe I can do manually, export the table in excel and then filter it to get the sum of the second
or is there is a faster way to calculate total second in that row?
nma
Do you mean something like this -
select userID,sum(datediff(ss,Date,NextDate)) as TotalSeconds
-
from
-
( select UserID,Date,(select min(Datetime)
-
from LogCombined2008 b
-
where b.userID=c.userID and b.date>c.date
-
) as NextDate
-
from LogCombined2008 c
-
)a
-
group by userID
-
Hi Delerna,
this is the query and the results generated....which is works fine.
[HTML]select userID,date,NextDate,datediff(ss,date,NextDate) as Seconds
from
( select userID,date,(select min(date)
from LogCombined2008 b
where b.userID=c.userID and b.date>c.date
) as NextDate
from LogCombined2008 c
)a[/HTML]
[HTML][userID] [date] [NextDate] [Seconds]
1 2007-10-31 13:18:49.897 2007-10-31 13:19:32.117 43
1 2007-10-31 13:19:32.117 2007-10-31 13:58:14.947 2322
12 2007-10-31 13:20:10.100 2007-10-31 13:24:46.650 276
12 2007-10-31 13:24:46.650 2007-10-31 13:25:03.210 17
12 2007-10-31 13:43:29.513 2007-10-31 13:43:40.047 11[/HTML]
but can I get total in seconds automatically calculated say userID=1 is equal 43+2322 and display the outputs in seconds and hour? Because after that I need to plot a graph for each user/total user in seconds/hour
But when I add the "group by userID" at the last line it shows error
"Msg 8120, Level 16, State 1, Line 1
Column 'a.date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Basically, the error means that you are selecting the field [date] but you are not aggregating it.
For example -
SELECT userID,date,sum(secs) as secs
-
FROM sourcetable
-
group by userid
-
would generate the same error because there are 3 fields being selected but only 2 of them are being aggregated.
secs is aggregated by the sum()
user id is being aggregated by the group by
date is not being aggregated by anything -
SELECT userID,date,sum(secs) as secs
-
FROM sourcetable
-
group by userid,[Date]
-
Now date is being aggregated by group by. No error will occurr
alternatively. -
SELECT userID,max(date) as Dte,sum(secs) as secs
-
FROM sourcetable
-
group by userid
-
now Date is being aggregated by max(). No error will occur.
Every field in your select must be aggregated in some way
You seem to be wanting to sum all the logged in times for a particular user on a particular date, so try this -
SELECT convert(datetime,left(LogCombined2008,11)) as Date
-
Try that as a test because i am not sure whether it is 10 or 11 characters for the left function to get just the date part of the datetime.
That should return the date only (time will always be midnight) for all dates in the table.
With the above info you can now group by the date like this -
select userID,convert(datetime,left(LogCombined2008,11)) as Date,NextDate,sum(datediff(ss,date,NextDate)) as Seconds
-
from
-
( select userID,date,(select min(date)
-
from LogCombined2008 b
-
where b.userID=c.userID and b.date>c.date
-
) as NextDate
-
from LogCombined2008 c
-
)a
-
group by userid,convert(datetime,left(LogCombined2008,11))
-
[quote=Delerna]You seem to be wanting to sum all the logged in times for a particular user on a particular date, so try this -
SELECT convert(datetime,left(LogCombined2008,11)) as Date
-
Hi Delerna,
The code above produce error message like below:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LogCombined2008'.
oops my bad I used the table name instead of the field
try this -
SELECT convert(datetime,left(Date,11)) as Date
-
which means the rest of the code should be -
select userID,convert(datetime,left(Date,11)) as Date,NextDate,sum(datediff(ss,date,NextDate)) as Seconds
-
from
-
( select userID,date,(select min(date)
-
from LogCombined2008 b
-
where b.userID=c.userID and b.date>c.date
-
) as NextDate
-
from LogCombined2008 c
-
)a
-
group by userid,convert(datetime,left(Date,11))
-
oops my bad I used the table name instead of the field
try this -
SELECT convert(datetime,left(Date,11)) as Date
-
which means the rest of the code should be -
select userID,convert(datetime,left(Date,11)) as Date,NextDate,sum(datediff(ss,date,NextDate)) as Seconds
-
from
-
( select userID,date,(select min(date)
-
from LogCombined2008 b
-
where b.userID=c.userID and b.date>c.date
-
) as NextDate
-
from LogCombined2008 c
-
)a
-
group by userid,convert(datetime,left(Date,11))
-
Hi Delerna,
It works...I just change the column name typing error only...it give total seconds the particular user has accesing/logging the system
Thanks a mills :)
I will post another problem solution as now almost drowning with my data analysis and will do part by part :)
Hi Delerna,
it possible right to convert the seconds into another column for minutes/hour from these query?
nma
hours=convert(int,seconds/60)
this will get the whole hours
you may not need to convert to int as both numbers are integers and the answer may come out as an integer?
Now to find the left over seconds Sec=seconds-(hours*60)
since hours=convert(int,seconds/60)
therefore Sec=seconds-(convert(int,seconds/60)*60) Sign in to post your reply or Sign up for a free account.
Similar topics
by: krystoffff |
last post by:
For the following query, I have a little problem ...
First, I have to rewrite several times the same query because the
alias are not recognised in the same query ( I got an error when I try
to...
|
by: sg_s123 |
last post by:
============================================================================
02-Feb-04 03-Feb-04
Staff Staff 0800hr- 1300hr- 1700hr- 1900hr- 0800hr- 1300hr- 1700hr-
1900hr-
Number...
|
by: Stephen Young |
last post by:
Hello
Have a bit of a problem, im trying to get the total number of
hours:minutes for all actions over the month in a query, have tried
two methods to no luck...
Duration This Month:...
|
by: Tu-Thach |
last post by:
TimeSpan span = aEnd.Subtract(aStart);
System.Console.WriteLine(span.TotalMilliseconds);
Tu-Thach
>-----Original Message-----
>I want to calculate the time it takes to execute a
function is...
|
by: leeg |
last post by:
Help please. Apologies but I am poor in access and programming :o) and am having trouble getting my head around this one!!...again!!!
I need to have a query or report to flag up someone who has...
|
by: archulu |
last post by:
hai
this is archulu. i am new to domapi.now my problem is in my project i am taken 2 domapi datepickers. what i am want Calculate duration between two dates which i am selected in that datepicker.
...
|
by: Jim Mandala |
last post by:
Using MS Access 2003 with SQL Server 2005 as a backend:
I am trying to automatically check for collisions in a table of
appointments with an appointment currenlty being saved from the form...
|
by: heart01 |
last post by:
Hello All, I have been trying to work this out myself based on a response that I received yesterday which worked however I think I am lost in the process. I have a form which I have 6 calculated...
|
by: Mirku |
last post by:
Hello!
I have a table in a database where there is a column called DATE (datetime type)that logs the date/time of an event.The first index of the table it represent the start date/time and the last...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |