| Author |
 Topic  |
|
|
ccbuilder
Starting Member
USA
13 Posts |
 Posted - 08/20/2012 : 21:55:41
|
Good evening, I am currently working with a query where i have to find the next record that has a null value, and I have the following columns
RowID (autoid), TaskID (int), UID varchar(10), DTS_Completed (datetime)
and the following sample data:
1,1,jdoe,2012-08-19 19:32:13.773
2,1,janedoe,NULL
3,1,sam,NULL
4,2,sam,NULL
5,2,jdoe,NULL
6,2,janedoe,NULL
7,3,janedoe,2012-08-20 15:55:16.123
8,3,jdoe,NULL
How can i find all TaskID items that require jdoe's attention but not before the person before him has completed their task
In this example, query would retrieve the following TaskID: 3
As always your help is greatly appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 08/20/2012 : 22:11:03
|
SELECT t1.*
FROM table t1
INNER JOIN table t2
ON t2.TaskID = t1.TaskID
AND t2.RowID = t1.RowID -1
WHERE t2.DTS_Completed IS NOT NULL
AND t1.DTS_Completed IS NULL
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
ccbuilder
Starting Member
USA
13 Posts |
Posted - 08/21/2012 : 00:03:30
|
Thanks for the quick response, how would this work if RowID was not sequential? In my test environment, I manually deleted some rows, so t1.RowID -1 would not yield any results in certain queries. In addition, after the first assignee, there's a program that adds more asignees to a Task once the first asignee completes his/her task.
Modifying the sample data:
1,1,jdoe,2012-08-19 19:32:13.773
2,2,janedoe,NULL
3,1,sam,NULL
4,2,sam,NULL
5,3,jdoe,2012-08-20 15:55:16.123
6,3,sam,NULL
7,3,janedoe,NULL
a query for sam, would yield RowID 3, and RowID 6
this because jdoe has completed his task for TaskID 1
and jdoe has completed his task for TaskID 3
a query for janedoe, would yield RowID 2
I really hope this makes sense.
Thanks in advance |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 08/21/2012 : 00:22:17
|
DECLARE @Yourvalue varchar(30)
SET @Yourvalue ='sam'
SELECT t1.*
FROM table t1
CROSS APPLY(SELECT DTS_Completed
FROM table
WHERE TaskID = t1.TaskID
AND RowID < t1.RowID
ORDER BY RowID DESC)t2
WHERE t2.DTS_Completed IS NOT NULL
AND t1.DTS_Completed IS NULL
AND t1.UID = @Yourvalue
you can pass any required user value through @Yourvalue
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
ccbuilder
Starting Member
USA
13 Posts |
Posted - 08/21/2012 : 00:35:37
|
Thanks for the prompt reply, i get the following: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Leaving the ORDER BY, i get two records |
|
|
|
ccbuilder
Starting Member
USA
13 Posts |
Posted - 08/21/2012 : 00:56:24
|
I had the wrong field column that's why i got two records, I left the ORDER BY out and it works.
Thanks again so much for your help  |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 08/21/2012 : 01:51:02
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Query with or without cursors
