April 21, 2011 at 9:13 pm
Hi,
I need to prepare a report that describes user status and the time spent in transitioning from one status to another, for instance,
User arrives at work, logs in, and starts working and logs out. So the user here has 3 states prcisely
Arrival, Logged in, and logged out (considering working is logged in).
I need to display the report in the following format
User StartTime EndTime Arrival Login Logout
where as we are storing the status as a enumeration in one single column ( please refer attached file).
the catch is, a user can log out for say shopping, then again arrive, logs in works and log out, so multiple cycles are there, however the last logout for a day would be considered as the final log out.
It is probably straight forward but seems like I am not able to crack this one, here's what I tried after create a temptable with the attached data,
select
SessionID
,MAX(case status when 0 then StateStartTime else null end) LoginToController
,MAX(case status when 1 then StateStartTime else null end) LoginToProgram
,MAX(case status when 2 then StateStartTime else null end) Logout
from #Status where
group by SessionID--, Status
and it gave me a single record (refer singlerecrd.txt)
Appreciate your inputs!
April 22, 2011 at 10:01 am
kazim.raza (4/21/2011)
Hi,I need to prepare a report that describes user status and the time spent in transitioning from one status to another, for instance,
User arrives at work, logs in, and starts working and logs out. So the user here has 3 states prcisely
Arrival, Logged in, and logged out (considering working is logged in).
I need to display the report in the following format
User StartTime EndTime Arrival Login Logout
where as we are storing the status as a enumeration in one single column ( please refer attached file).
the catch is, a user can log out for say shopping, then again arrive, logs in works and log out, so multiple cycles are there, however the last logout for a day would be considered as the final log out.
It is probably straight forward but seems like I am not able to crack this one, here's what I tried after create a temptable with the attached data,
select
SessionID
,MAX(case status when 0 then StateStartTime else null end) LoginToController
,MAX(case status when 1 then StateStartTime else null end) LoginToProgram
,MAX(case status when 2 then StateStartTime else null end) Logout
from #Status where
group by SessionID--, Status
and it gave me a single record (refer singlerecrd.txt)
Appreciate your inputs!
If your attached text files are all of your data, then the process is working correctly. You are grouping by SessionID and you only have 1 SessionID so you'll only get 1 row returned.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply