Solved

Looping to create a table schedule

Posted on 2011-03-15
16
247 Views
Last Modified: 2012-05-11
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      

0
Comment
Question by:LougaLo
  • 8
  • 6
  • 2
16 Comments
 
LVL 7

Expert Comment

by:deadlyDev
ID: 35137238
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

0
 
LVL 2

Expert Comment

by:ramkihardy
ID: 35137341
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
0
 

Author Comment

by:LougaLo
ID: 35153770
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.
0
 

Author Comment

by:LougaLo
ID: 35153815
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'.
0
 
LVL 2

Expert Comment

by:ramkihardy
ID: 35154286
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 $$

0
 
LVL 7

Expert Comment

by:deadlyDev
ID: 35157322
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....
0
 

Author Comment

by:LougaLo
ID: 35196627
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
0
 

Author Comment

by:LougaLo
ID: 35204546
HI deadlyDev:
Do you know how i can use the table created through this query and link it with another table in my database.?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

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

i used to create table views.
0
 

Author Comment

by:LougaLo
ID: 35204679
do you know hoe to create it in a VIEW
0
 
LVL 7

Expert Comment

by:deadlyDev
ID: 35205508
In that case you will need to create a permanent table with the visit count, and join onto that in the view.
0
 
LVL 7

Expert Comment

by:deadlyDev
ID: 35205512
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
0
 

Author Comment

by:LougaLo
ID: 35205582

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
0
 
LVL 7

Accepted Solution

by:
deadlyDev earned 500 total points
ID: 35208404
The syntax above creates a temporary (per query) table...

To create the permanent table do the following (Note: DO NOT DO THIS MORE THAN ONCE):

CREATE TABLE VisitCount (cnt int)

nsert 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)

After this you can do the select:

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

and it will work permanently :)
0
 

Author Comment

by:LougaLo
ID: 35212327
deadlyDev:
thank you so much. it's working
0
 
LVL 7

Expert Comment

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now