Sql query to get the difference b/w max and min of an instance

Question

0.00/5 (No votes)

See more:

I am expecting a query to list out all the records with the difference of Max and Min time of that user

See below

Current Format

id......Name....Time..................Expected column(In secs)
1  | 	John  | 2015-11-29 14:01:16  | 	7
2  |  	John | 	2015-11-29 14:01:16  | 	7
3  | 	John | 	2015-11-29 14:01:22 |  	7
4  | 	John | 	2015-11-29 14:01:22  | 	7	
5  | 	Mark | 	2015-11-29 14:00:54  | 	6
6 | 	Mark | 	2015-11-29 14:00:54  | 	6
7 | 	Mark | 	2015-11-29 14:01:00  | 	6
8 | 	Mark | 	2015-11-29 14:01:00  | 	6

In the above table Expected column in my requirement..

In the above table I would Like to add a column "Expected column", what i want to calculate is wil be velow

Expected column formula

Diff(Max(Time)-Min(Time)) for that Perosn

Lets take an example for John

Max(Time) is "2015-11-29 14:01:22"
Min(Time) is "2015-11-29 14:01:16"

Diff is calculated as 7 seconds
So for all the records of John The time diffrence should be apended. Same like other records..

It would be really great if any one provide me the perfect query for this

What I have tried:

I am trying to add a column by calculating the max and min time stamp of an instance and difference of seconds should be displayed for that records.. In out put I should be getting all the recordsss with added column which show the time difference for that instance

Posted 20-Feb-16 23:39pm

jing567

Updated 21-Feb-16 0:24am

Maciej Los

v4

Add a Solution

Comments

OriginalGriff 21-Feb-16 5:49am

That doesn't make a whole load of sense - perhaps if you add an example of the output you expect?
Use the "Improve question" widget to edit your question and provide better information.

jing567 21-Feb-16 6:08am

I am sorry Griff..I have updated with detail explanation.. Can you help me Please!!!

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)

OriginalGriff · Answer 1 · 2016-02-21T00:24:00

First off, your table design looks bad - you shouldn't be using text names each time, but a separate "users" table that has a name, and an ID value - you then correlate the two tables using a JOIN. That way, you can have multiple users with the same first name... :laugh:

Secondly, adding summary info to every row is a bad way to do it - it duplicates work and duplicates data, so while it is possible it's much better to have a summary query and use that separately to your user records. (Duplicating data is always a bad idea!)

But you can do it - it's just a bad idea:

SQL

SELECT Id, a.[name], a.[Time], DATEDIFF(ss, b.Mind, b.Maxd) FROM MyTable a
JOIN (SELECT [name], MAX([Time]) AS Maxd, MIN([Time]) AS Mind FROM MyTable
      GROUP BY [name]) b
ON a.[name] = b.[name]