Userlog data processing in SQL query

Question

1.00/5 (1 vote)

See more:

, +

I have one UserLog table in sql which contains UserName and DateTimeData fields. One user can login and logout several times in a day. So I have to generate one view or query to show the X username and his/her firstlogindatetime and lastlogoutdatetime and total usage of the software for that particular day and particular user. Same thing i have to repeat for all the users and dates. Finally, I need to write the query for get the each UserName by Date and their FirstLogin and LastLogout as well as TotalTimeWorked. My Data type for UserName is nvarchar(50) and for DateTimeData is nvarchar(50). One username should appear only once for day. But it can show firstlogin and lastlogout in separate columns. Can u guys pls help me and guide me in this thing?
Thanks in advance:)

What I have tried:

I have to write the sql query to get desired output

Posted 20-Feb-17 9:39am

Mohammed Ikbal Abdul Salam

Updated 20-Feb-17 12:02pm

Afzaal Ahmad Zeeshan

v2

Add a Solution

Comments

ZurdoDev 20-Feb-17 15:46pm

Where are you stuck?

Graeme_Grant 20-Feb-17 15:47pm

What have you tried so far?

j snooze 20-Feb-17 17:39pm

Google Min, Max, and Sum aggregates for SQL Server, that should help give you an idea where to start. Feel free to post your attempts so we can point out what else you might need.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2017-02-20T12:02:00

I would do this a 'bit differently' than the guys in the comments

- I'd write 'n' queries, where a query might be to get the data for user x for day y
- I'd use PetaPoco to extract that data and return an object list
- I'd then use LINQ to aggregate (possibly group) the objects

The unfortunate thing is, you have to make a start and supply 'something' for whichever approach you decide to use ie 'pure sql' or basic sql & LINQ - people here wont write your code for you