asked on

Looping to create a table schedule

i would like some need creating a query to generate in a View mode a table scheduling each visit based on the DOB.
each participant will have 8 visits.
total participants = 250

here the table of DOB
tbDOB (already entered in the database)
PID      DOB
01      1/15/2011
02      1/16/2011
03      1/17/2011

here is the desired tbSchedule

PID      visitdate
01      2/15/2011      (DOB + 1 month)
01      3/15/2011      (DOB + 2 month)
01      4/15/2011      (DOB + 3 month)
01      5/15/2011      (DOB + 4 month)
01      6/15/2011      (DOB + 5 month)
01      7/15/2011      (DOB + 6 month)
01      8/15/2011      (DOB + 7 month)
01      9/15/2011      (DOB + 8 month)
02      2/16/2011
02      3/16/2011
02      4/16/2011
02      5/16/2011
02      6/16/2011
02      7/16/2011
02      8/16/2011
02      9/16/2011
03      2/17/2011
03      3/17/2011
03      4/17/2011
03      5/17/2011
03      6/17/2011
03      7/17/2011
03      8/17/2011

deadlyDev

Here is some SQL that will do what you are looking for

set nocount on

declare @patient table
(
	PID INT,
	DOB DATETIME
)

INSERT INTO @patient (PID, DOB) VALUES (1, '15/1/2011');
INSERT INTO @patient (PID, DOB) VALUES (2, '16/1/2011');
INSERT INTO @patient (PID, DOB) VALUES (3, '17/1/2011');

declare @visitCount table ( cnt int )
insert into @visitCount (cnt) values (1)
insert into @visitCount (cnt) values (2)
insert into @visitCount (cnt) values (3)
insert into @visitCount (cnt) values (4)
insert into @visitCount (cnt) values (5)
insert into @visitCount (cnt) values (6)
insert into @visitCount (cnt) values (7)
insert into @visitCount (cnt) values (8)

select p.PID, DATEADD(month, v.cnt, p.DOB) as visitdate
from @patient p, @visitCount v
order by pid

Open in new window

ramkihardy

After adding the date of birth in the tbDOB...
youi have to to do the following

DECLARE counter INT DEFAULT 1;
      simple_loop: LOOP
      SET counter=counter+1;
      select p.PID, DATEADD(month,counter, p.DOB) as visitdate
from tbDOB p, order by p.PID
      IF counter=9 THEN
      LEAVE simple_loop;
      END IF;
      END LOOP simple_loop;

For each and every PID in tblDOB run this loop to get the desired tbSchedule...
Insert that each selected record into a view to show the generated results to the user...
Please let me know if u want any help..
Regards
Ramki

LougaLo

ASKER

deadlyDev:
@patient table already exist in my database. I do not need to create it again.
here is the error message recived:
Msg 242, Level 16, State 3, Line 9
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

LougaLo

ASKER

ramkihardy:

I replaced [tbDOB] with my table containning the DOB variable here is the error received. i ran the query

Msg 155, Level 15, State 2, Line 1
'INT' is not a recognized CURSOR option.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'order'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'THEN'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'LOOP'.

ramkihardy

Im using Mysql according to that
'INT' is not a recognized CURSOR option.---This indicates that you have to change the delimiter before executing multiple statements
DELIMITER $$
......Statements
$$
2.Incorrect syntax near the keyword 'order'.---remove the comma before the word order...
3.Incorrect syntax near the keyword 'THEN'.Put semicolon for all the sql statements....here i had left the semicolon after p.PID....

4.Incorrect syntax near 'LOOP'. I didnt know the actual scenario...i have used this looping inside the stored procedure....

For example look at this

drop procedure if exists ABC;
DELIMITER $$
-CREATE PROCEDURE ABC()
BEGIN
DECLARE a INT Default 0 ;
simple_loop: LOOP
SET a=a+1;
select a;
IF a=3 THEN
LEAVE simple_loop;
END IF;
END LOOP simple_loop;
END $$

deadlyDev

Hey LougaLo,

I realize that the table is in your database, I just created a temporary table for the test script :) You need to change the date values for the test script to values that are recognized by your system to avoid the error....

LougaLo

ASKER

HI deadlyDev:
I worked fine on the query but i cannot use it on a View Mode. Declare is not recognized on a View mode.
I am not familiar with stored procedure.
thanks

LougaLo

ASKER

HI deadlyDev:
Do you know how i can use the table created through this query and link it with another table in my database.?

LougaLo

ASKER

HI deadlyDev:
my question how can i create a temporary table and use it later.

i used to create table views.

LougaLo

ASKER

do you know hoe to create it in a VIEW

deadlyDev

In that case you will need to create a permanent table with the visit count, and join onto that in the view.

deadlyDev

Once you have created the visit count table, and inserted the 8 values, you would use a query something like the following

select p.PID, DATEADD(month, v.cnt, p.DOB) as visitdate
from tbDOB p, visitCount v
order by p.pid

LougaLo

ASKER

deadlyDev:

one of my issue is how to create that permanent table?

here is what i did

1. i already have the tbDOB in my database.
PID      dateofvisit
01      2011-02-15
02      2011-03-16
03      2011-04-17

2. i ran this query you sent in declare @visitCount table ( cnt int )
insert into @visitCount (cnt) values (1)
insert into @visitCount (cnt) values (2)
insert into @visitCount (cnt) values (3)
insert into @visitCount (cnt) values (4)
insert into @visitCount (cnt) values (5)
insert into @visitCount (cnt) values (6)
insert into @visitCount (cnt) values (7)
insert into @visitCount (cnt) values (8)

select p.Pid, DATEADD(month, v.cnt, p.dateofvisit) as visitdate
from dbo.tbDOB p, @visitCount v
order by pid

a table is listing (attached file) but how to make permanent?
tbVisit.cpp

ASKER CERTIFIED SOLUTION

deadlyDev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LougaLo

ASKER

deadlyDev:
thank you so much. it's working

deadlyDev

Glad to hear that! Could you accept one of my answers so I get the points :) Thanks!