SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there a way to do this without dynamic sql?


Is there a way to do this without dynamic sql?

Author
Message
jfogel
jfogel
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1176
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
jfogel
jfogel
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1176
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
TheGreenShepherd
TheGreenShepherd
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1776 Visits: 608
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.
jfogel
jfogel
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1176
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94892 Visits: 38960
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94892 Visits: 38960
TheGreenShepherd (6/13/2014)
Yep, exactly. Thanks.


Glad it worked. FYI, I did have to make some modify your inserts a bit to get things working. You should see the differences by comparing my code to what you had posted.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TheGreenShepherd
TheGreenShepherd
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1776 Visits: 608
Yeah, thanks. This isn't like my production code. I was just trying to generalize an example while I was on the train home from work. Sorry if it wasn't perfect. I appreciate your help.
jfogel
jfogel
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1176
Lynn Pettis (6/13/2014)
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.


I got that. I'm just not even sure what I was thinking. The whole question was how to do it without dynamic SQL and I turn around and propose something based on just that. I'm going to try and avoid touching anything important today!

Cheers
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94892 Visits: 38960
jfogel (6/13/2014)
Lynn Pettis (6/13/2014)
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.


I got that. I'm just not even sure what I was thinking. The whole question was how to do it without dynamic SQL and I turn around and propose something based on just that. I'm going to try and avoid touching anything important today!


I know how you feel.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sachin Nandanwar
Sachin Nandanwar
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1557 Visits: 2633
Another use of LEAD function
SELECT QueueId,TaskId,TaskName,Rundatetime FROM(
SELECT T.QueueId,T.TaskId,T.TaskName,T.RunDateTime, LEAD(taskid,MaximumQueueLength)OVER(PARTITION BY T.QueueId ORDER BY taskid DESC)lid FROM Tasks T
INNER JOIN Queue Q ON T.QueueId=Q.QueueId
)T1 WHERE lid IS NULL ORDER BY TaskName



EDIT: Works with SQL Server 2012 and above

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search