Working out montly averages

[mtgriffiths]

Hi all,

 

I am trying to work out the monthly attendance for a student. I have the dates the have logged into the system saved in a database as dd/mm/yyyy.

 

I am not sure how to work out how many days are in each month so that when the total amount of days a month a student has logged in can be worked out a calculation for the average can be found.

 

Any one have any ideas?

 

Thanks in advance

 

Matthew

[djpic]

What database system are you using?  MySQL?

[discomatt]

If you had used a datetime column instead of storing them as a string, you could have used

 

SELECT COUNT(*) as `count` FROM `table` WHERE `date` BETWEEN '2008-03-01' AND '2008-04-01'

[mtgriffiths]

Hi,

 

Yes i am using mysql.

 

In my database i have a feild called date and the data for example is 17/04/2008

 

I thout that i would have to work out how many days are in a month and divide by the dates logged for that month.

 

Any ideas?

 

[discomatt]

My advice is to convert over to a datetime type column... it's easy enough to convert over, jsut create a script to do it for you

 

Grab all data from sql, save to text file in case stuff breaks, convert row by row dd/mm/yyyy to yyyy-mm-dd, build a new query set, empty table, alter table (change col to datetime type), insert new data

 

You can now use my query example.

[djpic]

You can use: cal_days_in_month() do get the number of days in a particular month.

<?php
$num = cal_days_in_month(CAL_GREGORIAN, 8, 2003); // 31
echo "There was $num days in August 2003";
?>

 

I am assuming you already know how to do the SQL command; if you don't know how to write the SQL command, then reply.

 

See:

http://us.php.net/cal_days_in_month

 

[discomatt]

http://www.phpfreaks.com/forums/index.php/topic,192978.0.html

 

that thread will probably help with your conversion, if you decide to do it.

[laffin]

julian calander functions would prolly be more useful here.

 

but if ya dun have access to those functions (add on to php)

 

u can either build yer own julian calander functions

or play with strtotime

<?php
  header('Content-type: text/plain');
  
  function daysinmonth($date)
  {
  $d1=strtotime($date);
  echo date("Y-m-d",$d1)."\n";
  $d2=strtotime(date("Y-m-01",$d1)); // First day of Month
  echo date("Y-m-d",$d2)."\n";
  $d3=strtotime("+1 Month -1 Day",$d2);
  echo date("Y-m-d",$d3)."\n";
  return intval(date("d",$d3));
  }

  echo daysinmonth("2007-12-31")
  
?>

[djpic]

Don't think they are trying to find if the day is in the month, just trying to find the total days in the month.  Seems to be a pretty simple question.

 

To pull the data from the database, could just use wildcards for the dates.  For example:

 

SELECT * FROM `login` WHERE `date` LIKE '%%%%-01-%%';

 

To count the rows, just use the COUNT()

 

SELECT COUNT(*) FROM `login` WHERE `date` LIKE '%%%%-01-%%';

 

[laffin]

Its not a mysql question.

he needs the days in month for an average calculation.

 

AverageDaysInClass = DaysInClass / DaysInMonth.

 

 

[discomatt]

Its not a mysql question.

he needs the days in month for an average calculation.

 

AverageDaysInClass = DaysInClass / DaysInMonth.

 

 

 

My mistake

[djpic]

Hi all,

 

I am trying to work out the monthly attendance for a student. I have the dates the have logged into the system saved in a database as dd/mm/yyyy.

 

I am not sure how to work out how many days are in each month so that when the total amount of days a month a student has logged in can be worked out a calculation for the average can be found.

 

Any one have any ideas?

 

Thanks in advance

 

Matthew

 

It is a SQL question, has a database with the login times in it.  "not sure how to work out how many days are in each month" is the question, and that is solved by cal_days_in_month() function.

 

Then needs to count the number of rows with that date which is the SQL statement.

 

Take the SQL result and divided by the value of the cal_days_in_month().  Not that difficult, just need to know how to make the SQL statement and how to use the cal_days_in_month() function.

 

Also stated it was a MySQL database with the login times.