Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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      

0
LougaLo
Asked:
LougaLo
  • 8
  • 6
  • 2
1 Solution
 
deadlyDevCommented:
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
 
ramkihardyCommented:
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
 
LougaLoAuthor Commented:
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LougaLoAuthor Commented:
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
 
ramkihardyCommented:
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
 
deadlyDevCommented:
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
 
LougaLoAuthor Commented:
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
 
LougaLoAuthor Commented:
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
 
LougaLoAuthor Commented:
HI deadlyDev:
 my question how can i create a temporary table and use it later.

i used to create table views.
0
 
LougaLoAuthor Commented:
do you know hoe to create it in a VIEW
0
 
deadlyDevCommented:
In that case you will need to create a permanent table with the visit count, and join onto that in the view.
0
 
deadlyDevCommented:
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
 
LougaLoAuthor Commented:

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
 
deadlyDevCommented:
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
 
LougaLoAuthor Commented:
deadlyDev:
thank you so much. it's working
0
 
deadlyDevCommented:
Glad to hear that! Could you accept one of my answers so I get the points :) Thanks!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now