system
June 19, 2004, 11:35am
1
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)
system
June 19, 2004, 11:52am
2
A sketch:
Group your Transaction Dates
If Goal is a measure use the MAX function
Andreas (BOB member since 2002-06-20)
system
June 20, 2004, 3:14am
3
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)
system
June 20, 2004, 1:58pm
4
Now I understand. Essentially you want to break on Goal and group the days dynamically based on the changes in Goal?
Andreas (BOB member since 2002-06-20)
system
June 20, 2004, 3:34pm
5
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 (BOB member since 2002-06-25)
system
June 21, 2004, 8:25am
6
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 (BOB member since 2002-11-08)
system
June 21, 2004, 11:32am
7
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)
system
June 22, 2004, 4:35am
8
How come 01-06 is 100 and not 150???
Sridharan (BOB member since 2002-11-08)
system
June 22, 2004, 4:40am
9
Must just be a simple mistake.
Steve Krandel (BOB member since 2002-06-25)
system
June 22, 2004, 5:02am
10
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 (BOB member since 2002-11-08)
system
June 22, 2004, 7:01am
11
Yes it is a mistake (typo) it should be 150
ashrafkh (BOB member since 2002-11-13)