Problem with query

8 Nov 2008 1:07 PM

Ricardo Luceac

Hi.. I'm not new to sql but I'm not a guru, and now I'm facing a problem
and hope you guys can help me...

I'm making an application to a hospital that will manage the time-table
of the medicals. The registry is like that:

table agendas:

id      Prof     date       number_of_consultations    interval
Start_Time
1       3453    2008-12-15     5                         00:10     08:00

so in the example the doctor will have 5 pacients in 2008-12-15 starting
at 08:00 a.m. and each consultation will have 10 minutes length.

What I want is to query the database and receive the data in the
following form:

medical agenda:

Prof           date           Time
3453        2008-12-15        08:00
3453        2008-12-15        08:10
3453        2008-12-15        08:20
3453        2008-12-15        08:30
3453        2008-12-15        08:40

is there some sql statements that will do that for me??

Thanks in advance.

*** Sent via Developersdex http://www.developersdex.com ***

8 Nov 2008 1:32 PM

Plamen Ratchev

Seems you posted to multiple groups, replied in 'programming',
copy/paste here:

Here is one method utilizing a table with numbers. Instead of using the
system table spt_values to simulate table with numbers, you can create a
permanent one (http://www.projectdmx.com/tsql/tblnumbers.aspx).

CREATE TABLE Agendas (
id INT PRIMARY KEY,
prof INT,
date DATETIME,
number_of_consultations INT,
interval CHAR(5),
start_time CHAR(5));

INSERT INTO Agendas VALUES(1, 3453, '20081215', 5, '00:10', '08:00');

SELECT id, prof, date, CONVERT(CHAR(5),
                                DATEADD(MINUTE, (n - 1) *
                                        DATEDIFF(MINUTE, 0,
                                                 CONVERT(DATETIME,
interval + ':00', 108)),
                                CONVERT(DATETIME, start_time + ':00', 108)),
                                108) AS time
FROM Agendas AS A
JOIN (SELECT number
       FROM master..spt_values
       WHERE type = 'P'
         AND number BETWEEN 1 AND 250) AS Nums(n)
   ON n BETWEEN 1 AND number_of_consultations;

--
Plamen Ratchev
http://www.SQLStudio.com