Query help needed...PLEASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query help needed...PLEASE

Author

Topic

testdronetone
Starting Member

4 Posts

Posted - 2007-08-08 : 12:39:17

In the table below for Query3, I need to be able to extract the last time "each' RN_TEST_ID was tested
for each month. For example, for RN_TEST_IDs '1453' and '1420', the rows in the results set would look like this because they are the most recent records for that specific ID for each Month. Thank you, Thank you, Thank you!

8/1/2007 09:55:48 1453 Passed
8/2/2007 10:32:17 1420 Passed
7/3/2007 09:28:42 1453 Passed

Query3
RN_EXECUTION_DATE RN_EXECUTION_TIME RN_TEST_ID RN_STATUS
8/2/2007 11:14:51 1421 Passed
8/2/2007 10:32:17 1420 Passed
8/2/2007 09:30:28 1420 Failed
8/2/2007 09:29:20 1418 Passed
8/2/2007 09:18:45 1418 Failed
8/1/2007 16:06:22 1416 Failed
8/1/2007 14:19:56 1413 Passed
8/1/2007 14:19:44 1413 Failed
8/1/2007 14:19:32 1414 Passed
8/1/2007 09:55:48 1453 Passed
8/1/2007 09:36:52 1453 Failed
7/3/2007 09:28:42 1453 Passed

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-08 : 12:49:39

Something like this:


Declare @T Table( Date datetime, Id int, Status varchar(20))
insert into @T 
Select '8/2/2007 11:14:51', 1421, 'Passed' UNION ALL 
Select '8/2/2007 10:32:17', 1420, 'Passed' UNION ALL 
Select '8/2/2007 09:30:28', 1420, 'Failed' UNION ALL  
Select '8/2/2007 09:29:20', 1418, 'Passed' UNION ALL 
Select '8/2/2007 09:18:45', 1418, 'Failed' UNION ALL  
Select '8/1/2007 16:06:22', 1416, 'Failed' UNION ALL  
Select '8/1/2007 14:19:56', 1413, 'Passed' UNION ALL 
Select '8/1/2007 14:19:44', 1413, 'Failed' UNION ALL  
Select '8/1/2007 14:19:32', 1414, 'Passed' UNION ALL 
Select '8/1/2007 09:55:48', 1453, 'Passed' UNION ALL 
Select '8/1/2007 09:36:52', 1453, 'Failed' UNION ALL  
Select '7/3/2007 09:28:42', 1453, 'Passed' 


Declare @Id int
Set @Id = 1453

Select T2.*
from @T T2
Join (
	Select Max(Date) as Mdate, Id 
	from @T T1
	Where T1.id = @Id
	Group by Id, Year(Date) , Month(Date)
	) T on T.Mdate = T2.Date And T.Id = T2.Id

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

testdronetone
Starting Member

4 Posts

Posted - 2007-08-08 : 13:05:42

Pardon my ignorance please but I'm a bit cofused with your "inserts".
It's my faul because Query 3 should realy be entitled Table 3. I just need to know how to select from the table to get the results set.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-08 : 13:38:25

You can use the final SELECT statement and replace the @T with your tablename.
All the INSERTs are just to populate a table I created for me to query on.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

testdronetone
Starting Member

4 Posts

Posted - 2007-08-08 : 15:56:46

Hi. I cannot seem to get your query to work. The query that I'm looking for would return the last time(RN_EXECUTION_DATE and RN_EXECUTION_TIME each RN_TEST_ID was executed (RN_STATUS) in each represented month.
Here is the table: (this is just a sample dataset of thousands of rows)

Table = td.run
RN_EXECUTION_DATE RN_EXECUTION_TIME RN_TEST_ID RN_STATUS
8/2/2007 11:14:51 1421 Passed
8/2/2007 10:32:17 1420 Passed
8/2/2007 09:30:28 1420 Failed
8/2/2007 09:29:20 1418 Passed
8/2/2007 09:18:45 1418 Failed
8/1/2007 16:06:22 1416 Failed
8/1/2007 14:19:56 1413 Passed
8/1/2007 14:19:44 1413 Failed
8/1/2007 14:19:32 1414 Passed
8/1/2007 09:55:48 1453 Passed
8/1/2007 09:36:52 1453 Failed
7/3/2007 09:28:42 1453 Passed
6/10/2006 09:44:20 315 Passed
6/10/2006 09:42:01 315 Failed

This is what the results set should look like:

8/2/2007 11:14:51 1421 Passed
8/2/2007 10:32:17 1420 Passed
8/2/2007 09:29:20 1418 Passed
8/1/2007 16:06:22 1416 Failed
8/1/2007 14:19:56 1413 Passed
8/1/2007 14:19:32 1414 Passed
8/1/2007 09:55:48 1453 Passed
7/3/2007 09:28:42 1453 Passed
6/10/2006 09:44:20 315 Passed

Thanks!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-08 : 16:15:17

The code I gave was for a particular Id. Just remove that line from the code and the rest of the query should give you exactly what you are looking for.


Select T2.*
from @T T2
Join (
	Select Max(Date) as Mdate, Id 
	from @T T1
	Where T1.id = @Id
	Group by Id, Year(Date) , Month(Date)
	) T on T.Mdate = T2.Date And T.Id = T2.Id

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

testdronetone
Starting Member

4 Posts

Posted - 2007-08-08 : 18:48:20

I tried your query and the results will return
both rows for RN_TEST_ID 343 (found at the bottom of this recordset)
343 was returned twice out of my db with your query. I'm not sure why. There are other records that returned twice in the same month also in the recordset from my db. Any clues?
Thank you very much for your continued help.

Table = td.run
RN_EXECUTION_DATE RN_EXECUTION_TIME RN_TEST_ID RN_STATUS
8/2/2007 11:14:51 1421 Passed
8/2/2007 10:32:17 1420 Passed
8/2/2007 09:30:28 1420 Failed
8/2/2007 09:29:20 1418 Passed
8/2/2007 09:18:45 1418 Failed
8/1/2007 16:06:22 1416 Failed
8/1/2007 14:19:56 1413 Passed
8/1/2007 14:19:44 1413 Failed
8/1/2007 14:19:32 1414 Passed
8/1/2007 09:55:48 1453 Passed
8/1/2007 09:36:52 1453 Failed
7/3/2007 09:28:42 1453 Passed
6/10/2006 09:44:20 315 Passed
6/10/2006 09:42:01 315 Failed
4/4/2006 11:52:12 343 Passed
4/4/2006 09:56:24 343 Not Completed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources