gaps in integer ranges

hello, i have quite a challenge on my hands here and would appreciate any help. :confused:

I have a table variable that stores integer ranges representing times of the day:

select * from @.reservations

room date | starttime | endtime
1 2004-12-11 0 1440 (represents an entire day in minutes)
2 2004-12-12 420 1020
3 2004-12-14 200 600
4 2004-12-15 0 200
4 2004-12-15 500 1000

I need to be able to return the minutes that are open for each room. The @.reservations table shows me the times that are blocked.

I'd like to analyze each row and return an integer range representing gaps in the day, where 0-1440 represents an entire day.

Based on the @.reservations table above, I'd like to write something that returns:

room date starttime endtime
2 2004-12-12 0 420
2 2004-12-12 1020 1440
3 2004-12-14 0 200
3 2004-12-14 600 1440
4 2004-12-15 200 500
4 2004-12-15 1000 1440

This result represents the times in minutes that are available.

I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @.reservations table.

I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time! :)in the results, do you also want

1 2004-12-12 0 1440
1 2004-12-13 0 1440
1 2004-12-14 0 1440
1 2004-12-15 0 1440
2 2004-12-13 0 1440
2 2004-12-14 0 1440
2 2004-12-15 0 1440
3 2004-12-11 0 1440
3 2004-12-12 0 1440
3 2004-12-13 0 1440
3 2004-12-15 0 1440
4 2004-12-11 0 1440
4 2004-12-12 0 1440
4 2004-12-13 0 1440
4 2004-12-14 0 1440

or do you just want the available ranges for dates where some part of the date is already booked?|||I want the available ranges for the dates where some part of the date is already booked

and I just thought that I will also need to know the available times if there is nothing booked in the room...yikes...

so if I have no row in my @.reservations table for room 1 on 2004-12-21 then I would want to return

1 2004-12-21 0 1440

Hmmm...would something like this work for that scenario??

I have a stored procedure that going to give me these results. I am writing a search. In the stored procedure I pass in the start date of the search and the end date of the search. So i could potentially, check that if there are no rows in the @.reservations table for that date and room, i would need to return 0-1440

thank you!|||The best way I have found to handle this functionality it to create a table in your database that consists of nothing but incrementing value from zero to ...well, whatever the highest number you end up needing.

By supplying a starting date to a SELECT query based upon this table, you can use the values to create a list of time intervals of whatever duration you choose. From this dataset, left join your Reservations table and return only the records where no corresponding Reservation exists. Voila! Your resultset shows all the unbooked times.

Functions with global variables

Hello,

I am porting a stored procedure from Oracle. It uses a variable that
remembers its previous values from each invocation. (It uses a PRAGMA
REFERENCES clause for those who are familiar with Oracle.) In other
words, the variable in a particular stored procedure acts as a global
variable. So the each invocation of the stored procedure can see its
last value, instead of its initial default value.

Is there something similar in SQLServer?There are no global variables in SQL and local variables in a stored
procedure go out of scope when the SP returns. Maybe you can put the values
you want to persist into a table?

I can think of two likely reasons for wanting to do what you have described:
an auto-incrementing ID or a user-defined aggregate function. A
auto-incrementing ID is easy: use an IDENTITY column. User-defined aggregate
functions aren't possible in SQL2000 but there are solutions for some of the
non-standard aggregates that are commonly requested (Median, Product and
String Concatenation for example).

--
David Portas
SQL Server MVP
--

function to return table variable

my question has to do with the performance of user-defined function that
returns a table variable vs. traditional temp tables. i have a function
that consists of a select statement that populates and returns a table
variable. the select is fairly complex and takes a siginificant amount of
time to run mainly due to the size of the tables being queried. the
resultset, is not that large (usually around 1000 rows in 10 columns). the
procedure that uses the data in the table variable makes reference to this
table variable in more than one location using something like:
select * from dbo.fTblVar(param1,param2,param3)
does this mean that each time the calling procedure makes reference to the
function, the complex (and long-running) select statement will be executed?
if this is the case, it seems that i would be better off using a traditional
temp table that is created and populated once, then acted on as needed. or
does this so somehow get cached sql server's memory for the duration of the
procedure? what if the parameter values change?Well if you call the UDF every time, then yes, it willrun every time... But
if the contents are not different then just run the UDF Once, outside your
query, and dump the valeusinto a local table variable, and use that table
variable in your query, then it won;t be running everytime
Declare @.T Table (<column Defintions> )
Insert @.T
select * from dbo.fTblVar(param1,param2,param3)
-- Now the @.T variable can be used throughout the rest of your SP Exactly
like a temp table would be used...
Which is better depends on how much data is in it, and what you need to do
with it. You can't put additional indexes (Other than Primary Key COnstrain
t
Index) on table variables, so if you need to really manipulate the data in
the table, a temp table is more flexible, but if all you need is a temporar
y
list of keys, say, for joining in another query, then table variables are
100% in memory, and should be much faster. If you use them, however, keep
them narrow.
"JT" wrote:

> my question has to do with the performance of user-defined function that
> returns a table variable vs. traditional temp tables. i have a function
> that consists of a select statement that populates and returns a table
> variable. the select is fairly complex and takes a siginificant amount of
> time to run mainly due to the size of the tables being queried. the
> resultset, is not that large (usually around 1000 rows in 10 columns). th
e
> procedure that uses the data in the table variable makes reference to this
> table variable in more than one location using something like:
> select * from dbo.fTblVar(param1,param2,param3)
> does this mean that each time the calling procedure makes reference to the
> function, the complex (and long-running) select statement will be executed
?
> if this is the case, it seems that i would be better off using a tradition
al
> temp table that is created and populated once, then acted on as needed. o
r
> does this so somehow get cached sql server's memory for the duration of th
e
> procedure? what if the parameter values change?
>
>

Function for return element list from a query

Hello,

I have do a sql function for return a list of element from a query send in variable.

When I test the function on self I have no problem.

But when I use the function in a sql query I have problem.

example :

Code Snippet

SELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL,
dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
AS V_PROCESS_ITEMS
FROM APPLI_SUPPLIER INNER JOIN
APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))

This is the error :

Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24
Only functions and extended stored procedures can be executed from within a function.

When I do an exec of the function I have this problem :

Code Snippet

exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))'

This is the function

Code Snippet

CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM]
(@.QUERY AS VARCHAR(3900)=null)
RETURNS varchar(8000)
AS
BEGIN
-- Insert statements for procedure here
declare @.v_List_ITEM as varchar(8000)
set @.v_List_ITEM=''

if @.QUERY is not null
Begin
declare @.cur_Lect_ITEM CURSOR;
declare @.FUNCTION AS NVARCHAR(4000);
Declare @.ITEM as VARCHAR(255);

SET @.FUNCTION = 'set @.mainCursor=cursor for ' + @.QUERY + ' for read only open @.mainCursor'

EXEC sp_executesql @.FUNCTION,N'@.mainCursor cursor output', @.cur_Lect_ITEM output

fetch next from @.cur_Lect_ITEM into @.ITEM
while @.@.fetch_status=0
begin
set @.v_List_ITEM=@.ITEM + ' ; ' + @.v_List_ITEM
fetch next from @.cur_Lect_ITEM into @.ITEM
end
deallocate @.cur_Lect_ITEM
SET @.v_List_ITEM=REPLACE(REPLACE(@.v_List_ITEM, CHAR(13), ''), CHAR(10), '')
set @.v_List_ITEM=left(@.v_List_ITEM,len(@.v_List_ITEM)-3)
End
RETURN @.v_List_ITEM
END

Can you help me please?

Thank you

You DO NOT execute a FUNCTION.

You use a FUNCTION inline (like an expression), or

you use a FUNCTION like a table.

Perhaps your 'function' should be a Stored Procedure...

|||

1. You can't use dynamic SQL in Functions

2. You needn't this much complex query to concatinate the items

If you use sql server 2005 the following query will work for you..

Code Snippet

SELECT

APPLI_SUPPLIER.N_SUPPLIER_ID,

APPLI_SUPPLIER.V_SUPPLIER_LABEL,

(SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) + ';' as [text()]

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6)

AND APPLI_SUPPLIER_MAIN.N_SUPPLIER_ID = APPLI_CONSTRUCTION.N_SUPPLIER_ID )

for XML path('')) AS V_PROCESS_ITEMS

FROM

APPLI_SUPPLIER APPLI_SUPPLIER_MAIN

INNER JOIN APPLI_SUPPLIER_SKILL APPLI_SUPPLIER_SKILL_MAIN

ON APPLI_SUPPLIER_MAIN.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL_MAIN.N_SUPPLIER_ID

WHERE

(APPLI_SUPPLIER_SKILL_MAIN.N_SKILL_ID IN (2, 3, 4, 5, 6))

|||

If you use sql server 2000,

Code Snippet

CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.RESULT VARCHAR(8000);

SET @.RESULT = '';

SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE

FROM

(

SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6)

AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID

)

) AS DATA;

RETURN @.RESULT;

END

GO

SELECT

APPLI_SUPPLIER.N_SUPPLIER_ID,

APPLI_SUPPLIER.V_SUPPLIER_LABEL,

DBO.GET_PROCESS_ITEMS(APPLI_CONSTRUCTION.N_SUPPLIER_ID) V_PROCESS_ITEMS

FROM

APPLI_SUPPLIER

INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE

(APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))

|||

Hello Manivannan.D.Sekaran

thank you for your answer.

I use SQL SERVER 2000 SP4

The objective of the function it's to use any query for return a list of element

It's not possible to do a generic function?

|||

In SQL Server 2000, we can't able to achieve this. You have to create a separate function. Since you have to embedded this function in your query you have to use the UDF (for each requirement).

In SQL Server 2005, you can achieve this using .NET CLR integration..

|||

thank you for answer ............. I have not chance|||

Hello,
I would like send a list of ID in the function.
How I can do that?
Do you have a idea?
thank you

Code Snippet

CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT, @.SKILL_ID AS VARCHAR(1000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.RESULT VARCHAR(8000);

SET @.RESULT = '';

SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE

FROM

(

SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN @.SKILL_ID

AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID

)

) AS DATA;

RETURN @.RESULT;

END

GO

|||

You can use the SPLIT UDF,

Code Snippet

CREATE FUNCTION SPLITINTOROWS

(

@.LIST AS VARCHAR(8000),

@.DELIMITER AS VARCHAR(10)

)

RETURNS @.LISTOFIDS TABLE (ITEM VARCHAR(8000))

AS

BEGIN

WHILE CHARINDEX(@.DELIMITER, @.LIST) <> 0

BEGIN

INSERT INTO @.LISTOFIDS

VALUES(SUBSTRING(@.LIST,1,CHARINDEX(@.DELIMITER,@.LIST)-1))

SET @.LIST = SUBSTRING(@.LIST, CHARINDEX(@.DELIMITER,@.LIST)+1, LEN(@.LIST))

END

INSERT INTO @.LISTOFIDS VALUES(@.LIST)

RETURN;

END

Code Snippet

CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT, @.SKILL_ID AS VARCHAR(1000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.RESULT VARCHAR(8000);

SET @.RESULT = '';

SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE

FROM

(

SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (select item from SplitIntoRows(@.SKILL_ID, ','))

AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID

)

) AS DATA;

RETURN @.RESULT;

END

|||

Thank you very much

You are my god