Sort data with ~ separation

[llunde]

I need help to separate the data my database returned to me that is separated by ~'s.

For example the hours per day of load for production would be 1~6~6~3.5 for a start date of September 21 thru September 24th.

The next part of this is to convert the days. The days per each day of load are formatted as 3916~3917~3918~3919.

I am a Crystal novice so any help will be appreciated!

 

[lbass]

Can't tell what you are converting here. Is it the hours (1,6,6,3.5) or the dates, and what are 3916,3917, etc.?

-LB

 

[llunde]

I need to convert the dates to normal date formats, but I also need to separate the hours and dates from each other so I can total the hours for each date for each job.

for example

Job number hours days start date
7437 1~6~6~3.5 3916~3917~3918~3919 9/21/09
7521 5~4 3917~3918 9/22/09
7620 4 3918 9/23/09

9/21 the total hours would be 1
9/22 the total hours would be 11
9/23 the total hours would be 14
9/24 the total hours would be 3.5

Thanks
LL

 

[CoSpringsGuy]

I notice that your sample report starts today which leads me to believe this is a forecast report. Is that just a coincidence?

Is there a maximum number of day for a job number. The first example has 4. could there be 10? 100? or is it limited in some way?

the baseline date to convert your days to dates is 1/1/999 but i ask the other questions to help come up with a way to accumulate those hours for you.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[llunde]

This is a report for the shop load for production. There could be load before today and further into the future. There is no maximum number of days for a job the most I have seen is 9 days, but it could be more.

Thanks for all your help
LL

 

[CoSpringsGuy]

hmmmm I duplicated your table in an excel spreadsheet so I could test on it a little.. could you give me more examples of raw data and a better description of what you are looking at for out put?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[llunde]

Okay,
As far as more data I don't know if this is what you had in mind but I will try

Group
Cutting
Job Load Dates Start date
7525 3.5~5~5~3.5 3916~3917~3918~3919 9/21/09
7437 4~5~5~4.5 3916~3917~3918~3919 9/21/09
7521 5~5~5 3917~3918~3919 9/22/09
7620 5~4 3918~3919 9/23/09

Group
Welding
Job Load Dates Start date
7525 5~5~0~0~3.5 3919~3920~3921~3922~3923 9/24/09
7437 4~0~0~4.5 3920~3921~3922~3923 9/25/09
7521 5~0~0~5 3920~3921~3922~3923 9/25/09
7620 5 3920 9/25/09

The output I am looking for is

Group Cutting
Date 9/21 9/22 9/23 9/24
Load 7.5 15 20 17

Group Welding
Date 9/24 9/25 9/26 9/27 9/28
Load 5 19 0 0 13

I hope this helps.
Thanks for all of you help!
LL

 

[CoSpringsGuy]

is there a field in the database which holds the value for cutting or welding?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[llunde]

Yes there is a field in the database for the number and description for cutting, welding, etc. (we have more areas than these).

 

[CoSpringsGuy]

unless you have a reference for the earliest date for a group this is a hard nut for me to crack. If noone else comes up with a good idea, I do have something we could try if you are willing to be a little creative and patient with my crazy thoughts.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[llunde]

The dates will be changing whenever this is run.
Thanks for all your help I will see if anyone else resonds.

 

[CoSpringsGuy]

ok ... I have a solution that works if you want to give it a shot

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[llunde]

I will give it a try.

 

[CoSpringsGuy]

could you to your report and from the menu at the top select database and show SQL query? Copy (CTRL-C) and paste in here what is in the box

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[llunde]

I am not quite sure what you are asking, but our software uses BAQ (business activity queries) and then it's downloaded to Crystal. I'm sorry am new to all of this.

 

[CoSpringsGuy]

Well Im not familiar with BAQ.... not sure I know how reports interface with the data using BAQ

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection