Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Ranges 2

Status
Not open for further replies.
Jan 20, 2003
41
US
I am writing a report to export billing data for Passport services. I need to set a contract year based on the date of service. I am trying to write please excuse the psuedo code:

IF service provided is between 7/1/02 - 6/30/03 then contract year = -7. I am going to need to do several years
can this be done using a case statement? What is the best way to code the date range?.....Please help.
 
Dear ComputerNurse,

What does the -7 represent? Please provide a little more info and I will be happy to help.

ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
 
The file I am creating is a ascii file and each field is delimited by the character "-", -7 is the contract year. It is incremented by one on July 1 of each year. For services after 6/30/03 the contract year will be -8
 
Dear ComputerNurse,

So would there be any service periods that exist for dates prior to 07/01/02? and if so they would decrement -1 for each year? (Obviously you would hit 0 in 1995)...

Please advise. Also, what version of Crystal and what db backend (SQL Server, Oracle, etc....)

ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
 
It's probably easiest to use ifs:

@start Date
If {table.servdate} <= cdate(2003,6,30) then
datediff(&quot;yyyy&quot;,-7,{table.servdate})
else
datediff(&quot;yyyy&quot;,-8,{table.servdate})

Hope this is somewhat close, otherwise post example data and expected output, text descriptions leave too much to the imagination, and I'm operating on 2 hours sleep today.

<imagining Ro's post turning into a large double mocha>

-k
 
Dear ComputerNurse,

Here is my suggestion:

I would use a Switch statement, I took you up to 2010, just add more conditions if you need more years checked. Make sure that the last entry does not end in a comma, replace {Tracking Log.Date} with your date field.

//begin formula
Switch
(
{Tracking Log.Date} in Date(2002,07,01) to Date(2003,06,01), '-7',
{Tracking Log.Date} in Date(2003,07,01) to Date(2004,06,01), '-8',
{Tracking Log.Date} in Date(2004,07,01) to Date(2005,06,01), '-9',
{Tracking Log.Date} in Date(2005,07,01) to Date(2006,06,01), '-10',
{Tracking Log.Date} in Date(2006,07,01) to Date(2007,06,01), '-11',
{Tracking Log.Date} in Date(2007,07,01) to Date(2008,06,01), '-12',
{Tracking Log.Date} in Date(2008,07,01) to Date(2009,06,01), '-13',
{Tracking Log.Date} in Date(2009,07,01) to Date(2010,06,01), '-14'
)
//ebd formula

Let me know how that works out for you and hope that is what you were looking for,

ro

SV: (Congrats on being named TipMaster of the week!) Tried to email you but the old address is no longer valid. ro

Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
 
ComputerNurse,

Sorry, I just noticed an error in my formula... the end dates are supposed to be June 30th, below is corrected.

//begin formula
Switch
(
{Tracking Log.Date} in Date(2002,07,01) to Date(2003,06,30), '-7',
{Tracking Log.Date} in Date(2003,07,01) to Date(2004,06,30), '-8',
{Tracking Log.Date} in Date(2004,07,01) to Date(2005,06,30), '-9',
{Tracking Log.Date} in Date(2005,07,01) to Date(2006,06,30), '-10',
{Tracking Log.Date} in Date(2006,07,01) to Date(2007,06,30), '-11',
{Tracking Log.Date} in Date(2007,07,01) to Date(2008,06,30), '-12',
{Tracking Log.Date} in Date(2008,07,01) to Date(2009,06,30), '-13',
{Tracking Log.Date} in Date(2009,07,01) to Date(2010,06,30), '-14'
)
//end formula

Sorry, ro... Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
 
Dear ComputerNurse,

Apparently, I am in deep need of a double espresso.

While there is nothing inherently wrong with the formula, you should always provide &quot;error handling&quot; so that if none of the values are matched, you get a heads up. Add this to the last line&quot;

True, 'Date Range not Handled'


Here is the entire formula:

//begin formula
Switch
(
{Tracking Log.Date} in Date(2002,07,01) to Date(2003,06,01), '-7',
{Tracking Log.Date} in Date(2003,07,01) to Date(2004,06,01), '-8',
{Tracking Log.Date} in Date(2004,07,01) to Date(2005,06,01), '-9',
{Tracking Log.Date} in Date(2005,07,01) to Date(2006,06,01), '-10',
{Tracking Log.Date} in Date(2006,07,01) to Date(2007,06,01), '-11',
{Tracking Log.Date} in Date(2007,07,01) to Date(2008,06,01), '-12',
{Tracking Log.Date} in Date(2008,07,01) to Date(2009,06,01), '-13',
{Tracking Log.Date} in Date(2009,07,01) to Date(2010,06,01), '-14',
true, 'Date Range not Handled'
)
//end of formula

Once again, sorry about that ---

ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top