SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Min Max Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tlug
Starting Member

USA
19 Posts

Posted - 08/28/2013 :  14:44:47  Show Profile  Reply with Quote
Hello,

I am having trouble coming up with a query to extract the min and max time for each Task.. I am using Teradata SQL. The data below shows two tasks which both have the same File and Task ID. Each task starts with a Submitted Status and ends with a Rejected Status. What I need is to get the min and max time for each one of those tasks.. Unfortuneately my data below consists of values that don't have a unique identifier that I can use to split the tasks.

Thank you in advance for the help.

File	Task	Description	Event Data	Event Time	Status
643	598	DV		2013/06/19	5:42:07		Submitted
643	598	DV		2013/06/19	5:52:48		Rejected
643	598	DV		2013/06/27	5:44:29		Submitted
643	598	DV		2013/06/27	6:02:47		Rejected


Thanks!

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 08/28/2013 :  15:53:46  Show Profile  Reply with Quote
What do you want for output? Just two rows or one row per task?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 08/28/2013 :  16:06:45  Show Profile  Reply with Quote
Here is a guess:
DECLARE @Table TABLE 
(
	[File] INT,
	Task INT,
	Description VARCHAR(100),
	[Event Data] DATE,
	[Event Time] TIME,
	Status VARCHAR(25)
)

INSERT @Table VALUES
(643, 598, 'DV', '2013/06/19', '5:42:07', 'Submitted'),
(643, 598, 'DV', '2013/06/19', '5:52:48', 'Rejected'),
(643, 598, 'DV', '2013/06/27', '5:44:29', 'Submitted'),
(643, 598, 'DV', '2013/06/27', '6:02:47', 'Rejected')


SELECT
	*
FROM
	(
		SELECT 
			*,
			ROW_NUMBER() OVER (PARTITION BY Task, Status ORDER BY CAST([Event Data] AS DATETIME) + CAST([Event Time] AS DATETIME)) AS MinDate,
			ROW_NUMBER() OVER (PARTITION BY Task, Status ORDER BY CAST([Event Data] AS DATETIME) + CAST([Event Time] AS DATETIME) DESC) AS MaxDate
		FROM @Table
	) AS T
WHERE
	(
		Status = 'Rejected' 
		AND MaxDate = 1
	)
	OR
	(
		Status = 'Submitted' 
		AND MinDate = 1
	)
Go to Top of Page

tlug
Starting Member

USA
19 Posts

Posted - 08/28/2013 :  17:30:14  Show Profile  Reply with Quote
Thanks for the help, I am looking for two rows.. One task includes the submitted and rejected, I am looking for the min time(submitted) and the max time (rejected) for each task.

Thanks!
Go to Top of Page

tlug
Starting Member

USA
19 Posts

Posted - 08/28/2013 :  17:41:57  Show Profile  Reply with Quote
So it would look something like this..

File	Task	Description	Date	     Min	     Max
642	598	DV	         2013/06/19   5:42:07	     5:52:48
642	598	DV	         2013/06/27   5:44:29	     6:02:47


Thanks!
Go to Top of Page

tlug
Starting Member

USA
19 Posts

Posted - 08/28/2013 :  18:52:34  Show Profile  Reply with Quote
Thanks for the help, I am looking for two rows.. One task includes the submitted and rejected, I am looking for the min time(submitted) and the max time (rejected) for each task.

Thanks!
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 08/28/2013 :  19:01:17  Show Profile  Reply with Quote
SELECT
	[File],
	Task,
	Description,
	[Event Data],
	MIN(CASE WHEN Status = 'Submitted' THEN [Event Time] ELSE NULL END) AS MinTime,
	MAX(CASE WHEN Status = 'Rejected' THEN [Event Time] ELSE NULL END) AS MaxTime
FROM	
	@Table
GROUP BY
	[File],
	Task,
	Description,
	[Event Data]
Go to Top of Page

tlug
Starting Member

USA
19 Posts

Posted - 08/29/2013 :  07:44:40  Show Profile  Reply with Quote
That worked, thanks a lot for the help!

Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000