loop between 2 datetime field values

led

unread,

Dec 20, 2009, 4:54:31 AM12/20/09

to

Hi how can i loop between 2 datetime value ex:

i have this stored procedure:

@onde CHAR(100) = NULL,
@quandoinicio CHAR(100) = NULL,
@quandofim CHAR(100) = NULL,
@quantominimo CHAR(100) = NULL,
@quantomaximo CHAR(100) = NULL,
@quartos CHAR(100) = NULL,
@pessoas CHAR(100) = NULL,
@tipo CHAR(100) = NULL,
@rcama CHAR(100) = NULL,
@rbanho CHAR(100) = NULL,
@limpeza CHAR(100) = NULL,
@maqloica CHAR(100) = NULL,
@maqroupa CHAR(100) = NULL,
@arcon CHAR(100) = NULL,
@satcabo CHAR(100) = NULL,
@internet CHAR(100) = NULL,
@barbecue CHAR(100) = NULL,
@formpesqavancadabut CHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT distinct
cod_casa,destino,data_activo,tipo,pessoas,localidade,img_1,img_2 ,img_3,
img_4,data_act, certif,valor_voto,n_voto

FROM
pesq_1_com_feedback
WHERE
((@onde IS NULL) OR (localidade = @onde))
AND ((@quandoinicio IS NULL) OR (inicio >= @quandoinicio))
AND ((@quandofim IS NULL) OR (fim <= @quandofim))
AND ((@quantominimo IS NULL) OR (preco > @quantominimo ))
AND ((@quantomaximo IS NULL) OR (preco < @quantomaximo ))
AND ((@quartos IS NULL) OR (quartos = @quartos ))
AND ((@pessoas IS NULL) OR (pessoas = @pessoas ))
AND ((@tipo IS NULL) OR (tipo = @tipo ))
AND ((@rcama IS NULL) OR (roupascama = @rcama ))

AND ((@rbanho IS NULL) OR (roupasbanho = @rbanho ))
AND ((@limpeza IS NULL) OR (limpeza = @limpeza ))
AND ((@maqloica IS NULL) OR (mlavaloica = @maqloica ))
AND ((@maqroupa IS NULL) OR (mlavaroupa = @maqroupa ))
AND ((@arcon IS NULL) OR (ar_condicionado = @arcon))
AND ((@satcabo IS NULL) OR (satcabo = @satcabo))
AND ((@internet IS NULL) OR (internet = @internet))
AND ((@barbecue IS NULL) OR (barbecue = @barbecue))

i have availibility calendar that have rows with
startdate and end date:

select startdate,enddate where cod_casa="record from the first select"
and livre=0

it is possible to loop between startdate to enddate?

as i do do while not rs2.eof
for k = cdate(rs2("startdate")) to cdate(rs2("enddate"))

i'm not experienced, but this loops in asp become slow and i think
treres a way to do it in sql server

Uri Dimant

unread,

Dec 20, 2009, 5:08:11 AM12/20/09

to

led
It is not clear what you are trying to achive, I mean, why loop? Can you
post sample data to be tested?

"led" <l.r...@sapo.pt> wrote in message
news:e5$evpVgK...@TK2MSFTNGP02.phx.gbl...

Erland Sommarskog

unread,

Dec 20, 2009, 6:31:27 AM12/20/09

to

led (l.r...@sapo.pt) writes:
> i have availibility calendar that have rows with
> startdate and end date:
>
> select startdate,enddate where cod_casa="record from the first select"
> and livre=0
>
> it is possible to loop between startdate to enddate?

Well, you don't really loop in SQL. But say that you have an orders
table, and you want show the numbers of orders as defined by a
calender table. That is, you only want to show data for the days
in that table. Then you would do:

SELECT C.OrderDate, COUNT(O.OrderDate)
FROM Calender C
LEFT JOIN Order O ON C.Date = O.OrderDate
GROUP BY C.OrderDate

I can't give an example for you procedure, because I don't really see
how your available calender fits into your query, but hopefully this
helps you to get going.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

--CELKO--

unread,

Dec 21, 2009, 7:59:05 PM12/21/09

to

SQL has no loops. SQL has no sequential data access. It is a
declarative language that works with completed sets of rows, not
records from sequential files.

If you don't know anything about RDBMS, then get a copy of the
simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

TheSQLGuru

unread,

Dec 21, 2009, 11:18:52 PM12/21/09

to

Actually you are incorrect. TSQL provides several ways to do loops and
sequential data access.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:50257da0-f13d-4bca...@v30g2000yqm.googlegroups.com...

Tony Rogerson

unread,

Dec 22, 2009, 3:20:06 AM12/22/09

to

--CLEKO--

You appear to be posting on the wrong forum.

SQL Server does have loops, it does have sequential access, it has xml, it
has unstructured search, it has mdx, it has dmx, it has .... well, a ton of
other stuff.

If you don't know anything about SQL Server (remember - this forum is for
users of SQL Server) then check out books online which can be easily found
on the internet.

--ROGGIE--

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:50257da0-f13d-4bca...@v30g2000yqm.googlegroups.com...