SQL Server 2008 Database Time Query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have a records like below in a table, my problem is I need to get ??? values from using database query or C# code. Kindly advise?

What is "???" column? Previous Out[minus]Current In

XML

<table>
  Date         In          Out        ???
10/26/2012  16:29:26    16:29:29
10/26/2012  16:31:21    16:31:25    0:01:52
10/26/2012  16:49:40    16:49:48    0:18:15
</table>

Posted 26-Oct-12 1:44am

abdulsafran

Updated 26-Oct-12 6:18am

fjdiewornncalwe

v2

Add a Solution

Comments

Bhushan Shah1988 26-Oct-12 9:05am

Please brief properly.
Tell us what you want?

5 solutions

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)

fjdiewornncalwe · Answer 1 · 2012-10-26T06:19:00

Solution 1

1) You need to figure out the pattern that exists. It is really quite simple.
2) You need to figure out how to calculate based on the pattern found in step 1.
3) Write sql code to generate that column's data.

Posted 26-Oct-12 6:19am

fjdiewornncalwe

Nitesh Kejriwal · Answer 2 · 2012-10-26T10:11:00

Solution 2

Pretty easy to do-
1. Get TOP 2 records from SQL

SQL

SELECT TOP 2 Out,In FROM tbl ORDER BY Out DESC

2. Get the values of In column in 2nd row and Out column in 1st row.
3. Get the difference between 2 values.

Posted 26-Oct-12 10:11am

Nitesh Kejriwal

cocis48 · Answer 3 · 2012-10-26T12:50:00

Solution 3

SELECT CONVERT(varchar,(Out - in), 108) FROM Table

Posted 26-Oct-12 12:50pm

cocis48

ahmad zrein · Answer 4 · 2012-10-26T19:10:00

Solution 4

hi

use Time(7) in the design of database.

Posted 26-Oct-12 19:10pm

ahmad zrein

ganeshdpatil · Answer 5 · 2012-10-27T04:53:00

Solution 5

SQL

SELECT Date
      ,In
      ,Out
      ,CONVERT(varchar, In - (SELECT MAX (OUT) FROM Table RD WHERE RD.OUT< MD.OUT AND RD.Date = MD.Date GROUP BY Date ), 108 ) [???]
FROM Table MD

This will give you expected output

Posted 27-Oct-12 4:53am

ganeshdpatil

Comments

abdulsafran 28-Oct-12 12:52pm

Hi Guys, thanks for your answers, but still my problem is not solved, I need that once enter the In and Out, I need to calculate previous record Out[minus]Current record IN to the ??? column. That's all. Kindly check and advise.

abdulsafran 28-Oct-12 12:55pm

??? Column = previous record Out Time - Current record In Time

abdulsafran 28-Oct-12 12:56pm

How to check previous record?

abdulsafran 28-Oct-12 13:32pm

Hi ganeshdpatil, what is RD and MD in your query? Thanks!

ganeshdpatil 28-Oct-12 14:40pm

Hi abdul RD and MD are alias for table. I was using table in two queries that's why to understand properly I gave alias. You just replace Table with your Table name and you will get desired output. I have tested this query.

abdulsafran 29-Oct-12 1:56am

Hi ganeshdpatil, thanks for the response, but below I have list down my table and column names, can you rename the query? it will help me a lot. I have only one table it is name is "TEXT" and column names are follow, Date, In, Out and ???. Waiting for your quick response.