Query with or without cursors - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query with or without cursors

Author

Topic

ccbuilder
Starting Member

17 Posts

Posted - 2012-08-20 : 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

52326 Posts

Posted - 2012-08-20 : 22:11:03

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ccbuilder
Starting Member

17 Posts

Posted - 2012-08-21 : 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

52326 Posts

Posted - 2012-08-21 : 00:22:17

[code]
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
[/code]

you can pass any required user value through @Yourvalue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ccbuilder
Starting Member

17 Posts

Posted - 2012-08-21 : 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

17 Posts

Posted - 2012-08-21 : 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

52326 Posts

Posted - 2012-08-21 : 01:51:02

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources