Solved

Looping to create a table schedule

Posted on 2011-03-15
16
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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
 

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
get_systemdrive info from tsql? 1 26
SQL trigger 5 51
SQL profiler 3 51
Estimating my database size 7 51
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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