Generating multiple time records from one record

[DropsIT]

I have a table that contains a single record for recurring time entries. For example the time entry will have one date (say 1st May), and then have a field in the record that says that it recurs 10 times at weekly intervals, i.e. 10 recurences * weekly / daily indicaor etc . The application multiplies this out on screen to show the entry for 10 instances say every Wednesday each week for 10 weeks. (1st May, 8th May, 15th May, 24th May etc etc )
However I need to report this by generating an entry for each of those recurrences in a seperate table as a seperate record in the table.
Anyone got any ideas how I should write a SQL select statement or query to achieve this ?

 

[r937]

use an integers table

this table has one column, called i

there are as many rows as you need numbers

let's say you fill this table with the numbers from 0 to 9

now do this:

select i
, dateadd("d",i*7,#2005-05-04#) as wednesdays
from integers;

result:

i wednesdays
0 2005-05-04
1 2005-05-11
2 2005-05-18
3 2005-05-25
4 2005-06-01
5 2005-06-08
6 2005-06-15
7 2005-06-22
8 2005-06-29
9 2005-07-06

neat, eh?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[DropsIT]

This is good, the problem that this gives me though is that I have to create the integer table first with correct number of integers. Can it generate the correct number of lines for itself and what will happen if the source table has more than one record ?

ie
record 1 : Staff A startdate 20/05/2005 3 occurences 3 days apart
record 2 : Staff B startdate 20/05/2005 4 occurences 2 days apart

I need to get the result

Staff A 20/05/2005 Count 1
Staff A 23/05/2005 2
Staff A 27/05/2005 3
Staff B 20/05/2005 1
Staff B 22/05/2005 2
Staff B 24/05/2005 3
Staff B 26/05/2005 4

Can this be done in the same way ?

 

[r937]

Can this be done in the same way ?

yes, and see how nicely, too

select R.staffname
     , dateadd( "d"
              , i * R.daysapart
              , R.startdate ) as [Start Date]
     , i + 1 as [Count]
  from staff_records as R
inner
  join integers
    on integers.i 
        between 0 and R.occurrences - 1

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[DropsIT]

I have this so far but still have a problem

SELECT R.Staffname
, DateAdd("d"
,i*R.daysapart
,R.startdate) AS [Start Date]
, i+1 AS [Count]
FROM staff_records AS R
INNER
JOIN INTEGERS
ON R.occurences = INTEGERS.i
between 0 and R.occurrences - 1;

As a test I have set up an R table with two staff records with fields staffname, daysapart ,startdate & occurences as above. On the INTEGERS table I have fields i and dates.

I am joining on R.occurences and integers.i

However I get the error "Between operator without And in query expression 'R.occurences = INTEGERS.i,

Also should the INTEGERS table still contain values in i as or does it generate these as it runs ?

 

[PHV]

And what about this ?
SELECT R.Staffname, DateAdd("d",i*R.daysapart,R.startdate) AS [Start Date], i+1 AS [Count]
FROM staff_records AS R, INTEGERS
WHERE INTEGERS.i Between 0 And R.occurrences - 1;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

yeah, silly me, writing queries with JOIN syntax when i should remember that Access prefers WHERE clauses...



FYI DropsIT, the integers table should not have a date column, just the i column

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)