Solved

Looping to create a table schedule

Posted on 2011-03-15
16
253 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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