June 12, 2014 at 4:30 pm
select
Queue.ScheduledTaskQueueId QueueId,
Queue.QueueMaximumLength,
Queue.IsLargeQueue
from
ScheduledTaskQueue Queue
I use the above SQL to find a list of queues to fill with scheduled tasks. Based off of the QueueMaximumLength value, I need to get the TOP X records from my ScheduledTask table ordered by the ScheduledTask.RunDateTime column.
Is there a way to do this without dynamic SQL?
June 12, 2014 at 4:38 pm
TOP clause accepts variables and expressions.
Example:
SELECT TOP(DATEPART( HH, GETDATE()))
*
FROM master.sys.all_columns
I'm not sure if that helps you because I'm not sure on how do you want to limit the rows.
June 12, 2014 at 4:44 pm
Ok, right..but if I have multiple queues, each with a different MaximumQueueLength, how would I use TOP without using either a cursor or dynamic SQL?
June 12, 2014 at 5:03 pm
I am not sure what you are trying to accomplish. The query you posted doesn't appear to be what you are asking about, or I just don't understand the question.
June 12, 2014 at 5:13 pm
Ok, let me give the whole picture.
I have three tables - Queue, Tasks, and QueueTaskLink
CREATE TABLE Queue
(
QueueId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
MaximumQueueLength INT NOT NULL
)
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
TaskName NVARCHAR(100) NOT NULL,
RunDatetime DATETIME NOT NULL
)
CREATE TABLE QueueTaskLink
(
QueueTaskLinkId NOT NULL PRIMARY KEY IDENTITY(1,1),
QueueId INT NOT NULL,
TaskId INT NOT NULL
)
Task is a table that contains a list of scheduled tasks. This table gets populated from various sources. Each task belongs in a queue, and each queue has a maximum number of tasks that can be run at a given time. I have a process that needs to get a list of all tasks that can be run for each queue.
Previously, I was accomplishing this with a SSIS task that gets a list of all queues, loops through each queue to build a string containing the select statement to get TOP X Tasks, where X is the MaximumQueueLength.
This feels cludgy to me and I'm wondering if there isn't a better way to do it.
June 12, 2014 at 5:29 pm
TheGreenShepherd (6/12/2014)
Ok, let me give the whole picture.I have three tables - Queue, Tasks, and QueueTaskLink
CREATE TABLE Queue
(
QueueId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
MaximumQueueLength INT NOT NULL
)
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
TaskName NVARCHAR(100) NOT NULL,
RunDatetime DATETIME NOT NULL
)
CREATE TABLE QueueTaskLink
(
QueueTaskLinkId NOT NULL PRIMARY KEY IDENTITY(1,1),
QueueId INT NOT NULL,
TaskId INT NOT NULL
)
Task is a table that contains a list of scheduled tasks. This table gets populated from various sources. Each task belongs in a queue, and each queue has a maximum number of tasks that can be run at a given time. I have a process that needs to get a list of all tasks that can be run for each queue.
Previously, I was accomplishing this with a SSIS task that gets a list of all queues, loops through each queue to build a string containing the select statement to get TOP X Tasks, where X is the MaximumQueueLength.
This feels cludgy to me and I'm wondering if there isn't a better way to do it.
Okay, so where does ScheduledTaskQueue table come in? That is the table in your query in your initial post.
Now that we have some of the tables, perhaps some sample data and the expected results based on the sample data and we may be able help you better.
June 12, 2014 at 5:52 pm
Forget about the table I initially mentioned. I generalized it down to those in my last post (and, actually, I'm going to generalize it even further to get rid of my QueueTaskLink, so that Task just has a QueueId foreign key column).
From those tables, you could insert these records:
INSERT INTO Queue (MaximumQueueLength) VALUES (3) --QueueId 1
INSERT INTO Queue (MaximumQueueLength) VALUES (5) --QueueId 2
INSERT INTO Queue (MaximumQueueLength) VALUES (1) --QueueId 3
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 1', 1, GETDATE()+1)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 2', 1, GETDATE()+2)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 3', 1, GETDATE()+3)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 4', 1, GETDATE()+4)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 5', 1, GETDATE()+5)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report A', 2, GETDATE()+6)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report B', 2, GETDATE()+7)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report C', 2, GETDATE()+8)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report D', 2, GETDATE()+9)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report E', 2, GETDATE()+1)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report F', 2, GETDATE()+2)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report G', 2, GETDATE()+3)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Big Export 1', 3, GETDATE())
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Big Export 2', 3, GETDATE()+1)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Big Export 3', 3, GETDATE()+2)
Ok, so now we have our tables populated. Queue 1 can handle 3 tasks at a time. Queue 2 handles 5. Queue 3 handles 1.
For each queue, I want to - select Top QueueMaximumLength TaskId from Task order by RunDatetime.
So, for Queue 1, I should get TaskId's 1,2,3 and not 4,5.
For Queue 2, I should get TaskId's 6,7,10,11,12 and not 8,9.
For Queue 3, I should get TaskId 14 and not 15,16.
Make sense?
June 12, 2014 at 6:08 pm
The inserts don't match the table definitions.
June 12, 2014 at 6:20 pm
In an empty database run the following and let us know if this is what you are looking to achieve.
CREATE TABLE dbo.[Queue]
(
QueueId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
MaximumQueueLength INT NOT NULL
)
CREATE TABLE dbo.Tasks
(
TaskId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
TaskName NVARCHAR(100) NOT NULL,
QueueId int,
RunDatetime DATETIME NOT NULL
)
go
INSERT INTO dbo.[Queue] (MaximumQueueLength) VALUES (3) --QueueId 1
INSERT INTO dbo.[Queue] (MaximumQueueLength) VALUES (5) --QueueId 2
INSERT INTO dbo.[Queue] (MaximumQueueLength) VALUES (1) --QueueId 3
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 1', 1, GETDATE()+1)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 2', 1, GETDATE()+2)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 3', 1, GETDATE()+3)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 4', 1, GETDATE()+4)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Excel Export 5', 1, GETDATE()+5)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report A', 2, GETDATE()+6)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report B', 2, GETDATE()+7)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report C', 2, GETDATE()+8)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report D', 2, GETDATE()+9)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report E', 2, GETDATE()+1)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report F', 2, GETDATE()+2)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Report G', 2, GETDATE()+3)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Big Export 1', 3, GETDATE())
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Big Export 2', 3, GETDATE()+1)
INSERT INTO Tasks (TaskName, QueueId, RunDatetime)
VALUES
('Big Export 3', 3, GETDATE()+2)
select * from dbo.[Queue];
select * from dbo.Tasks order by QueueId, RunDatetime;
select
q.QueueId,
ca.TaskId,
ca.TaskName,
ca.RunDatetime
from
dbo.[Queue] q
cross apply (select top (q.MaximumQueueLength) t.TaskId, t.TaskName, t.RunDatetime from dbo.Tasks t where t.QueueId = q.QueueId order by t.RunDatetime asc)ca
;
go
drop table dbo.[Queue];
drop table dbo.Tasks;
go
June 13, 2014 at 9:15 am
Yep, exactly. Thanks.
June 13, 2014 at 10:01 am
Your insert statements, etc are a complete mess! That didn't help at all, but I hope what is posted below will. I do not claim this is the best, or even an efficient way to do this. And because I had difficulty prepping the that it is likely the results for task/queue are off. The point is to demonstrate a suggested way to pull off what you are looking to do.
set nocount on
declare
@SqlStmt nvarchar(max),
@Roller int,
@MaximumQueueLength int
Declare @RollerStore table(QueueID int, MaximumQueueLength int)
create table #Results (QueueID int, TaskID Int, RunDateTime date)
insert @RollerStore select QueueID,MaximumQueueLength from Queue
while exists (select 1 from @RollerStore)
begin
select top 1 @Roller=QueueID, @MaximumQueueLength=MaximumQueueLength from @RollerStore
set @SqlStmt='insert into #Results
select top '+convert(varchar(5),@MaximumQueueLength)+' T0.QueueID, T0.TaskID, t1.RunDatetime
from tasks t1 inner join QueueTaskLink T0
on T0.TaskID=T1.TaskID
where T0.QueueID='+convert(varchar(5),@Roller)+'
order by RunDatetime'
exec sp_executesql @SqlStmt
delete @RollerStore where QueueID=@roller
end
select * from #Results
The results are:
QueueID TaskID RunDateTime
----------- ----------- -----------
1 1 2014-06-14
1 2 2014-06-15
1 3 2014-06-16
2 13 2014-06-14
2 14 2014-06-15
2 15 2014-06-16
2 9 2014-06-19
2 10 2014-06-20
3 4 2014-06-13
Cheers
June 13, 2014 at 10:04 am
Here are the create/insert statements I used in my example:
drop table Queue
drop table Tasks
drop table QueueTaskLink
CREATE TABLE Queue(
QueueId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
MaximumQueueLength INT NOT NULL)
CREATE TABLE Tasks(
TaskId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
TaskName NVARCHAR(100) NOT NULL,
RunDatetime DATETIME NOT NULL)
CREATE TABLE QueueTaskLink(
QueueTaskLinkId int NOT NULL PRIMARY KEY IDENTITY(1,1),
QueueId INT NOT NULL,
TaskId INT NOT NULL)
INSERT INTO Queue (MaximumQueueLength) VALUES (3) --QueueId 1
INSERT INTO Queue (MaximumQueueLength) VALUES (5) --QueueId 2
INSERT INTO Queue (MaximumQueueLength) VALUES (1) --QueueId 3
INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Excel Export 1', GETDATE()+1)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Excel Export 2', GETDATE()+2)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Excel Export 3', GETDATE()+3)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Big Export 1', GETDATE())
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Big Export 2', GETDATE()+1)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Big Export 3', GETDATE()+2)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES ('Excel Export 4', GETDATE()+4)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Excel Export 5', GETDATE()+5)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report A', GETDATE()+6)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report B', GETDATE()+7)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report C', GETDATE()+8)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report D', GETDATE()+9)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report E', GETDATE()+1)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report F', GETDATE()+2)
INSERT INTO Tasks (TaskName, RunDatetime) VALUES('Report G', GETDATE()+3)
insert QueueTaskLink values(1,1)
insert QueueTaskLink values(1,2)
insert QueueTaskLink values(1,3)
insert QueueTaskLink values(1,7)
insert QueueTaskLink values(1,8)
insert QueueTaskLink values(2,9)
insert QueueTaskLink values(2,10)
insert QueueTaskLink values(2,11)
insert QueueTaskLink values(2,12)
insert QueueTaskLink values(2,13)
insert QueueTaskLink values(2,14)
insert QueueTaskLink values(2,15)
insert QueueTaskLink values(3,6)
insert QueueTaskLink values(3,5)
insert QueueTaskLink values(3,4)
Cheers
June 13, 2014 at 10:05 am
Hey thanks for the compliment. I was doing my best, trying to type it in on my phone on the train home from work. But thanks again. Also, Lynn's cross apply is what I was looking for. Thanks.
June 13, 2014 at 10:14 am
Ah, I see. Once I remarked out two of the select statements in Lynn's query before the cross apply I see just the desired result. Much more graceful method than mine.
Cheers
June 13, 2014 at 10:20 am
jfogel (6/13/2014)
Ah, I see. Once I remarked out two of the select statements in Lynn's query before the cross apply I see just the desired result. Much more graceful method than mine.
The first two selects were to display the initial data after the inserts. I used that to do a visual verification of my results.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply