Count records by months 2

[johnstrang]

Hi,

Using BO5.1.7 on an Oracle 8.1.7 database.

I have a problem where I need to count the number of jobs worked on per month.
There are fields "Actual Start Date" and "Actual Completion Date".

Finding the month of the Start Date and Completion Date is straightforward, but the difficulty arises where the job goes over the end of a month
eg Actual Start Date is 30th August
Actual Completion Date is 2nd September.

The user wants such joby to be counted in BOTH months.

As a relative newcomed to BO, could someone please give some advice on how to achieve this?

Many thanks in advance,
John

 

[BrianTyler]

Create a variable as a measure:

=if formatdate(d1,"yyyymm")=formatdate(d1,"yyyymm") then 0 else 1

You can then accumulate this measure.
If a job can appear in three months, the same approach will work but the sttement is a little more complex

 

[blom0344]

You could look into the use of Theta-joins at the universe-level. This, in combination with a calenderfile will allow you to fetch the months august and september for an actual startdate in august and an enddate in september.

Even better still, this will work too with periods spanning many months.

SELECT A,B,PERIOD 
FROM
 TABLE, PERIOD_CALENDER
WHERE PERIOD_CALENDER.PERIOD BETWEEN
TABLE.TO_NUMBER(TO_CHAR(STARTDATE,'MM')) AND TABLE.TO_NUMBER(TO_CHAR(ENDDATE,'MM'))

If start and enddate have different yearparts this will be more complicated , but doable...

Ties Blom
Information analyst 
tbl@shimano-eu.com

 

[johnstrang]

Thanks for the information - I'll let you know how I get on with it.
The users want reports only for the current year, so hopefully that will not be too complicated to implement.

If I get stuck I'll be back

 

[johnstrang]

Hi again,

I have come up with the following

MONTH_NUMBERS.MONTH between 
to_number(to_char(PW_START,'MM')) 
and NVL(to_number(to_char(PW_STOP,'MM')), 
        to_number(to_char(sysdate,'MM')))
OR 
(MONTH_NUMBERS.MONTH  < 
to_number(to_char(PW_START,'MM'))
AND to_number(to_char(PW_START,'YYYY'))
 < to_number(to_char(PW_STOP,'YYYY')) 
AND MONTH_NUMBERS.MONTH 
<= to_number(to_char(PW_STOP,'MM')))

It seems to work OK, and takes into account the situation where a job is started in the previous year and completed during the current year. It also includes jobs which are not yet completed.

Again, many thanks for the suggestions.

John

 

[blom0344]

Congrats, beautiful solution using complex joins....

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[johnstrang]

Thanks Ties - nice to get some positive feedback. It's just a pity that I had to create a MONTH_NUMBERS table. All it contains is the numbers 1 to 12 - I'd hoped it may be possible to avoid creating such a table.
But it works, and it's not taking up much disk space!

John

 

[blom0344]

John,

Calenderfiles in whatever shape do a wonderful job in making life a lot easier!

Ties Blom
Information analyst
tbl@shimano-eu.com