True Weekly Movement

[djeddie]

Hi All,

Using Crystal XI on SQL

Heres what i need.

I need to create a report that shows the true movement per week) of staff in different positions. i.e. Customer Service, Management etc

I have a table that contains employee details for each week as a snap shot, so, there can be emplyees that appear every week (existing staff), or some that start to appear every week from a particular week (new staff), and staff that dont appear anymore (resigned/terminated).

I have grouped as per below:

GH1 {@Position}
GH2 {HRStaff.datedump}
DET Nothing

What formula/method can be used to verify if a particular person existed in the previous group or not??

Instead of seeing this:

GH1 Customer Service Rep Total
GH2 05/04/2007 200
GH2 12/04/2007 200


I would like to see this:

GH1 Customer Service Rep Existing Resigned Joined
GH2 05/04/2007 150 50 50
GH2 12/04/2007 150 50 50

Cheers

Eddie S

 

[synapsevampire]

Why not post example dataand the exepcted output?

We don'tknow what is in {@Position}???

Do you hav a code for the action, such as resigned,joined?

-k

 

[djeddie]

synapsevampire,

I have told you what the expected output is.

You do know what is in {@Position}, the example says "Customer Service Rep".

The code for the action is my initial request. If there was a field that indicate the movement each week then i would have been able to accomplish this task on my own.

Cheers

Eddie S

 

[synapsevampire]

Yeah, I see the expected output, just not example data, instead you show what you don't want in a report.

Some rows of data would be useful...Usually there are IDs such as emp ID, perhaps a position ID (which is what you're probably using to derive a description). And no, we don'[tknow what is in @position, it's a formula, youshowed some output, not what it is or how...

As it stands a simple PREVIOUS probably won't cut it, so we need to know what the data looks like.

Not trying to make excess work for you, I'm trying to get you to post basic requirements.

-k

 

[lbass]

Do you have start and end dates for each person? If so, is the weekly date group independent of these? And is the date used for that group an actual date datatype?

-LB

 

[djeddie]

Here is an example of what the data looks like (date_dump is a weekly snapshot)

DATE_DUMP STAFF POSITION
20070405 John Senior
20070405 Harry Senior
20070412 John Senior
20070412 Harry Senior
20070412 Peter Senior
20070419 John Senior
20070419 Simon Senior
20070419 Peter Senior
20070426 John Senior
20070426 Simon Senior
20070426 Peter Senior

In this example i would like to see the following:

- the addition of 1 senior for the date_dump 20070412
- the addition of 1 senior and loss of 1 senior for 20070419
- no change for 20070426

 

[djeddie]

I have been thinking about how i have been trying to explain what i want and i've come up with another description.

Basically, is there anyway to verify if a particular record exists anywhere in the previous group or in the next group?

If i have one group being date_dump which is always a thursday, can i verify on each group the number of times and employee_id can be found in the previous week or the next week?