Date Ranges

[justatad]

Does anyone out there have advice on a date range issue that I am having?

I am pulling data from an oracle database...

-I have a start date field
-I have an end date field
-I have a field that provides one character per day per field which represents the day that a recurence will occur.(e.g. Monday field = M Tuesday field = T)

I want to create a formula in Crystal that will create multiple records based on dates that fall into the ranges which are somehow compared to whether the monday, tuesday, etc fields are populated.

I need one record per date that is based upon whether the U,M,T,W,R,F,S exists and I need to find a way to match that up to the date ranges.

Any advice provided would be extremely appreciated!

 

[justatad]

still working on this one.
can someone let me know if it looks like I am going in the right direction?

if Date({db.START_DATE}) = Date({db.END_DATE})
then {db.START_DATE} else
if Date({db.START_DATE}) <> Date({db.END_DATE})
then {db.START_DATE}+1 else
if Date({db.START_DATE})+1 <> Date({db.END_DATE})
then {db.START_DATE}+2 else
if Date({db.START_DATE})+2 <> Date({db.END_DATE})
then {db.START_DATE}+3 else


also...if does anyone know of a way to print out all the days in between (and therefore seperate records) if:
Date({db.START_DATE}) <> Date({db.END_DATE})

Thanks to whoever wishes to respond!

 

[kenhamady]

I am not sure that I understand your question, but CR can't create multiple records out of one record.

Can you give an example of the actual entries in two sample records, and then explain how you want to use these records in a report? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[justatad]

Thanks for responding!

here is a sample of some of the data that I am working with...

Event_Title
Meeting
Meeting2
Meeting3

date_begin date_end sun mon tues wed thur fri sat
01/01/01 01/31/01 s m f
05/21/01 12/25/01 m w f
06/01/01 06/01/01 s

Ken,

This is an example of the data that I am working with. I don't know if the fields are going to line up with the data in this post but I think you can see what I mean. I essentially need to do two things:

1. replicate records that meet the following criteria date_begin <> date_end

2. create a formula that will compare the weekday fields (Sun, Mon, Tues, etc..) with the dates that are duplicated and only keep the ones that that match. (e.g. for meeting1 I would only like to keep duplicated records that fall on dates between 01/01/01 to 01/31/01 on sundays, mondays and fridays and exclude all others {exclude tuesdays, wednesdays, thursdays, and saturdays})

I am not trying to create new records...just duplicate ones that I already have that fall between two dates.

Thanks a lot!

 

[kenhamady]

IF you have one record, and you want to make 10 records out of it, CR can't do that without helpf from a table.

Are you trying to display all of the dates for ONE meeting at a time, or are you trying to create a calendar with all meetings on it? The first would be possible within CR, although it would require you setting up in advance a separate conditional section for the the maximum number of weeks that a meeting can run.

I can't think of an easy way to do the second.

Forcing table inflation by using a new table of all future dates, linked by the start date, and using a &quot;Greater than &quot; join might work. You could put in criter that said that the limits the number of days returned to the End Date. Then you could only select days that matched the DayOfWeek values in the fields. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[justatad]

actually displaying all dates for one event at a time is essentially what I am trying to do. However, what I thought about (I am not sure if this is a good idea) is duplicating the event title data if it meets the criteria. Otherwise supress the title & date field altogether.

Here is what I would like the report to look like:

Date Event
01/01/01 Meeting
01/03/01 Meeting
01/05/05 Meeting

this would continue until 01/31/01 (which is the date that it ends) and then it would go on to Meeting2...

What I thought about doing on this would be to insert a date field (customizing the format so that it would read as the week day <monday, tuesday, etc..>) then compare this field to the weekday fields (sun mon tues wed thur fri sat), if the weekdays don't match then do not supress the date data and event.

what would be the formula on this?
>>Forcing table inflation by using a new table of all >>future dates, linked by the start date, and using >>a &quot;Greater than &quot; join might work. You could put in >>criter that said that the limits the number of days >>returned to the End Date. Then you could only select >>days that matched the DayOfWeek values in the fields.

Would this be supressing objects?

Thank you very much!

 

[kenhamady]

If you can create a table and use it I like the inflation technique. Forcing table inflation would actually generate new records, so you would use your select expert to only select the appropriate ones.


The problem I have with the first technique is that you gave an example that runs for 7 months. This means having a hard coded section available for each of about 200 days, not very doable. If you could live with one line per week, it might be more manageable. Then you might need only 30 sections to do a 7 month period.
Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[justatad]

Thanks a lot for the advice!

One final question:
You mention that I should create a table and inflate it. Could this be done in access? Do you have any code on this (sql or visual basic)? Or any general ideas on how I should approach inflating a table?

Thanks again!

 

[kenhamady]

Preferably, you would create the table within the database that stores your current tables. You could create it in a separate database, but your preformance will be poor, and you won't be able to use outer joins. This means you have to have access to the database to create and update this table. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[EnigmaX]

Ok this code is actually from a friend.... but this should help. Put this in a module in access.

Sub CalendarCreate()
Dim rst As Recordset, Counter As Integer, TotalRecords As Integer, NewStart As Date
Dim NewEnd As Date, NewData As String

Set rst = CurrentDb.OpenRecordset(&quot;Dates&quot

Counter = 0
TotalRecords = rst.RecordCount
rst.MoveFirst

Do Until Counter = TotalRecords

With rst

If !StartDate <> !EndDate Then
NewStart = !StartDate
NewEnd = !EndDate
NewData = !Data
.AddNew
!StartDate = NewStart + 1
!EndDate = NewEnd
!Data = NewData
.Update
TotalRecords = TotalRecords + 1
End If

.MoveNext

End With

Counter = Counter + 1

Loop

rst.Close

End Sub