Crystal 2008 Oracle 11 g Last Month First Full Week (Mon-Sun)/Second/Third/Fourth

[hmax]

Crystal 2008
Oracle 11g

Hi:

I need to capture the following ...

Last Month First Full Week (Mon-Sun) or part thereof
Last Month Second Full Week (Mon-Sun)
Last Month Third Full Week (Mon-Sun)
Last Month Fourth Full Week (Mon-Sun) or part thereof

Also,

This Month First Full Week (Mon-Sun) or part thereof
Second
Third
Fourth or part thereof
if they exist, through to the most recent Sun
So, for example, today, 10/10/12, we would have the first full week of October ending Sun Oct 7.
If last full week contains Sep and Oct, we want to capture just Oct dates.

Thank you for your help.

 

[pmax9999]

The starting point for this report is to calculate the first Monday of the current month and the previous month. There may be a simpler way to achieve this, but I would create the following formula that calculates these two dates and assigns them to Global Variables (FMCM = First Monday of Current Month; FMPM = First Monday Previous Month)

WhilePrintingRecords;
Local DateVar CD   := CurrentDate;

Local DateVar FDoCM := Date(Year(CD), Month(CD), 1);
Global DateVar FMCM :=
Select  DayOfWeek(FDoCM, crMonday)
Case    1   : FDoCM 
Case    2   : FDoCM + 6
Case    3   : FDoCM + 5
Case    4   : FDoCM + 4
Case    5   : FDoCM + 3
Case    6   : FDoCM + 2
Case    7   : FDoCM + 1;


Local DateVar FDoPM := DateSerial(Year(CD), Month(CD)-1, 1);
Global DateVar FMPM :=
Select  DayOfWeek(FDoPM, crMonday)
Case    1   : FDoPM 
Case    2   : FDoPM + 6
Case    3   : FDoPM + 5
Case    4   : FDoPM + 4
Case    5   : FDoPM + 3
Case    6   : FDoPM + 2
Case    7   : FDoPM + 1;

Put this formula in the Report Header, and the all of the other date ranges can be calculated from these two variables.

Hope this helps.

Cheers
Pete.

 

[hmax]

Thanks, pmax9999.

So, I've created my new subreport with these global variables declared in the header.

Now, let's say I want to retrieve all IDs with dates that exist within the first week of last month? A formula with, e.g,

If {table.createddatefield} >= FDoPM and
{table.createddatefield} <= FDoPM +6
then {table.IDfield} = 1 else 0;

Is that it?

And then another set of IDs with dates that fall within the second week, and so on...

 

[pmax9999]

How you proceed will depend on the format required of the resultant report. Assuming you need a separate sub report for each week, I would put the original formula in the main report RH. Then create a series of separate formulas as follows:


//@Week 1 - Previous Month - Start
WhilePrintingRecords;
Global DateVar FMPM

//@Week 1 - Previous Month - End
WhilePrintingRecords;
Global DateVar FMPM + 6

//@Week 2 - Previous Month - Start
WhilePrintingRecords;
Global DateVar FMPM + 7

//@Week 2 - Previous Month - End
WhilePrintingRecords;
Global DateVar FMPM + 13

... and so on for each week of both current and previous months.

Then set up each of the subreports as normal reports and pass the relevant formulas through to the subreports as parameters in the normal way.

Alternatively, you might set up the report to return all records from the beginning of the previous month, then use the a formula to allocate each record to one of the weeks, then use that formula to group the records (but it would need to be done without variables so as to be able to group by it).

If you can explain more about what the resultant report will look like, I will be better placed to be able to help further.


Pete.

 

[hmax]

Hi, pmax9999

This will be a subreport, separate from the main report where calculations are related but it's a different scenario.

So, I retrieve all data for the last month in the subreport.

I add your variable to the heading.

Now I need to create counts of IDs for those that fall within a range of dates for a week.

The request is for specific results displayed in a specific way and there is no desire to change thinking just now, there is a better approach, but we must get through this first, and then guide them towards the better place!!! I've got success so far with all other reqs. Just need to work out this logic.

So, for the first week, I try

If {@SiteType} = "XYZ" and
{Table.DateCreatedField} >= Global DateVar FMPM and
{Table.DateCreatedField} <= Global DateVar FMPM + 6
then 1 else 0;


They do exist, but the formula shows 0. Something is wrong. Help, please!!!


I have groups for

@SiteType
DateCreated
OrderID

Results will appear under OrderID group

Thank you!

 

[hmax]

Ignore - got it!!! Taking a deep breath. What was I thinking?

If {@SiteType} = "XYZ" and
{Table.DateCreatedField} in @Week1
then 1 else 0;

Good to go.

pmax9999, you're a life saver.

Thank you.

hmax

 

[hmax]

Hang on, will this work when we move to the next month for the previous month?

I meant

If {@SiteType} = "XYZ" and
{Table.DateCreatedField} in @Week1 to @Week2
then 1 else 0;

What about the straggler days before week 1 Mon-Sun, and after week 4 Mon-Sun?

E.g., Sep 2012 we have Sat 1 to Sun 2. Yes, we can calculate based on the above during this month, October, but what happens when we reach November?

I need something that will capture these dates consistently for the previous month as we move forward.

Cheers,
Helen

 

[pmax9999]

Hi Helen

You should be able to make this work. The number of weeks in a month could be 4 or 5 so it would be necessary to establish this.

One simple test as to whether the month has a 5th week would be to test the start date and end date for week 5 and if they are in the same month then week 5 would not ne needed (they must logically be wholly in the next month).

I don't have access to Crystal at the moment so can't do any testing of my theory or develop the approach more fully, but happy to do some more work on it on Monday when I get home.

Hope this helps
Pete.

 

[hmax]

Yes, thanks for the suggestion, Pete.

 

[pmax9999]

OK, here's how I tackled it (there may of course be other simpler ways).

First I created formulas to determine the first Monday day of the Previous Month and first Monday day of the Current Month:

//{@PM: 1st Mon}
Select  DayOfWeek(DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1), crMonday)
Case    1   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) 
Case    2   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 6
Case    3   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 5
Case    4   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 4
Case    5   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 3
Case    6   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 2
Case    7   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 1

//{@CM: 1st Mon}
Select  DayOfWeek(Date(Year(CurrentDate), Month(CurrentDate), 1), crMonday)
Case    1   : Date(Year(CurrentDate), Month(CurrentDate), 1) 
Case    2   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 6
Case    3   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 5
Case    4   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 4
Case    5   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 3
Case    6   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 2
Case    7   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 1

Using these two formula's I created a formula to return the subsequent Mondays, calculated by adding 7 days to the previous date:

//{@PM: 2nd Mon}
{@PM: 1st Mon} + 7

//{@PM: 3rd Mon}
{@PM: 2nd Mon} + 7

... and so on until I have a formula returning the 2nd, 3rd and 4th Monday of the Previous and Current months.

As some months may have 5 Mondays, the formula is amended to test if it is still the same month, and returns a null if the 5th Monday is in the next month.

//{@PM: 5th Mon}
If	Month({@PM: 4th Mon} + 7) = Month({@PM: 4th Mon})
Then    {@PM: 4th Mon} + 7
Else    Date(0,0,0)

//{@CM: 5th Mon}
If	Month({@CM: 4th Mon} + 7) = Month({@CM: 4th Mon})
Then    {@CM: 4th Mon} + 7
Else    Date(0,0,0)

These formulas can then be used to determine relevant weeks of each month, because the final day of each week is simply the Monday date + 6 days. They can be used to pass to as parameters to sub reports or within the main report (for grouping etc).

While variables could arguably be more efficient, I have avoided them in this approach because the print time evaluation time can be a little restrictive depending on how/where they need to be used.


Hope this helps

Cheers
Pete.

 

[pmax9999]

On re-reading the post, it is possible I misunderstood, and that the first week will consist of those dates between the 1st day of the month up to the the 1st Sunday, in which case the approach can be varied slightly to calculate the 1st Sunday of each month:

Select  DayOfWeek(DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1), crMonday)
Case    1   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 6
Case    2   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 5
Case    3   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 4
Case    4   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 3
Case    5   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 2
Case    6   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1) + 1
Case    7   : DateSerial(Year(CurrentDate), Month(CurrentDate)-1, 1)

Select  DayOfWeek(Date(Year(CurrentDate), Month(CurrentDate), 1), crMonday)
Case    1   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 6 
Case    2   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 5
Case    3   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 4
Case    4   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 3
Case    5   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 2
Case    6   : Date(Year(CurrentDate), Month(CurrentDate), 1) + 1
Case    7   : Date(Year(CurrentDate), Month(CurrentDate), 1)

, and:
week 1 would be 1st day of the month to the 1st Sunday of the month;
week 2 would be 1st Sunday of the month + 1 to 1st Sunday of the month + 7, ... etc

Hope I am not just confusing everything unnecessarily.

Cheers
Pete.

 

[hmax]

Wow, awesome, Pete. Thank you so much!