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
 SQL Min Max Help

Author  Topic 

tlug
Starting Member

21 Posts

Posted - 2013-08-28 : 14:44:47
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
Master Smack Fu Yak Hacker

4614 Posts

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-28 : 16:06:45
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

21 Posts

Posted - 2013-08-28 : 17:30:14
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

21 Posts

Posted - 2013-08-28 : 17:41:57
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

21 Posts

Posted - 2013-08-28 : 18:52:34
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-28 : 19:01:17
[code]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][/code]
Go to Top of Page

tlug
Starting Member

21 Posts

Posted - 2013-08-29 : 07:44:40
That worked, thanks a lot for the help!

Thanks!
Go to Top of Page
   

- Advertisement -