Difference Between Maximum and Minumum Values

Question

Difference Between Maximum and Minumum Values

Thinky Night

SSCommitted

Points: 1663
More actions
December 31, 2013 at 6:22 am

#280847

I have a table
id Name starttime value
--- ----- --------- ------
1 A 2013-12-07 46
2 A 2013-12-08 9
3 A 2013-12-14 59
1 B 2013-12-05 10
2 B 2013-12-10 1
3 B 2013-12-11 12
1 C 2013-12-04 16
2 C 2013-12-13 2
3 C 2013-12-15 20
And want to get the Difference between MAx And MIN Values of a Date's Value.
My Result must be shown below:
Name MAX(starttime) MIN(STARTTIME) DIFFERENCE
----------------------------------------------------------
A 2013-12-14 2013-12-07 13
B 2013-12-11 2013-12-05 2
C 2013-12-15 2013-12-04 4
Could you please send me the SELECT Statement ?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I didn't check it (next time pleas write a script that creates the table and inserts the data), but I think that the query bellow will do it for you:

select Name, min(starttime) as Min_stattime, max(statrttime) as Max_stattime, max(value)-min(value) as Differnce

from MyTable

group by Name

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Thinky Night SSCommitted Points: 1663 More actions · Answer 2

This is not working, The Result is not i appreciated.

I want to get

max(starttime).value -min(starttime).value as Differnce

Here is the Runtime DDL

declare @MyTable table (id int,Name char(1),starttime Datetime, value int);

insert into @MyTable values (1,'A','2013-12-07',46);

insert into @MyTable values (2,'A','2013-12-08', 9);

insert into @MyTable values (3,'A','2013-12-14',59);

insert into @MyTable values (1,'B','2013-12-05',10);

insert into @MyTable values (2,'B','2013-12-10',1);

insert into @MyTable values (3,'C','2013-12-11',12);

insert into @MyTable values (1,'C','2013-12-04',16);

insert into @MyTable values (2,'C','2013-12-13',2);

insert into @MyTable values (3,'C','2013-12-15',20);

select * from @MyTable

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Hi,

To get better and faster answers, you should post DDL and sample data in a consumable format as I did this time for you. (It seems that you edited your post while I was writing mine :-D)

Here's an option, but there might be a better one.

CREATE TABLE #Sample(

idint,

Name char(1),

starttime date,

value int)

INSERT INTO #Sample VALUES(

1, 'A', '2013-12-07', 46),(

2, 'A', '2013-12-08', 9),(

3, 'A', '2013-12-14', 59),(

1, 'B', '2013-12-05', 10),(

2, 'B', '2013-12-10', 1),(

3, 'B', '2013-12-11', 12),(

1, 'C', '2013-12-04', 16),(

2, 'C', '2013-12-13', 2),(

3, 'C', '2013-12-15', 20);

WITH Dates AS(

SELECT Name,

MAX( starttime) Max_Time,

MIN( starttime) Min_Time

FROM #Sample

GROUP BY Name

)

SELECT s.Name,

d.Max_Time,

d.Min_Time,

MAX(CASE WHEN s.starttime = d.Max_Time THEN s.value END)

- MAX(CASE WHEN s.starttime = d.Min_Time THEN s.value END)

FROM #Sample s

JOIN Dates d ON s.Name = d.Name

GROUP BY s.Name,

d.Max_Time,

d.Min_Time

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thinky Night SSCommitted Points: 1663 More actions · Answer 4

Thinky Night

SSCommitted

Points: 1663

December 31, 2013 at 9:13 am

#1676612

Thanks a lot. Happy New Year.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Thinky Night (12/31/2013)
Thanks a lot. Happy New Year.

Let's start the new year off right. Do you understand how and why the solution works?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 6

I believe that this may be an alternate solution.

SELECT Name

,[MAX(starttime)]=MAX(CASE WHEN rn = c THEN starttime END)

,[MIN(STARTTIME)]=MIN(CASE WHEN rn = 1 THEN starttime END)

,[DIFFERENCE]=MAX(value)-MIN(value)

FROM

(

SELECT id, Name, starttime, value

,rn=ROW_NUMBER() OVER (PARTITION BY Name ORDER BY starttime)

,c=COUNT(*) OVER (PARTITION BY Name)

FROM @MyTable

) a

WHERE rn IN (1,c)

GROUP BY Name;

This solution seems to do a single table scan, but will have some overhead due to the COUNT(*) using the window aggregate.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St