Comparing Values

I have a table which has the following data.

Employee, Transaction Date, Daily Goal
1, 01-Jan-2004 , 280
1, 02-Jan-2004 , 280
1, 03-Jan-2004 , 280
1, 04-Jan-2004 , 280
1, 05-Jan-2004 , 120
1, 06-Jan-2004 , 120
1, 07-Jan-2004 , 280
1, 08-Jan-2004 , 280
1, 09-Jan-2004 , 100
1, 10-Jan-2004 , 100

Desired Output

Employee, Period , Daily Goal
1 , 01 - 04 , 280
1 , 05 - 06 , 120
1 , 07 - 08 , 280
1 , 09 - 10 , 100

Take care,
ashrafkh@hotmail.com


ashrafkh (BOB member since 2002-11-13)

A sketch:

  • Group your Transaction Dates
  • If Goal is a measure use the MAX function

Andreas :de: (BOB member since 2002-06-20)

can you please eloborate more. the daily goals value keep changing every month for each day for every employee.


ashrafkh (BOB member since 2002-11-13)

Now I understand. Essentially you want to break on Goal and group the days dynamically based on the changes in Goal?


Andreas :de: (BOB member since 2002-06-20)

I don’t think you can do this. I have it almost working, but it fails because a Goal can be repeated for a different range of days. If you have something else on each row that makes it unique I can do this. I’ll keep trying and post back.


Steve Krandel :us: (BOB member since 2002-06-25)

ashrafkh,

How do you do the grouping of Dates??? Is it always same or its dynamic… You have Goal Value 280 for Jan 1 to Jan 4 and also Jan7 -8… Do you want to change the Grouping based on change in GOAL value… Provide us with more inputs if possible…


Sridharan :india: (BOB member since 2002-11-08)

It is dynamic and not fixed for eg.

Emp TRANS_DA DAILY_GOAL


1 01.01.04 280
1 02.01.04 280
1 03.01.04 280
1 04.01.04 280
1 05.01.04 120
1 06.01.04 120
1 07.01.04 280
1 08.01.04 280
1 09.01.04 100
1 10.01.04 100

Result needed
PERIOD DAILY_GOAL


01 - 04 280
05 - 06 120
07 - 08 280
09 - 10 100

The Next month values could be.

Emp TRANS_DA DAILY_GOAL


1 01.02.04 150
1 02.02.04 150
1 03.02.04 150
1 04.02.04 150
1 05.02.04 150
1 06.02.04 150
1 07.02.04 280
1 08.02.04 280
1 09.02.04 280
1 10.02.04 280

Result needed
PERIOD DAILY_GOAL


01 - 06 100
07 - 10 280

So the next month could have different values for the daily goal. we cannot group just based on the daily goal.

hope I have explained it well.


ashrafkh (BOB member since 2002-11-13)

How come 01-06 is 100 and not 150???


Sridharan :india: (BOB member since 2002-11-08)

Must just be a simple mistake.


Steve Krandel :us: (BOB member since 2002-06-25)

Thanks Steve… Really didn’t strike me… So as told earlier by Steve you need to look at something outside the reporter module… Lemme check during my free time…


Sridharan :india: (BOB member since 2002-11-08)

Yes it is a mistake (typo) it should be 150


ashrafkh (BOB member since 2002-11-13)