Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Is there a way to do this without dynamic sql? Expand / Collapse
Author
Message
Posted Friday, June 13, 2014 10:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:24 AM
Points: 371, Visits: 967
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
Post #1580605
Posted Friday, June 13, 2014 10:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:24 AM
Points: 371, Visits: 967
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
Post #1580609
Posted Friday, June 13, 2014 10:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:44 PM
Points: 570, Visits: 475
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.
Post #1580610
Posted Friday, June 13, 2014 10:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:24 AM
Points: 371, Visits: 967
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
Post #1580617
Posted Friday, June 13, 2014 10:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 20,685, Visits: 32,290
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.



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)
Post #1580620
Posted Friday, June 13, 2014 10:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 20,685, Visits: 32,290
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.



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)
Post #1580621
Posted Friday, June 13, 2014 10:24 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:44 PM
Points: 570, Visits: 475
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.
Post #1580623
Posted Friday, June 13, 2014 10:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:24 AM
Points: 371, Visits: 967
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
Post #1580624
Posted Friday, June 13, 2014 10:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 20,685, Visits: 32,290
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.



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)
Post #1580640
Posted Monday, June 16, 2014 1:55 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
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
Post #1581014
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse