Jump to content
Salesforce and other SMB Solutions are coming soon. ×

How calculate the date ranges of a date using calculations & relationships?


marysilvaramos

Recommended Posts

Hi All:

 

I have the following table (Services Dates Ranges):

 

Date Start Date End Price Serv. Type

10/02/2003 03/31/2004 $110 Anc

04/01/2002 03/31/2004 $505.36 Bed

04/01/2002 10/01/2003 $50 Anc

04/01/2002 03/31/2003 $104 Anc

 

This table list the dates ranges & prices of the services done.

 

The user enters the date of the service and the type of the service (Anc or Bed) in other table called the billing.

 

The database should show the price of the service based on the date of it and the dates ranges from the first table (services dates). Later some calculations need to be done using that price.

 

How can it be done with calculations and relationships?

 

Thanks in advance

 

Mary

Link to comment
Share on other sites

Hi Mary ...

 

Your problem could definitely be handled by a scripted Find process, but a more elegant procedure is the use of smart date ranges. I've been studying this technique for the past few weeks. It's been a bear to get my brain around :eek: , but I'm finally beginning to understand how to implement it in my solutions.

 

I've posted a demo system here that roughly uses your file descriptions and data fields. You can download it and take a look at the calculations that produce the service code/date and range keys which are used to match entries in your Billing file with entries in the file were your prices reside.

 

Also, I highly recommend you read the article by Mikhail Edoshin that I've referenced in the post along with my demo. It's a pretty dense read, but once you begin to grasp the basic concepts, it sort of all falls into place in terms of understanding the principles and procedures involved.

 

If you have any questions, post them here. Peeps will gladly jump in to help you out. smile.gif

 

Good luck!

Link to comment
Share on other sites

Thanks very much, Jim.... I appreciate your time to answer my email. I wil contact you soon to tell how it goes...

 

Thanks again

 

Mary

Link to comment
Share on other sites

  • 3 weeks later...

Thanks very much!!! It worked perfectly!

 

What happen when I have dates that overlap?

 

For example:

 

5/12/2003 - 1/1/2004

9/12/2003 - 1/1/2004

 

Thanks

Link to comment
Share on other sites

Mary ...

 

No problem if you have overlapping dates for different service codes.

 

E.g.:

 

Code: Anc; Begin: 01/01/2003; End: 06/30/2003; Price: $50

Code: Anc; Begin: 07/01/2003; End: 09/30/2003; Price: $50

Code: Bed; Begin: 01/01/2003; End: 07/15/2003; Price: $75

Code: Bed; Begin: 07/16/2003; End: 10/15/2003; Price: $75

 

However, if you have overlapping dates for the same service codes, e.g.,

 

Code: Anc; Begin: 01/01/2003; End: 06/30/2003; Price: $50

Code: Anc; Begin: 06/01/2003; End: 09/30/2003; Price: $60

Code: Bed; Begin: 01/01/2003; End: 07/15/2003; Price: $75

Code: Bed; Begin: 07/01/2003; End: 10/15/2003; Price: $80

 

this presents a BIG problem. In this case, there would be no unique match in the Prices file for the Service Code/Service Date combination you enter in Billing. This will result in more than one Price being returned.

 

If you need to have overlapping dates for the same type of service, this will require an additional code to define the overlapping dates as unique in some way. E.g., You may discount certain of your rates for certain periods for regular customers, and you don't want to simply provide a universal percentage discount at all times of the year, i.e., you may want to set up a schedule of discounted rates for different periods. In this case, you could add, e.g., "R" to the existing Service Codes. So you might have something like the following, where "AncR" and "BedR" are the Service Codes for the discounted rate:

 

Code: Anc; Begin: 01/01/2003; End: 06/30/2003; Price: $50

Code: AncR; Begin: 06/01/2003; End: 09/30/2003; Price: $45

Code: Anc; Begin: 07/01/2003; End: 09/30/2003; Price: $60

Code: Bed; Begin: 01/01/2003; End: 07/15/2003; Price: $75

Code: BedR; Begin: 06/01/2003; End: 10/15/2003; Price: $65

Code: Bed; Begin: 07/16/2003; End: 10/15/2003; Price: $85

 

What you're doing here, really, is creating new Service Codes, and this will eliminate any overlapping dates problem.

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use