[font=system]
I have, what I believe, is a very simple problem that (since I am new) I cannot figure out.

I have a table called HISTORY that stores historical data. Initially the data is dumped from 
another server which collects the data into the MS SQL database. I need the following items 
from the table TIME, NAME, VALUE and then only where ID = 20 and NAME = NAME1 or NAME2 or 
NAME3. To give you more information about this particular set of data, the values are 
collected once an hour all day, every day. What I need to do is find the maximum value for NAME1 
each day and get the values for NAME2 and NAME3 at that same time regardless if they were 
maximum values for themselves.


So if I had something like this:
                   NAME1    NAME2     NAME3
10/1/2008 7:00     1000     834       99
10/1/2008 8:00     1140     710       250
---------carry on for day through 23:00 where no other values are > 
10/2/2008 1:00     1569     800       123
10/2/2008 2:00     1500     334       456
---------carry on for the day through 23:00 where no other values are >

I would want to have the results like this
TIME               NAME1     NAME2     NAME3
10/1/2008 8:00     1140      710       250
10/2/2008 1:00     1569      800       123
-----and so one for each DAY

Would also be helpful to have it parameterized by month and year 
so I could ask for the results of the time of the maximum value for NAME1 during 10-2008 or 1-2009.

Would love it if someone could help me with this!!!
[/font=system]

quote:

---

Originally posted by paulhood

TIME               NAME1     NAME2     NAME3
10/1/2008 8:00     1140      710       250
10/2/2008 1:00     1569      800       123
-----and so one for each DAY

---

DECLARE	@Sample TABLE
	(
		DT DATETIME,
		NAME1 INT,
		NAME2 INT,
		NAME3 INT
	)

INSERT	@Sample
SELECT	'10/1/2008 7:00', 1000, 834,  99 UNION ALL
SELECT	'10/1/2008 8:00', 1140, 710, 250 UNION ALL
SELECT	'10/2/2008 1:00', 1569, 800, 123 UNION ALL
SELECT	'10/2/2008 2:00', 1500, 334, 456


SELECT		DT,
		theName,
		theValue
FROM		(
			SELECT	DT,
				NAME1,
				NAME2,
				NAME3
			FROM	(
					SELECT	DT,
						NAME1,
						NAME2,
						NAME3,
						ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, 0, DT) ORDER BY NAME1 DESC) AS recID
					FROM	@Sample
				) AS d
			WHERE	recID = 1
		) AS d
UNPIVOT		(
			theValue
			FOR theName IN (d.NAME1, d.NAME2, d.NAME3)
		) AS u
ORDER BY	DT,
		theName

quote:

---

Originally posted by DonAtWork

Follow the 2nd link in my signature, and it tells you what information to provide, and how to provide it.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

---

SELECT		DT,
		theName,
		theValue
FROM		(
			SELECT	DT,
				NAME1,
				NAME2,
				NAME3
			FROM	(
					SELECT	DT,
						NAME1,
						NAME2,
						NAME3,
						ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, 0, DT) ORDER BY NAME1 DESC) AS recID
					FROM	HISTORY
				) AS d
			WHERE	recID = 1
		) AS d
UNPIVOT		(
			theValue
			FOR theName IN (d.NAME1, d.NAME2, d.NAME3)
		) AS u
ORDER BY	DT,
		theName

quote:

---

Originally posted by Peso

Since I have no access to your data, I hade to make up an environment similar to yours.
Replace the @Sample table with your table name HISTORY, and you are set to go!

SELECT		DT,
		theName,
		theValue
FROM		(
			SELECT	DT,
				NAME1,
				NAME2,
				NAME3
			FROM	(
					SELECT	DT,
						NAME1,
						NAME2,
						NAME3,
						ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, 0, DT) ORDER BY NAME1 DESC) AS recID
					FROM	HISTORY
				) AS d
			WHERE	recID = 1
		) AS d
UNPIVOT		(
			theValue
			FOR theName IN (d.NAME1, d.NAME2, d.NAME3)
		) AS u
ORDER BY	DT,
		theName

---

N 56°04'39.26"
E 12°55'05.63"

---

quote:

---

Originally posted by Peso

Listen carefully to DonAtWork so we can assist you.

---

N 56°04'39.26"
E 12°55'05.63"

---