Peak During Time Period

Hi
I am hoping someone might be able to help me out with this.
I am writing a helpdesk system which records agents logging in and out
of the system.
I need to write a stored procedure which will show the peak number of
agents logged in concurrently during a specified time period. Within
the time period, the person viewing the report should be able to
specify intervals at which to see the data.
Eg. There is already a table in the system which holds logged
in/logged out data like
22/11/2004 14:02 - 22/11/2004 17:30
22/11/2004 09:00 - 22/11/2004 17:12
22/11/2004 10:25 - 22/11/2004 16:30
22/11/2004 11:02 - 22/11/2004 12:30
22/11/2004 16:00 - 22/11/2004 17:30
The report user can then say for example they want to view data
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this:
10/11/2004 00:00 - 10/11/2004 00:15
10/11/2004 00:15 - 10/11/2004 00:30
10/11/2004 00:30 - 10/11/2004 00:45
10/11/2004 00:45 - 10/11/2004 01:00 etc etc
Against each of these time slots, I need to work out the peak number
of concurrent agents logged in using the first table.
Can anyone make any suggestions? The time period the report user can
choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
Thanks in advance
[posted and mailed, please reply in news]
Dave (dave@.court-technologies.com) writes:
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
The normal recommendation for this sort of post is to include:
o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired output given the sample.
This makes it easy to post a tested solution, since the dirty work is
already set up, and it's only to cut and paste.
This time I did it for you, because the problem seemed interesting enough.
First I set up a table of numbers. This is a one-column table with numbers
1 to whatever the limit (80000 in this case, that's 55 days). The I packed
the actual query in a stored procedure to easily permit for parameters.
@.len is the length of the reporting interval in minutes.
The query has a number of nested derived tables. The innermost gives
the number of agents logged in at any given minute. The middle table,
normalizes the minute to the start of the reporting interval, and
the outermost, get the maximum count for each interval.
Further testing is recommended!
CREATE TABLE sessions (start datetime NOT NULL,
stop datetime NULL)
go
SET DATEFORMAT dmy
go
SELECT TOP 80000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
go
INSERT sessions (start, stop)
SELECT '22/11/2004 14:02', '22/11/2004 17:30' UNION
SELECT '22/11/2004 09:00', '22/11/2004 17:12' UNION
SELECT '22/11/2004 10:25', '22/11/2004 16:30' UNION
SELECT '22/11/2004 11:02', '22/11/2004 12:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 17:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 16:05' UNION
SELECT '22/11/2004 16:06', '22/11/2004 16:10'
go
CREATE PROCEDURE get_peaks @.start datetime,
@.stop datetime,
@.len smallint AS
SELECT intstart, intstop = dateadd(mi, @.len, intstart), MAX(cnt)
FROM (SELECT intstart = dateadd(mi, @.len *
(datediff(mi, @.start, a.minute) / @.len), @.start),
a.cnt
FROM (SELECT mi.minute, cnt = COUNT(s.start)
FROM (SELECT minute = dateadd(mi, n, @.start)
FROM numbers
WHERE n <= datediff(mi, @.start, @.stop)) AS mi
LEFT JOIN sessions s
ON mi.minute BETWEEN s.start AND s.stop
GROUP BY mi.minute) AS a
) AS b
GROUP BY intstart
ORDER BY intstart
go
EXEC get_peaks '20041122 08:00', '20041122 18:00', 15
go
DROP TABLE numbers
DROP TABLE sessions
DROP PROCEDURE get_peaks
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||>> I am writing a helpdesk system which records agents logging in and
out of the system. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Does your boss, who is paying you, hide this
information and expect you to get your job done?
You might also want to learn that the only format for temporal data in
Standard SQL is ISO-8601 (yyyy-mm-dd hh:mm:ss.sss...) and start using
it; you can never tell, other systems just might follow iSO standards

[vbcol=seagreen]
of agents logged in concurrently during a specified time period. <<
Why not VIEWs? SQL is a non-procdural language after all. If you had
followed minimal netiquette, would this table lok liket his?
CREATE TABLE HelpDeskLogs
(agent_id CHAR(5) NOT NULL
REFERENCES Agents(agent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
finish_time DATETIME, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (agent_id, start_time));
[vbcol=seagreen]
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this: <<
Let's fill up a table of ranges:
CREATE TABLE ReportPeriods
(period_scale CHAR(2) DEFAULT '15' NOT NULL,
CHECK (period_scale IN ('15', '30', '45', 'HR', 'DY')
start_time DATETIME NOT NULL,
finish_time DATETIME NOT NULL, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (period_scale, start_time));
In standard SQL, we have a predicate for durations like this:
SELECT COUNT(agent_id) AS active_agents
FROM ReportPeriods AS P, HelpDeskLogs AS L
WHERE (P.start_time, P.finish_time)
OVERLAPS (L.start_time, L.finish_time);
That predicate gets translated into this:
(P.start_time > L.start_time
AND NOT (P.start_time >= L.finish_time
AND P.finish_time >= L.finish_time))
OR (L.start_time > P.start_time
AND NOT (L.start_time >= P.finish_time
AND L.finish_time >= P.finish_time))
OR (P.start_time = L.start_time
AND (P.finish_time <> L.finish_time
OR P.finish_time = L.finish_time))
Yes, it is a bit weird because it has to handle NULLs in the general
case.
You might also want to look up Rick Snodgrass at the University of
Arizona. he has a copy of his book on Temporal quereis in SQL in PDF
on his university website.
|||"Dave" <dave@.court-technologies.com> wrote in message
news:7bbc1b13.0411250202.57f40780@.posting.google.c om...
> Hi
> I am hoping someone might be able to help me out with this.
> I am writing a helpdesk system which records agents logging in and out
> of the system.
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
> Can anyone make any suggestions? The time period the report user can
> choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
> Thanks in advance
CREATE TABLE LoginPeriods
(
agent_id VARCHAR(20) NOT NULL,
time_in DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (time_in <= CURRENT_TIMESTAMP),
time_out DATETIME NOT NULL DEFAULT '99991231'
CHECK (time_out <= CURRENT_TIMESTAMP OR time_out = '99991231'),
PRIMARY KEY (time_in, agent_id),
CHECK (time_in < time_out)
)
-- Your sample data
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A1', '20041122 14:02', '20041122 17:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A2', '20041122 09:00', '20041122 17:12')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A3', '20041122 10:25', '20041122 16:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A4', '20041122 11:02', '20041122 12:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A5', '20041122 16:00', '20041122 17:30')
-- Digits 0-9
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
-- Nonnegative integers to some suitable upper bound
-- Used in representing the sequence of time periods from
-- begin to end datetimes
CREATE VIEW NonnegativeIntegers (n)
AS
SELECT Ones.d + 10 * Tens.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
-- For each time period between begin and end datetimes,
-- return login periods that overlap
CREATE FUNCTION LoginPeriodsBetween
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AS begin_time,
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time) AS end_time,
agent_id,
CASE WHEN time_in <=
DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
THEN DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
ELSE time_in
END AS time_in,
CASE WHEN time_out <=
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
THEN time_out
ELSE DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
END AS time_out
FROM NonnegativeIntegers AS I
LEFT OUTER JOIN
LoginPeriods AS LP
ON time_out > DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AND
time_in < DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
WHERE I.n < DATEDIFF(MINUTE, @.begin_time, @.end_time) / @.period_mins
)
-- Maximum number of concurrent agent logins per time period
CREATE FUNCTION MaxConcurrentAgents
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT begin_time, end_time, MAX(concurrent_agents) AS concurrent_agents_tally
FROM (SELECT LP1.begin_time, LP1.end_time,
LP1.agent_id,
LP1.time_in, LP1.time_out,
COUNT(LP2.agent_id) AS concurrent_agents
FROM LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP1
LEFT OUTER JOIN
LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP2
ON LP1.begin_time = LP2.begin_time AND
LP1.end_time = LP2.end_time AND
LP1.time_in >= LP2.time_in AND
LP1.time_in < LP2.time_out
GROUP BY LP1.begin_time, LP1.end_time, LP1.agent_id,
LP1.time_in, LP1.time_out) AS CA
GROUP BY begin_time, end_time
)
-- Maximum number of concurrent agent logins for each 30 minute
-- period between the specified begin and end datetimes
-- Note that no logins for a time period will be indicated by a 0 tally
SELECT begin_time, end_time, concurrent_agents_tally
FROM MaxConcurrentAgents('20041122 09:00', '20041122 18:00', 30)
ORDER BY begin_time
begin_time end_time concurrent_agents_tally
2004-11-22 09:00:00.000 2004-11-22 09:30:00.000 1
2004-11-22 09:30:00.000 2004-11-22 10:00:00.000 1
2004-11-22 10:00:00.000 2004-11-22 10:30:00.000 2
2004-11-22 10:30:00.000 2004-11-22 11:00:00.000 2
2004-11-22 11:00:00.000 2004-11-22 11:30:00.000 3
2004-11-22 11:30:00.000 2004-11-22 12:00:00.000 3
2004-11-22 12:00:00.000 2004-11-22 12:30:00.000 3
2004-11-22 12:30:00.000 2004-11-22 13:00:00.000 2
2004-11-22 13:00:00.000 2004-11-22 13:30:00.000 2
2004-11-22 13:30:00.000 2004-11-22 14:00:00.000 2
2004-11-22 14:00:00.000 2004-11-22 14:30:00.000 3
2004-11-22 14:30:00.000 2004-11-22 15:00:00.000 3
2004-11-22 15:00:00.000 2004-11-22 15:30:00.000 3
2004-11-22 15:30:00.000 2004-11-22 16:00:00.000 3
2004-11-22 16:00:00.000 2004-11-22 16:30:00.000 4
2004-11-22 16:30:00.000 2004-11-22 17:00:00.000 3
2004-11-22 17:00:00.000 2004-11-22 17:30:00.000 3
2004-11-22 17:30:00.000 2004-11-22 18:00:00.000 0
JAG

Peak During Time Period

Hi
I am hoping someone might be able to help me out with this.
I am writing a helpdesk system which records agents logging in and out
of the system.
I need to write a stored procedure which will show the peak number of
agents logged in concurrently during a specified time period. Within
the time period, the person viewing the report should be able to
specify intervals at which to see the data.
Eg. There is already a table in the system which holds logged
in/logged out data like
22/11/2004 14:02 - 22/11/2004 17:30
22/11/2004 09:00 - 22/11/2004 17:12
22/11/2004 10:25 - 22/11/2004 16:30
22/11/2004 11:02 - 22/11/2004 12:30
22/11/2004 16:00 - 22/11/2004 17:30
The report user can then say for example they want to view data
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this:
10/11/2004 00:00 - 10/11/2004 00:15
10/11/2004 00:15 - 10/11/2004 00:30
10/11/2004 00:30 - 10/11/2004 00:45
10/11/2004 00:45 - 10/11/2004 01:00 etc etc
Against each of these time slots, I need to work out the peak number
of concurrent agents logged in using the first table.
Can anyone make any suggestions? The time period the report user can
choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
Thanks in advance[posted and mailed, please reply in news]
Dave (dave@.court-technologies.com) writes:
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
The normal recommendation for this sort of post is to include:
o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired output given the sample.
This makes it easy to post a tested solution, since the dirty work is
already set up, and it's only to cut and paste.
This time I did it for you, because the problem seemed interesting enough.
First I set up a table of numbers. This is a one-column table with numbers
1 to whatever the limit (80000 in this case, that's 55 days). The I packed
the actual query in a stored procedure to easily permit for parameters.
@.len is the length of the reporting interval in minutes.
The query has a number of nested derived tables. The innermost gives
the number of agents logged in at any given minute. The middle table,
normalizes the minute to the start of the reporting interval, and
the outermost, get the maximum count for each interval.
Further testing is recommended!
CREATE TABLE sessions (start datetime NOT NULL,
stop datetime NULL)
go
SET DATEFORMAT dmy
go
SELECT TOP 80000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
go
INSERT sessions (start, stop)
SELECT '22/11/2004 14:02', '22/11/2004 17:30' UNION
SELECT '22/11/2004 09:00', '22/11/2004 17:12' UNION
SELECT '22/11/2004 10:25', '22/11/2004 16:30' UNION
SELECT '22/11/2004 11:02', '22/11/2004 12:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 17:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 16:05' UNION
SELECT '22/11/2004 16:06', '22/11/2004 16:10'
go
CREATE PROCEDURE get_peaks @.start datetime,
@.stop datetime,
@.len smallint AS
SELECT intstart, intstop = dateadd(mi, @.len, intstart), MAX(cnt)
FROM (SELECT intstart = dateadd(mi, @.len *
(datediff(mi, @.start, a.minute) / @.len), @.start),
a.cnt
FROM (SELECT mi.minute, cnt = COUNT(s.start)
FROM (SELECT minute = dateadd(mi, n, @.start)
FROM numbers
WHERE n <= datediff(mi, @.start, @.stop)) AS mi
LEFT JOIN sessions s
ON mi.minute BETWEEN s.start AND s.stop
GROUP BY mi.minute) AS a
) AS b
GROUP BY intstart
ORDER BY intstart
go
EXEC get_peaks '20041122 08:00', '20041122 18:00', 15
go
DROP TABLE numbers
DROP TABLE sessions
DROP PROCEDURE get_peaks
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I am writing a helpdesk system which records agents logging in and
out of the system. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Does your boss, who is paying you, hide this
information and expect you to get your job done?
You might also want to learn that the only format for temporal data in
Standard SQL is ISO-8601 (yyyy-mm-dd hh:mm:ss.sss...) and start using
it; you can never tell, other systems just might follow iSO standards

[vbcol=seagreen]
of agents logged in concurrently during a specified time period. <<
Why not VIEWs? SQL is a non-procdural language after all. If you had
followed minimal netiquette, would this table lok liket his?
CREATE TABLE HelpDeskLogs
(agent_id CHAR(5) NOT NULL
REFERENCES Agents(agent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
finish_time DATETIME, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (agent_id, start_time));
[vbcol=seagreen]
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this: <<
Let's fill up a table of ranges:
CREATE TABLE ReportPeriods
(period_scale CHAR(2) DEFAULT '15' NOT NULL,
CHECK (period_scale IN ('15', '30', '45', 'HR', 'DY')
start_time DATETIME NOT NULL,
finish_time DATETIME NOT NULL, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (period_scale, start_time));
In standard SQL, we have a predicate for durations like this:
SELECT COUNT(agent_id) AS active_agents
FROM ReportPeriods AS P, HelpDeskLogs AS L
WHERE (P.start_time, P.finish_time)
OVERLAPS (L.start_time, L.finish_time);
That predicate gets translated into this:
(P.start_time > L.start_time
AND NOT (P.start_time >= L.finish_time
AND P.finish_time >= L.finish_time))
OR (L.start_time > P.start_time
AND NOT (L.start_time >= P.finish_time
AND L.finish_time >= P.finish_time))
OR (P.start_time = L.start_time
AND (P.finish_time <> L.finish_time
OR P.finish_time = L.finish_time))
Yes, it is a bit weird because it has to handle NULLs in the general
case.
You might also want to look up Rick Snodgrass at the university of
Arizona. he has a copy of his book on Temporal quereis in SQL in PDF
on his university website.|||"Dave" <dave@.court-technologies.com> wrote in message
news:7bbc1b13.0411250202.57f40780@.posting.google.com...
> Hi
> I am hoping someone might be able to help me out with this.
> I am writing a helpdesk system which records agents logging in and out
> of the system.
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
> Can anyone make any suggestions? The time period the report user can
> choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
> Thanks in advance
CREATE TABLE LoginPeriods
(
agent_id VARCHAR(20) NOT NULL,
time_in DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (time_in <= CURRENT_TIMESTAMP),
time_out DATETIME NOT NULL DEFAULT '99991231'
CHECK (time_out <= CURRENT_TIMESTAMP OR time_out = '99991231'),
PRIMARY KEY (time_in, agent_id),
CHECK (time_in < time_out)
)
-- Your sample data
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A1', '20041122 14:02', '20041122 17:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A2', '20041122 09:00', '20041122 17:12')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A3', '20041122 10:25', '20041122 16:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A4', '20041122 11:02', '20041122 12:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A5', '20041122 16:00', '20041122 17:30')
-- Digits 0-9
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9
-- Nonnegative integers to some suitable upper bound
-- Used in representing the sequence of time periods from
-- begin to end datetimes
CREATE VIEW NonnegativeIntegers (n)
AS
SELECT Ones.d + 10 * Tens.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
-- For each time period between begin and end datetimes,
-- return login periods that overlap
CREATE FUNCTION LoginPeriodsBetween
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AS begin_time,
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time) AS end_time,
agent_id,
CASE WHEN time_in <=
DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
THEN DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
ELSE time_in
END AS time_in,
CASE WHEN time_out <=
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
THEN time_out
ELSE DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
END AS time_out
FROM NonnegativeIntegers AS I
LEFT OUTER JOIN
LoginPeriods AS LP
ON time_out > DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AND
time_in < DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
WHERE I.n < DATEDIFF(MINUTE, @.begin_time, @.end_time) / @.period_mins
)
-- Maximum number of concurrent agent logins per time period
CREATE FUNCTION MaxConcurrentAgents
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT begin_time, end_time, MAX(concurrent_agents) AS concurrent_agents_tal
ly
FROM (SELECT LP1.begin_time, LP1.end_time,
LP1.agent_id,
LP1.time_in, LP1.time_out,
COUNT(LP2.agent_id) AS concurrent_agents
FROM LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP1
LEFT OUTER JOIN
LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP2
ON LP1.begin_time = LP2.begin_time AND
LP1.end_time = LP2.end_time AND
LP1.time_in >= LP2.time_in AND
LP1.time_in < LP2.time_out
GROUP BY LP1.begin_time, LP1.end_time, LP1.agent_id,
LP1.time_in, LP1.time_out) AS CA
GROUP BY begin_time, end_time
)
-- Maximum number of concurrent agent logins for each 30 minute
-- period between the specified begin and end datetimes
-- Note that no logins for a time period will be indicated by a 0 tally
SELECT begin_time, end_time, concurrent_agents_tally
FROM MaxConcurrentAgents('20041122 09:00', '20041122 18:00', 30)
ORDER BY begin_time
begin_time end_time concurrent_agents_tally
2004-11-22 09:00:00.000 2004-11-22 09:30:00.000 1
2004-11-22 09:30:00.000 2004-11-22 10:00:00.000 1
2004-11-22 10:00:00.000 2004-11-22 10:30:00.000 2
2004-11-22 10:30:00.000 2004-11-22 11:00:00.000 2
2004-11-22 11:00:00.000 2004-11-22 11:30:00.000 3
2004-11-22 11:30:00.000 2004-11-22 12:00:00.000 3
2004-11-22 12:00:00.000 2004-11-22 12:30:00.000 3
2004-11-22 12:30:00.000 2004-11-22 13:00:00.000 2
2004-11-22 13:00:00.000 2004-11-22 13:30:00.000 2
2004-11-22 13:30:00.000 2004-11-22 14:00:00.000 2
2004-11-22 14:00:00.000 2004-11-22 14:30:00.000 3
2004-11-22 14:30:00.000 2004-11-22 15:00:00.000 3
2004-11-22 15:00:00.000 2004-11-22 15:30:00.000 3
2004-11-22 15:30:00.000 2004-11-22 16:00:00.000 3
2004-11-22 16:00:00.000 2004-11-22 16:30:00.000 4
2004-11-22 16:30:00.000 2004-11-22 17:00:00.000 3
2004-11-22 17:00:00.000 2004-11-22 17:30:00.000 3
2004-11-22 17:30:00.000 2004-11-22 18:00:00.000 0
JAG

Peak During Time Period

Hi

I am hoping someone might be able to help me out with this.

I am writing a helpdesk system which records agents logging in and out
of the system.

I need to write a stored procedure which will show the peak number of
agents logged in concurrently during a specified time period. Within
the time period, the person viewing the report should be able to
specify intervals at which to see the data.

Eg. There is already a table in the system which holds logged
in/logged out data like

22/11/2004 14:02 - 22/11/2004 17:30
22/11/2004 09:00 - 22/11/2004 17:12
22/11/2004 10:25 - 22/11/2004 16:30
22/11/2004 11:02 - 22/11/2004 12:30
22/11/2004 16:00 - 22/11/2004 17:30

The report user can then say for example they want to view data
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this:

10/11/2004 00:00 - 10/11/2004 00:15
10/11/2004 00:15 - 10/11/2004 00:30
10/11/2004 00:30 - 10/11/2004 00:45
10/11/2004 00:45 - 10/11/2004 01:00 etc etc

Against each of these time slots, I need to work out the peak number
of concurrent agents logged in using the first table.

Can anyone make any suggestions? The time period the report user can
choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.

Thanks in advance[posted and mailed, please reply in news]

Dave (dave@.court-technologies.com) writes:
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.

The normal recommendation for this sort of post is to include:

o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it easy to post a tested solution, since the dirty work is
already set up, and it's only to cut and paste.

This time I did it for you, because the problem seemed interesting enough.
First I set up a table of numbers. This is a one-column table with numbers
1 to whatever the limit (80000 in this case, that's 55 days). The I packed
the actual query in a stored procedure to easily permit for parameters.
@.len is the length of the reporting interval in minutes.

The query has a number of nested derived tables. The innermost gives
the number of agents logged in at any given minute. The middle table,
normalizes the minute to the start of the reporting interval, and
the outermost, get the maximum count for each interval.

Further testing is recommended!

CREATE TABLE sessions (start datetime NOT NULL,
stop datetime NULL)
go
SET DATEFORMAT dmy
go
SELECT TOP 80000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
go
INSERT sessions (start, stop)
SELECT '22/11/2004 14:02', '22/11/2004 17:30' UNION
SELECT '22/11/2004 09:00', '22/11/2004 17:12' UNION
SELECT '22/11/2004 10:25', '22/11/2004 16:30' UNION
SELECT '22/11/2004 11:02', '22/11/2004 12:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 17:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 16:05' UNION
SELECT '22/11/2004 16:06', '22/11/2004 16:10'
go
CREATE PROCEDURE get_peaks @.start datetime,
@.stop datetime,
@.len smallint AS

SELECT intstart, intstop = dateadd(mi, @.len, intstart), MAX(cnt)
FROM (SELECT intstart = dateadd(mi, @.len *
(datediff(mi, @.start, a.minute) / @.len), @.start),
a.cnt
FROM (SELECT mi.minute, cnt = COUNT(s.start)
FROM (SELECT minute = dateadd(mi, n, @.start)
FROM numbers
WHERE n <= datediff(mi, @.start, @.stop)) AS mi
LEFT JOIN sessions s
ON mi.minute BETWEEN s.start AND s.stop
GROUP BY mi.minute) AS a
) AS b
GROUP BY intstart
ORDER BY intstart
go
EXEC get_peaks '20041122 08:00', '20041122 18:00', 15
go
DROP TABLE numbers
DROP TABLE sessions
DROP PROCEDURE get_peaks

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I am writing a helpdesk system which records agents logging in and
out of the system. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Does your boss, who is paying you, hide this
information and expect you to get your job done?

You might also want to learn that the only format for temporal data in
Standard SQL is ISO-8601 (yyyy-mm-dd hh:mm:ss.sss...) and start using
it; you can never tell, other systems just might follow iSO standards
:)

>> I need to write a stored procedure which will show the peak number
of agents logged in concurrently during a specified time period. <<

Why not VIEWs? SQL is a non-procdural language after all. If you had
followed minimal netiquette, would this table lok liket his?

CREATE TABLE HelpDeskLogs
(agent_id CHAR(5) NOT NULL
REFERENCES Agents(agent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
finish_time DATETIME, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (agent_id, start_time));

>> The report user can then say for example they want to view data
between 10th November and 12th November broken down into 15 minutes
intervals which would produce a table like this: <<

Let's fill up a table of ranges:

CREATE TABLE ReportPeriods
(period_scale CHAR(2) DEFAULT '15' NOT NULL,
CHECK (period_scale IN ('15', '30', '45', 'HR', 'DY')
start_time DATETIME NOT NULL,
finish_time DATETIME NOT NULL, -- null means still active
CHECK (start_time < finish_time),
PRIMARY KEY (period_scale, start_time));

In standard SQL, we have a predicate for durations like this:

SELECT COUNT(agent_id) AS active_agents
FROM ReportPeriods AS P, HelpDeskLogs AS L
WHERE (P.start_time, P.finish_time)
OVERLAPS (L.start_time, L.finish_time);

That predicate gets translated into this:

(P.start_time > L.start_time
AND NOT (P.start_time >= L.finish_time
AND P.finish_time >= L.finish_time))
OR (L.start_time > P.start_time
AND NOT (L.start_time >= P.finish_time
AND L.finish_time >= P.finish_time))
OR (P.start_time = L.start_time
AND (P.finish_time <> L.finish_time
OR P.finish_time = L.finish_time))

Yes, it is a bit weird because it has to handle NULLs in the general
case.

You might also want to look up Rick Snodgrass at the University of
Arizona. he has a copy of his book on Temporal quereis in SQL in PDF
on his university website.|||"Dave" <dave@.court-technologies.com> wrote in message
news:7bbc1b13.0411250202.57f40780@.posting.google.c om...
> Hi
> I am hoping someone might be able to help me out with this.
> I am writing a helpdesk system which records agents logging in and out
> of the system.
> I need to write a stored procedure which will show the peak number of
> agents logged in concurrently during a specified time period. Within
> the time period, the person viewing the report should be able to
> specify intervals at which to see the data.
> Eg. There is already a table in the system which holds logged
> in/logged out data like
> 22/11/2004 14:02 - 22/11/2004 17:30
> 22/11/2004 09:00 - 22/11/2004 17:12
> 22/11/2004 10:25 - 22/11/2004 16:30
> 22/11/2004 11:02 - 22/11/2004 12:30
> 22/11/2004 16:00 - 22/11/2004 17:30
> The report user can then say for example they want to view data
> between 10th November and 12th November broken down into 15 minutes
> intervals which would produce a table like this:
> 10/11/2004 00:00 - 10/11/2004 00:15
> 10/11/2004 00:15 - 10/11/2004 00:30
> 10/11/2004 00:30 - 10/11/2004 00:45
> 10/11/2004 00:45 - 10/11/2004 01:00 etc etc
> Against each of these time slots, I need to work out the peak number
> of concurrent agents logged in using the first table.
> Can anyone make any suggestions? The time period the report user can
> choose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.
> Thanks in advance

CREATE TABLE LoginPeriods
(
agent_id VARCHAR(20) NOT NULL,
time_in DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (time_in <= CURRENT_TIMESTAMP),
time_out DATETIME NOT NULL DEFAULT '99991231'
CHECK (time_out <= CURRENT_TIMESTAMP OR time_out = '99991231'),
PRIMARY KEY (time_in, agent_id),
CHECK (time_in < time_out)
)

-- Your sample data
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A1', '20041122 14:02', '20041122 17:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A2', '20041122 09:00', '20041122 17:12')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A3', '20041122 10:25', '20041122 16:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A4', '20041122 11:02', '20041122 12:30')
INSERT INTO LoginPeriods (agent_id, time_in, time_out)
VALUES ('A5', '20041122 16:00', '20041122 17:30')

-- Digits 0-9
CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9

-- Nonnegative integers to some suitable upper bound
-- Used in representing the sequence of time periods from
-- begin to end datetimes
CREATE VIEW NonnegativeIntegers (n)
AS
SELECT Ones.d + 10 * Tens.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens

-- For each time period between begin and end datetimes,
-- return login periods that overlap
CREATE FUNCTION LoginPeriodsBetween
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AS begin_time,
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time) AS end_time,
agent_id,
CASE WHEN time_in <=
DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
THEN DATEADD(MINUTE, I.n * @.period_mins, @.begin_time)
ELSE time_in
END AS time_in,
CASE WHEN time_out <=
DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
THEN time_out
ELSE DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
END AS time_out
FROM NonnegativeIntegers AS I
LEFT OUTER JOIN
LoginPeriods AS LP
ON time_out > DATEADD(MINUTE, I.n * @.period_mins, @.begin_time) AND
time_in < DATEADD(MINUTE, (I.n+1) * @.period_mins, @.begin_time)
WHERE I.n < DATEDIFF(MINUTE, @.begin_time, @.end_time) / @.period_mins
)

-- Maximum number of concurrent agent logins per time period
CREATE FUNCTION MaxConcurrentAgents
(@.begin_time DATETIME, @.end_time DATETIME, @.period_mins INT)
RETURNS TABLE
AS
RETURN(
SELECT begin_time, end_time, MAX(concurrent_agents) AS concurrent_agents_tally
FROM (SELECT LP1.begin_time, LP1.end_time,
LP1.agent_id,
LP1.time_in, LP1.time_out,
COUNT(LP2.agent_id) AS concurrent_agents
FROM LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP1
LEFT OUTER JOIN
LoginPeriodsBetween(@.begin_time, @.end_time, @.period_mins) AS LP2
ON LP1.begin_time = LP2.begin_time AND
LP1.end_time = LP2.end_time AND
LP1.time_in >= LP2.time_in AND
LP1.time_in < LP2.time_out
GROUP BY LP1.begin_time, LP1.end_time, LP1.agent_id,
LP1.time_in, LP1.time_out) AS CA
GROUP BY begin_time, end_time
)

-- Maximum number of concurrent agent logins for each 30 minute
-- period between the specified begin and end datetimes
-- Note that no logins for a time period will be indicated by a 0 tally
SELECT begin_time, end_time, concurrent_agents_tally
FROM MaxConcurrentAgents('20041122 09:00', '20041122 18:00', 30)
ORDER BY begin_time

begin_time end_time concurrent_agents_tally
2004-11-22 09:00:00.000 2004-11-22 09:30:00.000 1
2004-11-22 09:30:00.000 2004-11-22 10:00:00.000 1
2004-11-22 10:00:00.000 2004-11-22 10:30:00.000 2
2004-11-22 10:30:00.000 2004-11-22 11:00:00.000 2
2004-11-22 11:00:00.000 2004-11-22 11:30:00.000 3
2004-11-22 11:30:00.000 2004-11-22 12:00:00.000 3
2004-11-22 12:00:00.000 2004-11-22 12:30:00.000 3
2004-11-22 12:30:00.000 2004-11-22 13:00:00.000 2
2004-11-22 13:00:00.000 2004-11-22 13:30:00.000 2
2004-11-22 13:30:00.000 2004-11-22 14:00:00.000 2
2004-11-22 14:00:00.000 2004-11-22 14:30:00.000 3
2004-11-22 14:30:00.000 2004-11-22 15:00:00.000 3
2004-11-22 15:00:00.000 2004-11-22 15:30:00.000 3
2004-11-22 15:30:00.000 2004-11-22 16:00:00.000 3
2004-11-22 16:00:00.000 2004-11-22 16:30:00.000 4
2004-11-22 16:30:00.000 2004-11-22 17:00:00.000 3
2004-11-22 17:00:00.000 2004-11-22 17:30:00.000 3
2004-11-22 17:30:00.000 2004-11-22 18:00:00.000 0

--
JAG

patch / update which packages?

hi
i updated my sql server today to sp3
there are more packages to download from microsoft site
the analysis package and the
msde desktop engine
do i need those too to have a full patched and updated sql server ready?
thank you for your hints
mike schwarz
If you are running analysis services then you need the analysis package.
If you are utilizing msde then you need the msde package. Otherwise you are
ok.
"Mike Schwarz" <ctek@.ctek.ch> wrote in message
news:ejyoWTBHEHA.2768@.tk2msftngp13.phx.gbl...
> hi
> i updated my sql server today to sp3
> there are more packages to download from microsoft site
> the analysis package and the
> msde desktop engine
> do i need those too to have a full patched and updated sql server ready?
> thank you for your hints
> mike schwarz
>
>

patch / update which packages?

hi
i updated my sql server today to sp3
there are more packages to download from microsoft site
the analysis package and the
msde desktop engine
do i need those too to have a full patched and updated sql server ready?
thank you for your hints
mike schwarzIf you are running analysis services then you need the analysis package.
If you are utilizing msde then you need the msde package. Otherwise you are
ok.
"Mike Schwarz" <ctek@.ctek.ch> wrote in message
news:ejyoWTBHEHA.2768@.tk2msftngp13.phx.gbl...
> hi
> i updated my sql server today to sp3
> there are more packages to download from microsoft site
> the analysis package and the
> msde desktop engine
> do i need those too to have a full patched and updated sql server ready?
> thank you for your hints
> mike schwarz
>
>

passwrd

Hi
i am new in mssql7.Using sp_password i put login password
for the server.How can i put a password for a particular
database in the server.
Thank you.
Passwords & Logins are specific to Servers. Databases simply have users. So,
there is no such thing as passwords for database users.
Anith

Password Protected Excel File

Hi

I'm in need of a bit of assitance here. Basically I am currently creating a SSIS package which works in principle with the exception of my data flow.

On my data flow I am reading an excel file using the excel source, this works fine for a number of my examples but one of the excel files is password protected. This is throwing an error when I try to run the package.

Does anyone know how to read a password protected excel file?

Thanks
Kismet123You cannot access a password-protected Excel file using the Jet OLE DB Provider, period.

I probably shouldn't mention this, but as an interesting tidbit of useless knowledge, the Provider can access the file if it is open at the same time in the Excel application...and without even providing the password. But doing so causes a huge memory leak in the Excel process that's going to bring things down sooner or later.
Q319998 BUG: Memory Leak When You Query Open Excel Worksheet with ADO
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319998

-Doug
|||Thanks for that information Doug. Could you possibly tell me what the Password property is referring to when you create an Excel Connection Manager. I thought it would have something to do with if the connection you are making is password protected.

Kismet

Passing SELECTed rows to a calling SP

Hi!
I'm a new T-SQL developer and just hit a roadblock.
I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.
I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.
Any help appreciated.
Cheers,
N.I.T.I.N.For the results of spInner to be available to spOuter, there are a few
options. I have used temporary tables or persisted the data into a real
table to avoid recompiles and also have a record of the data. If you use the
latter option, you can use a guid column and each row with a guid obtained
from spOuter. This way you can easily pick up the rows you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||NiTiN (emailme.nitin@.gmail.com) writes:
> I'm a new T-SQL developer and just hit a roadblock.
> I have a scenario that goes like this: I have 2 stored procedures,
> spInner and spOuter. spInner has a SELECT statement which would
> normally be used by a class using MS Enterprise Library and that output
> goes into a DataSet. However, I need to get the output of the SELECT
> statement to go into spOuter and that's what I can't seem to figure
> out.
> I know I may be asked to use functions that return tables in replies to
> this post, but I can't do that as some parts of my application have an
> EXEC(string) for dynamic SQL, instead of spInner.
I have an article on my web site that discusses possible options. See
http://www.sommarskog.se/share_data.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Passing SELECTed rows to a calling SP

Hi!

I'm a new T-SQL developer and just hit a roadblock.

I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.

I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.

Any help appreciated.

Cheers,
N.I.T.I.N.For the results of spInner to be available to spOuter, there are a few
options. I have used temporary tables or persisted the data into a real
table to avoid recompiles and also have a record of the data. If you use the
latter option, you can use a guid column and each row with a guid obtained
from spOuter. This way you can easily pick up the rows you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||NiTiN (emailme.nitin@.gmail.com) writes:

Quote:

Originally Posted by

I'm a new T-SQL developer and just hit a roadblock.
>
I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.
>
I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.

I have an article on my web site that discusses possible options. See
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Passing results from select into exec

Hi
I'm trying to call a stored proc from within another of my stored
procs, passing the results of a select statement as the parameters -
but it's not working. Here's what I'm trying to do:
CREATE PROCEDURE [MoveRecordsToArchive]
AS
EXEC [ARCHIVEInsert]
(
SELECT
*
FROM
[CURRENT_DATA]
)
where [ARCHIVEInsert] is a stored proc which takes parameters which are
the same as the columns in [CURRENT_DATA].
But I'm getting the following error when trying to run
MoveRecordsToArchive:
"Procedure 'MoveRecordsToArchive' expects parameter '@.ID', which was
not supplied.", where @.ID is the first parameter to [ARCHIVEInsert] and
[ID] is the first column in [CURRENT_DATA].
Am I trying to do something impossible again? If so, how else could I
go about this?You will have to do it like this
declare @.ID' int @.name varchar(50)
select @.id =id,@.name =name
from [CURRENT_DATA] where id = Somevalue
exec ARCHIVEInsert @.id ,@.name
keep in mind you can only use 1 row at a time
You can do this if you need to move more rows
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA]
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Unfortunately, there may well be many rows to move, and I can't just
use your second method (i.e. a straight INSERT) since I need to insert
if the row doesn't exist in the archive table, and update if it does,
which is the process that the ARCHIVEInsert sp follows.
Is there then an easy way to iterate through all the rows in the table
so that I can use your first method?|||The insert
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA] c
left join DATA_ARCHIVE d on c.id =d.id
where d.id is null
The update
update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
FROM [CURRENT_DATA] c
join DATA_ARCHIVE d on c.id =d.id
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Yeah, that's not quite what I need. As I say, I have the
insert-or-update functionality working, in my ARCHIVEInsert storedproc.
I guess I could duplicate in the MoveRowsToArchive proc, but it seems
a bit silly when I have a perfectly good stored proc to call that
already does what I need.
Thanks anyway.
SQL wrote:
> The insert
> INSERT INTO DATA_ARCHIVE
> SELECT * FROM [CURRENT_DATA] c
> left join DATA_ARCHIVE d on c.id =d.id
> where d.id is null
> The update
> update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
> FROM [CURRENT_DATA] c
> join DATA_ARCHIVE d on c.id =d.id
> ----
--
> "I sense many useless updates in you... Useless updates lead to
> fragmentation... Fragmentation leads to downtime...Downtime leads to
> suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
> and DBCC DBREINDEX are the force...May the force be with you" --
> http://sqlservercode.blogspot.com/|||"Cathryn Johns" <cjohns@.gmail.com> wrote in message
news:1130510060.880509.220720@.f14g2000cwb.googlegroups.com...
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
> Thanks anyway.
> SQL wrote:
>
Well, if you want to do it the hard way..
Create a CURSOR in the first stored procedure and pull one row at a time and
call your insert/update spoc in a loop.
Something like:
declare @.ID' int @.name varchar(50)
DECLARE Cur CURSOR FOR
select id, name
from [CURRENT_DATA] where id = Somevalue
OPEN Cur
FETCH NEXT FROM Cur
INTO @.ID, @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec ARCHIVEInsert @.id ,@.name
FETCH NEXT FROM Cur
INTO @.ID, @.name
END
CLOSE Cur
DEALLOCATE Cur|||Cathryn Johns (cjohns@.gmail.com) writes:
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
That's not really right. You have a stored procedure which has the
logic to do this for one single row. Now you need something that performs
the same thing for multiple rows.
You can of coruse iterate over the source table, and move one row at a
time. But, frankly, for simple logic like this, that would be about
criminal. OK, that choice of words may stun you, but if I tell you
that to move 10000 rows, it would take 50 seconds with calling the
stored procedure for each row, and five seconds with the solution
that Denis posted, you may agree. Of course, I just made those numbers
up, but the difference is really that drastic - or even worse, if
proper indexing is not in place.
RDBMS are designed to work with sets of data, and you should try to
this as much as possible.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Cathryn Johns (cjohns@.gmail.com) writes:
> That's not really right. You have a stored procedure which has the
> logic to do this for one single row. Now you need something that performs
> the same thing for multiple rows.
> You can of coruse iterate over the source table, and move one row at a
> time. But, frankly, for simple logic like this, that would be about
> criminal. OK, that choice of words may stun you, but if I tell you
> that to move 10000 rows, it would take 50 seconds with calling the
> stored procedure for each row, and five seconds with the solution
> that Denis posted, you may agree. Of course, I just made those numbers
> up, but the difference is really that drastic - or even worse, if
> proper indexing is not in place.
> RDBMS are designed to work with sets of data, and you should try to
> this as much as possible.
Okay, I see what you're saying, but please bear with me here because I
know I don't have the right sql mindset :-) - I'm more used to regular
functional programming.
I understand the update & insert as posted by SQL, but what I don't get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one. Currently my
table has a uniqueness constraint consisting of several columns, and
what my stored proc does is try to insert (since this will happen
successfully 99% of the time, I try the insert first), then checks
@.@.error and if the code indicates a uniqueness constraint violation, it
updates instead. Maybe this isn't the best way to implement what I'm
trying to accomplish, but either way I still don't see how to do this
using a set-based approach. The cursor approach makes more sense to
me, but I can see that it could be really slow.|||Cathryn,
"I understand the update & insert as posted by SQL, but what I don't
get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one"
when you do a join only rows that exist in both tables are returned so
you can do an update
when you do a left join with where d.id is null only the rows that
don't exist in the other table are returned so you can do an update
When you work with SQL you have to think in terms of sets, basically
you have to unlearn what you were taught for VB, Java C# etc etc
A very good book on T-SQL is
The Guru's Guide to Transact-SQL by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
And of course Books On Line
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Cathryn Johns (cjohns@.gmail.com) writes:
> Okay, I see what you're saying, but please bear with me here because I
> know I don't have the right sql mindset :-) - I'm more used to regular
> functional programming.
Then you have a bit to unlearn. :-)

> I understand the update & insert as posted by SQL, but what I don't get
> is how to determine which rows need to be inserted and which need to be
> updated *without* going through each row, one by one.
UPDATE target
SET col = s.col
FROM target t
JOIN sources s ON t.keycol = s.keycol
INSERT target (...)
SELECT ...
FROM source s
WHERE NOT EXISTS (SELECT *
FROM target t
WHERE t.keycol = s.keycol)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp