Selecting only Certain Rows based on a Date

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

Selecting only Certain Rows based on a Date

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2015-03-17 : 10:49:07

Here's my current SQL:


SELECT 
   RN_TEST_ID AS 'Test ID', 
   MAX(RN_EXECUTION_DATE) AS 'Last Execution Date', 
   MAX(RN_EXECUTION_TIME) AS 'Execution Time', 
   RN_DURATION AS 'Run Duration' 
FROM RUN 
WHERE 
   (RN_STATUS = 'Passed' OR RN_STATUS = 'Failed') AND
   RN_EXECUTION_DATE BETWEEN '2/1/15' AND '2/28/15' 
GROUP BY 
   RN_TEST_ID, 
   RN_EXECUTION_DATE, 
   RN_EXECUTION_TIME,
   RN_DURATION, 
   RN_STATUS
ORDER BY
   RN_TEST_ID

Here's a sample of data returned:

Test ID Last Execution Date Execution Time Run Duration
8672 2/9/2015 0:00 12:08:16 180
8682 2/9/2015 0:00 11:29:06 181
8684 2/9/2015 0:00 08:29:17 119
10525 2/3/2015 0:00 19:03:40 89
10525 2/3/2015 0:00 19:10:13 305
10668 2/3/2015 0:00 18:55:43 103
10668 2/6/2015 0:00 18:10:50 123
11457 2/3/2015 0:00 11:40:07 26

What I need are two things:

1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.

For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.

Not sure where to begin. Any help is greatly appreciated. Thanks.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-17 : 11:04:53

Try something like:


WITH RunOrder
AS
(
	SELECT *
		,ROW_NUMBER() OVER
			 (
				PARTITION BY RN_TEST_ID
				ORDER BY RN_EXECUTION_DATE DESC, RN_EXECUTION_TIME DESC, RN_DURATION DESC
			) AS rn
	FROM Run
	WHERE RN_STATUS IN ('Passed', 'Failed')
		-- Always specify date constants in ISO format.
		AND RN_EXECUTION_DATE BETWEEN '20150201' AND '20150228'
)
SELECT *
FROM RunOrder
WHERE rn = 1;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 11:07:36

you can usually do this kind of thing like this:


select ...
from RUN
join (
    select rn_test_id, MAX(RN_EXECUTION_DATE) AS max_RN_EXECUTION_DATE, 
   MAX(RN_EXECUTION_TIME) AS max_RN_EXECUTION_TIME
   from RUn
   group by ...
) q

on run.RN_EXECUTION_DATE = q.max_RN_EXECUTION_DATE and run.RN_EXECUTION_TIME = q.max_RN_EXECUTION_TIME

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2015-03-17 : 11:38:40

OK, so I tried this:

select RN_TEST_ID AS 'Test ID', MAX(RN_EXECUTION_DATE) AS 'Last Execution Date',  MAX(RN_EXECUTION_TIME) AS 'Execution Time',
       RN_DURATION AS 'Run Duration'
from RUN
join (
   select RN_TEST_ID, MAX(RN_EXECUTION_DATE) AS max_RN_EXECUTION_DATE,
   MAX(RN_EXECUTION_TIME) AS max_RN_EXECUTION_TIME
   from RUN
   group by RN_TEST_ID, RN_EXECUTION_DATE, RN_EXECUTION_TIME, RN_DURATION, RN_STATUS
) q

on run.RN_EXECUTION_DATE = q.max_RN_EXECUTION_DATE and run.RN_EXECUTION_TIME = q.max_RN_EXECUTION_TIME

But I get error: Ambiguous column name RN_TEST_ID

I don't see how it's ambiguous. Any idea?

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2015-03-17 : 11:56:17

OK, I get the ambiguous ... this sql works for me now - thank you:

select r.RN_TEST_ID AS 'Test ID', MAX(r.RN_EXECUTION_DATE) AS 'Last Execution Date',  MAX(r.RN_EXECUTION_TIME) AS 'Execution Time',
       r.RN_DURATION AS 'Run Duration'
from RUN r
join (
   select RN_TEST_ID, MAX(RN_EXECUTION_DATE) AS max_RN_EXECUTION_DATE,
   MAX(RN_EXECUTION_TIME) AS max_RN_EXECUTION_TIME
   from RUN
   group by RN_TEST_ID, RN_EXECUTION_DATE, RN_EXECUTION_TIME, RN_DURATION, RN_STATUS
) q

on RN_EXECUTION_DATE = q.max_RN_EXECUTION_DATE and RN_EXECUTION_TIME = q.max_RN_EXECUTION_TIME

group by r.rn_test_id, r.rn_duration
order by r.rn_test_id desc

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2015-03-17 : 12:08:04

Spoke too soon. The alias for the RUN table (r) solved the ambiguity issue, but the query still returns multiple rows for a rn_test_id ... I need only one row and the row for the most recent date / time.

Here's a sample result followed by the query:

Sample Result - note, still have multiple rows for a test id:

8672 2/9/2015 0:00 12:08:16 180
8682 2/9/2015 0:00 11:29:06 181
8684 2/9/2015 0:00 08:29:17 119
8684 2/9/2015 0:00 11:57:30 10
8684 2/9/2015 0:00 11:58:00 0
10525 2/3/2015 0:00 19:03:40 89
10525 2/3/2015 0:00 19:10:13 305
10668 2/3/2015 0:00 18:55:43 103
10668 2/6/2015 0:00 18:10:50 123
11457 2/3/2015 0:00 11:40:07 26

select r.RN_TEST_ID AS 'Test ID', MAX(r.RN_EXECUTION_DATE) AS 'Last Execution Date',  MAX(r.RN_EXECUTION_TIME) AS 'Execution Time',
       r.RN_DURATION AS 'Run Duration'
from RUN r

join (
   select RN_TEST_ID, MAX(RN_EXECUTION_DATE) AS max_RN_EXECUTION_DATE,
   MAX(RN_EXECUTION_TIME) AS max_RN_EXECUTION_TIME
   from RUN
   group by RN_TEST_ID, RN_EXECUTION_DATE, RN_EXECUTION_TIME, RN_DURATION, RN_STATUS
) q

on RN_EXECUTION_DATE = q.max_RN_EXECUTION_DATE and RN_EXECUTION_TIME = q.max_RN_EXECUTION_TIME
where r.rn_execution_date between '20150201' and '20150228'
group by r.rn_test_id, r.rn_duration, r.rn_execution_date, r.rn_execution_time
order by r.rn_test_id, r.rn_execution_date, r.rn_execution_time, r.rn_duration asc

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 12:23:17

You need to add RN_Test_ID to the join conditions. You should not have

RN_EXECUTION_DATE, RN_EXECUTION_TIME, RN_DURATION, RN_STATUS

in the group by. It defeats the purpose of the grouping.

something more like this:


select r.RN_TEST_ID AS 'Test ID', r.RN_EXECUTION_DATE AS 'Last Execution Date',  r.RN_EXECUTION_TIME AS 'Execution Time',
       r.RN_DURATION AS 'Run Duration'
from RUN r

join (
   select RN_TEST_ID, MAX(RN_EXECUTION_DATE) AS max_RN_EXECUTION_DATE,
   MAX(RN_EXECUTION_TIME) AS max_RN_EXECUTION_TIME
   from RUN
   group by RN_TEST_ID
) q

on r.RN_TEST_ID = q.RN_TEST_ID  and r.RN_EXECUTION_DATE = q.max_RN_EXECUTION_DATE and t.RN_EXECUTION_TIME = q.max_RN_EXECUTION_TIME
where r.rn_execution_date between '20150201' and '20150228'

order by r.rn_test_id, r.rn_execution_date, r.rn_execution_time, r.rn_duration asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources