Argh - General Perl Logic! Don't know where to start..

[HughbertD]

Hi all,

I am having problems simply figuring out where to start with this problem.

Ultimately I want to create a spreadsheet which adds up all the hours spent at each company.

I have a MySQL database. It holds all the meetings details for each company visited including, the meeting start time and end time.

I want to get all the details of the meetings, find out the total amount of time spent in meetings, then find out the total amount of time spent for each company. The database holds information like so:

meetingID, start Date/Time, End Date/Time, Name of company visiting.

The start and end times are TimeStamps. The Name of the company being visited is from a different table, so multiple instances of the same company will be exactly the same. That said, I know I should use a foreign key, but if I can avoid it that would be fine.

My major problem is just understanding how to find out the time spent for each company. How do I total up the hours?

Any help would be really appreciated
Hugh

 

[KevinADC]

Not sure if you asking perl questions or SQL questions. But if you need to total up something in perl use the "+" math operator.

$sum += $foo;

where $foo is some type of number and $sum will be the total value of all $foo records you process in a loop.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[HughbertD]

I suppose the part I'm having trouble with, is how to seperate the Companies.

I want to a total for each company. Can I get perl to total things up based on per distinct company name?

 

[KevinADC]

Yes, but once again, I am unsure if you asking SQL or perl questions. To interface with your SQL database using the DBI module and the corresponding driver (DBD::Mysql in this case). I guess that is where you need to start.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[HughbertD]

I see what you mean. I think I am jumping the gun somewhat. As someone with experience, would you try the calculation at the MySQL level or Perl level?

 

[stevexff]

I'd probably go for the SUM() features of SQL if you want a programmatic solution. But if your end goal is a spreadsheet, it may be easier to just set up MySql as a datasource and do it all in the spreadsheet...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[KevinADC]

Ishnid posted the Optimus Prime variaton

------------------------------------------
- Kevin, perl coder unexceptional!

 

[KevinADC]

oops, wrong thread, never mind....

------------------------------------------
- Kevin, perl coder unexceptional!

 

[ishnid]

I was wondering about your second-last reply there Kevin. Had me confused for a minute.

I'd also try to do this at the SQL level - should be a quicker and more efficient way of doing things.