?
Solved

SQL Query show date in and date out on table

Posted on 2015-01-29
8
Medium Priority
?
210 Views
Last Modified: 2015-01-30
Hi I have a table at the moment as follows

Name             Date
john                01-01-2000 09:00
john                01-01-2000 11:00
john                01-01-2000 11:15
john                01-01-2000 13:00
john                01-01-2000 13:30
john                01-01-2000 17:30

What I want to do is convert this to
Name             In                                     Out                                Time In
john                01-01-2000 09:00         01-01-2000 11:00        01:30
john                01-01-2000 11:15         01-01-2000 13:00        01:45
john                01-01-2000 13:30         01-01-2000 17:30        04:00

There are loads of people and they can lots of in and outs per day, please advise on how I could do this in SQL
0
Comment
Question by:taz8020
8 Comments
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 40577435
How do you know if is coming in or out? Don't exists a column with that information?
0
 
LVL 1

Expert Comment

by:echobridge
ID: 40577506
Having been down this road before, I'd might suggest restructuring your table if at all possible to record this information in the in/out format you're looking for. Makes it a lot easier when you get issues like missing information. In my situation it was people logging into a time clock, so missing information was common.
0
 
LVL 3

Author Comment

by:taz8020
ID: 40577669
No but the first record of the day is always in. Can it not have a query that numbers the logs per user and on a daily basis then odds would be in and evens would be out?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40577672
SELECT Name, MIN(Date) AS [In], MAX(Date) AS [Out],
    LEFT(CONVERT(varchar(10), DATEADD(MINUTE, DATEDIFF(MINUTE, MIN(DATE), MAX(Date)), 0), 8), 5) AS [Time In]
FROM (
    SELECT Name, Date,
        ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date) - 1 AS row_num
    FROM table_name
) AS derived
GROUP BY Name, row_num / 2
0
 
LVL 3

Author Comment

by:taz8020
ID: 40577954
Scott that is great, is there a way to start it each day. Just the way it is, if there is a duplicate clock in one day the following day it look like they have been in since they clocked out. hope you know what I mean
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40578010
Yeah, assuming you don't have shifts that cross days :-) :
...
ROW_NUMBER() OVER(PARTITION BY Name, DATEADD(DAY, DATEDIFF(DAY, 0, Date), 0) ORDER BY Date) - 1 AS row_num
...
0
 
LVL 3

Author Comment

by:taz8020
ID: 40579297
It now starts each day per user with a 0 but when I put it together it selects a different day for the out Max date. Is there something I am doing wrong?
SELECT Name, MIN(Date) AS [In], MAX(Date) AS [Out],
     LEFT(CONVERT(varchar(10), DATEADD(MINUTE, DATEDIFF(MINUTE, MIN(DATE), MAX(Date)), 0), 8), 5) AS [Time In]
 FROM (
     SELECT Name, Date,
         ROW_NUMBER() OVER(PARTITION BY Name, DATEADD(DAY, DATEDIFF(DAY, 0, Date), 0) ORDER BY Date) - 1 AS row_num
     FROM table_name
 ) AS derived
 GROUP BY Name, row_num / 2 

Open in new window

0
 
LVL 3

Author Closing Comment

by:taz8020
ID: 40579299
Sorry being dull, got it thank you so much
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question