Novice looking for advice (date comparison).

After a little PHP education, my first project (to get my feet wet) is
to
make an employee time-tracking program for our small business.

*** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***

I'm in my planning stage, and here's my hurdle:
On the time report, we want to see EVERY date under each employee,
and
the string "Absent" for the dates on which the user has no punch
times. (It would also be nice to include day-names on the report.)
Obviously the Time table will only have entries for when people
actually clock in/out, so how do I get the other dates to show on the
report?
Any suggestions?

One guy here suggested just adding a table which includes every date
from here to ...., then just do an outer join.
This seems like it could work well, but I would like to get some input
from those with more experience.

Lastly, if this Calendar table is a good way to go, does anyone have
any
advice on how to go about creating one (preferrably with day and date
values), or a table like this wich they coud just provide a SQL
export of.

While a table dump would get me going quicker, I'd prefer to learn how
to do it myself.
I would then be able to create a form enabling me to periodically
(probably at the turn of our fiscal year) just enter the new ending
date (or date range) to further populate the Calendar table.

~Mo

(NOTE: I originally posted this in comp.lang.php, where it was
suggested that I repost in this group.)

On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
> After a little PHP education, my first project (to get my feet wet) is
> to
> make an employee time-tracking program for our small business.
>
> *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***
>
> I'm in my planning stage, and here's my hurdle:
> On the time report, we want to see EVERY date under each employee,
> and
> the string "Absent" for the dates on which the user has no punch
> times. (It would also be nice to include day-names on the report.)
> Obviously the Time table will only have entries for when people
> actually clock in/out, so how do I get the other dates to show on the
> report?
> Any suggestions?
>
> One guy here suggested just adding a table *which includes every date
> from here to ...., then just do an outer join.
> This seems like it could work well, but I would like to get some input
> from those with more experience.
>
> Lastly, if this Calendar table is a good way to go, does anyone have
> any
> advice on how to go about creating one (preferrably with day and date
> values), *or a table like this wich they coud just provide a SQL
> export of.
>
> While a table dump would get me going quicker, I'd prefer to learn how
> to do it myself.
> I would then be able to create a form enabling me to periodically
> (probably at the turn of our fiscal year) just enter the new ending
> date (or date range) to further populate the Calendar table.
>
> ~Mo
>
> (NOTE: I originally posted this in comp.lang.php, where it was
> suggested that I repost in this group.)

You could use an external scripting language like PHP to create the
calendar dates on-the-fly, and then compare this with data in your
table but, for a purely mysql solution, yes you'd probably need to
create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
little procedure for doing this very quickly. See his sight at
http://datacharmer.blogspot.com/2006...s-quickly.html.
Personally, I'd do as the other respondent suggested and just create a
calendar table along these lines. Note that you don't have to store
days, just dates. Days can be easily calculated using mysql's built-in
date functions.

On 8 May, 12:37, strawberry <zac.ca...@gmail.com> wrote:
> On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
>
>
>
> > After a little PHP education, my first project (to get my feet wet) is
> > to
> > make an employee time-tracking program for our small business.
>
> > *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***
>
> > I'm in my planning stage, and here's my hurdle:
> > On the time report, we want to see EVERY date under each employee,
> > and
> > the string "Absent" for the dates on which the user has no punch
> > times. (It would also be nice to include day-names on the report.)
> > Obviously the Time table will only have entries for when people
> > actually clock in/out, so how do I get the other dates to show on the
> > report?
> > Any suggestions?
>
> > One guy here suggested just adding a table which includes every date
> > from here to ...., then just do an outer join.
> > This seems like it could work well, but I would like to get some input
> > from those with more experience.
>
> > Lastly, if this Calendar table is a good way to go, does anyone have
> > any
> > advice on how to go about creating one (preferrably with day and date
> > values), or a table like this wich they coud just provide a SQL
> > export of.
>
> > While a table dump would get me going quicker, I'd prefer to learn how
> > to do it myself.
> > I would then be able to create a form enabling me to periodically
> > (probably at the turn of our fiscal year) just enter the new ending
> > date (or date range) to further populate the Calendar table.
>
> > ~Mo
>
> > (NOTE: I originally posted this in comp.lang.php, where it was
> > suggested that I repost in this group.)
>
> You could use an external scripting language like PHP to create the
> calendar dates on-the-fly, and then compare this with data in your
> table but, for a purely mysql solution, yes you'd probably need to
> create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
> little procedure for doing this very quickly. See his sight athttp://datacharmer.blogspot.com/2006/06/filling-test-tables-quickly.html.
> Personally, I'd do as the other respondent suggested and just create a
> calendar table along these lines. Note that you don't have to store
> days, just dates. Days can be easily calculated using mysql's built-in
> date functions.

"See his site" (not "sight")!

Actually I rather like one of the other approaches that was suggested.
Have a table containing the correct number of rows for the amount of
dates you want in you notional calendar table. You can then create the
dates on the fly by querying this table.

On 8 May, 14:04, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 8 May, 12:37, strawberry <zac.ca...@gmail.com> wrote:
>
>
>
>
>
> > On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
>
> > > After a little PHP education, my first project (to get my feet wet) is
> > > to
> > > make an employee time-tracking program for our small business.
>
> > > *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***
>
> > > I'm in my planning stage, and here's my hurdle:
> > > On the time report, we want to see EVERY date under each employee,
> > > and
> > > the string "Absent" for the dates on which the user has no punch
> > > times. (It would also be nice to include day-names on the report.)
> > > Obviously the Time table will only have entries for when people
> > > actually clock in/out, so how do I get the other dates to show on the
> > > report?
> > > Any suggestions?
>
> > > One guy here suggested just adding a table *which includes every date
> > > from here to ...., then just do an outer join.
> > > This seems like it could work well, but I would like to get some input
> > > from those with more experience.
>
> > > Lastly, if this Calendar table is a good way to go, does anyone have
> > > any
> > > advice on how to go about creating one (preferrably with day and date
> > > values), *or a table like this wich they coud just provide a SQL
> > > export of.
>
> > > While a table dump would get me going quicker, I'd prefer to learn how
> > > to do it myself.
> > > I would then be able to create a form enabling me to periodically
> > > (probably at the turn of our fiscal year) just enter the new ending
> > > date (or date range) to further populate the Calendar table.
>
> > > ~Mo
>
> > > (NOTE: I originally posted this in comp.lang.php, where it was
> > > suggested that I repost in this group.)
>
> > You could use an external scripting language like PHP to create the
> > calendar dates on-the-fly, and then compare this with data in your
> > table but, for a purely mysql solution, yes you'd probably need to
> > create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
> > little procedure for doing this very quickly. See his sight athttp://datacharmer.blogspot.com/2006/06/filling-test-tables-quickly.html.
> > Personally, I'd do as the other respondent suggested and just create a
> > calendar table along these lines. Note that you don't have to store
> > days, just dates. Days can be easily calculated using mysql's built-in
> > date functions.
>
> "See his site" (not "sight")!
>
> Actually I rather like one of the other approaches that was suggested.
> Have a table containing the correct number of rows for the amount of
> dates you want in you notional calendar table. You can then create the
> dates on the fly by querying this table.- Hide quoted text -
>
> - Show quoted text -

Touché

On 8 May, 14:04, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 8 May, 12:37, strawberry <zac.ca...@gmail.com> wrote:
>
>
>
>
>
> > On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
>
> > > After a little PHP education, my first project (to get my feet wet) is
> > > to
> > > make an employee time-tracking program for our small business.
>
> > > *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***
>
> > > I'm in my planning stage, and here's my hurdle:
> > > On the time report, we want to see EVERY date under each employee,
> > > and
> > > the string "Absent" for the dates on which the user has no punch
> > > times. (It would also be nice to include day-names on the report.)
> > > Obviously the Time table will only have entries for when people
> > > actually clock in/out, so how do I get the other dates to show on the
> > > report?
> > > Any suggestions?
>
> > > One guy here suggested just adding a table *which includes every date
> > > from here to ...., then just do an outer join.
> > > This seems like it could work well, but I would like to get some input
> > > from those with more experience.
>
> > > Lastly, if this Calendar table is a good way to go, does anyone have
> > > any
> > > advice on how to go about creating one (preferrably with day and date
> > > values), *or a table like this wich they coud just provide a SQL
> > > export of.
>
> > > While a table dump would get me going quicker, I'd prefer to learn how
> > > to do it myself.
> > > I would then be able to create a form enabling me to periodically
> > > (probably at the turn of our fiscal year) just enter the new ending
> > > date (or date range) to further populate the Calendar table.
>
> > > ~Mo
>
> > > (NOTE: I originally posted this in comp.lang.php, where it was
> > > suggested that I repost in this group.)
>
> > You could use an external scripting language like PHP to create the
> > calendar dates on-the-fly, and then compare this with data in your
> > table but, for a purely mysql solution, yes you'd probably need to
> > create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
> > little procedure for doing this very quickly. See his sight athttp://datacharmer.blogspot.com/2006/06/filling-test-tables-quickly.html.
> > Personally, I'd do as the other respondent suggested and just create a
> > calendar table along these lines. Note that you don't have to store
> > days, just dates. Days can be easily calculated using mysql's built-in
> > date functions.
>
> "See his site" (not "sight")!
>
> Actually I rather like one of the other approaches that was suggested.
> Have a table containing the correct number of rows for the amount of
> dates you want in you notional calendar table. You can then create the
> dates on the fly by querying this table.- Hide quoted text -
>
> - Show quoted text -

"your notional table"

touché

On May 8, 5:04*am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 8 May, 12:37, strawberry <zac.ca...@gmail.com> wrote:
>
>
>
>
>
> > On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
>
> > > After a little PHP education, my first project (to get my feet wet) is
> > > to
> > > make an employee time-tracking program for our small business.
>
> > > *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***
>
> > > I'm in my planning stage, and here's my hurdle:
> > > On the time report, we want to see EVERY date under each employee,
> > > and
> > > the string "Absent" for the dates on which the user has no punch
> > > times. (It would also be nice to include day-names on the report.)
> > > Obviously the Time table will only have entries for when people
> > > actually clock in/out, so how do I get the other dates to show on the
> > > report?
> > > Any suggestions?
>
> > > One guy here suggested just adding a table *which includes every date
> > > from here to ...., then just do an outer join.
> > > This seems like it could work well, but I would like to get some input
> > > from those with more experience.
>
> > > Lastly, if this Calendar table is a good way to go, does anyone have
> > > any
> > > advice on how to go about creating one (preferrably with day and date
> > > values), *or a table like this wich they coud just provide a SQL
> > > export of.
>
> > > While a table dump would get me going quicker, I'd prefer to learn how
> > > to do it myself.
> > > I would then be able to create a form enabling me to periodically
> > > (probably at the turn of our fiscal year) just enter the new ending
> > > date (or date range) to further populate the Calendar table.
>
> > > ~Mo
>
> > > (NOTE: I originally posted this in comp.lang.php, where it was
> > > suggested that I repost in this group.)
>
> > You could use an external scripting language like PHP to create the
> > calendar dates on-the-fly, and then compare this with data in your
> > table but, for a purely mysql solution, yes you'd probably need to
> > create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
> > little procedure for doing this very quickly. See his sight athttp://datacharmer.blogspot.com/2006/06/filling-test-tables-quickly.html.
> > Personally, I'd do as the other respondent suggested and just create a
> > calendar table along these lines. Note that you don't have to store
> > days, just dates. Days can be easily calculated using mysql's built-in
> > date functions.
>
> "See his site" (not "sight")!
>
> Actually I rather like one of the other approaches that was suggested.
> Have a table containing the correct number of rows for the amount of
> dates you want in you notional calendar table. You can then create the
> dates on the fly by querying this table.- Hide quoted text -
>
> - Show quoted text -

I'd really like to understand both options fully prior to making a
decision.
Can you further explain the suggestion of creating dates on the fly by
querying against a table with day numbers?

~Mo

On 8 May, 19:47, Mo <Mehile.Orl...@gmail.com> wrote:
> On May 8, 5:04 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 8 May, 12:37, strawberry <zac.ca...@gmail.com> wrote:
>
> > > On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
>
> > > > After a little PHP education, my first project (to get my feet wet) is
> > > > to
> > > > make an employee time-tracking program for our small business.
>
> > > > *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***
>
> > > > I'm in my planning stage, and here's my hurdle:
> > > > On the time report, we want to see EVERY date under each employee,
> > > > and
> > > > the string "Absent" for the dates on which the user has no punch
> > > > times. (It would also be nice to include day-names on the report.)
> > > > Obviously the Time table will only have entries for when people
> > > > actually clock in/out, so how do I get the other dates to show on the
> > > > report?
> > > > Any suggestions?
>
> > > > One guy here suggested just adding a table which includes every date
> > > > from here to ...., then just do an outer join.
> > > > This seems like it could work well, but I would like to get some input
> > > > from those with more experience.
>
> > > > Lastly, if this Calendar table is a good way to go, does anyone have
> > > > any
> > > > advice on how to go about creating one (preferrably with day and date
> > > > values), or a table like this wich they coud just provide a SQL
> > > > export of.
>
> > > > While a table dump would get me going quicker, I'd prefer to learn how
> > > > to do it myself.
> > > > I would then be able to create a form enabling me to periodically
> > > > (probably at the turn of our fiscal year) just enter the new ending
> > > > date (or date range) to further populate the Calendar table.
>
> > > > ~Mo
>
> > > > (NOTE: I originally posted this in comp.lang.php, where it was
> > > > suggested that I repost in this group.)
>
> > > You could use an external scripting language like PHP to create the
> > > calendar dates on-the-fly, and then compare this with data in your
> > > table but, for a purely mysql solution, yes you'd probably need to
> > > create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
> > > little procedure for doing this very quickly. See his sight athttp://datacharmer.blogspot.com/2006/06/filling-test-tables-quickly.html.
> > > Personally, I'd do as the other respondent suggested and just create a
> > > calendar table along these lines. Note that you don't have to store
> > > days, just dates. Days can be easily calculated using mysql's built-in
> > > date functions.
>
> > "See his site" (not "sight")!
>
> > Actually I rather like one of the other approaches that was suggested.
> > Have a table containing the correct number of rows for the amount of
> > dates you want in you notional calendar table. You can then create the
> > dates on the fly by querying this table.- Hide quoted text -
>
> > - Show quoted text -
>
> I'd really like to understand both options fully prior to making a
> decision.
> Can you further explain the suggestion of creating dates on the fly by
> querying against a table with day numbers?
>
> ~Mo

OK, here is an example. Suppose you have a table called dates, which
contains 366 rows. The rows do not need to contain anything, just an
empty field will do.

Now, suppose you want to produce a list of all dates from tomorrow for
one year:
SET @d =0;
SELECT
MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + ( @d := @d +1 ) ) calendar
FROM `dates` ;

Alternatively, if dates contained an integer column with the numbers 1
to 366 (let's call the column daynum):
SELECT
MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + daynum ) calendar
FROM `dates` ;

On May 9, 6:58*am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 8 May, 19:47, Mo <Mehile.Orl...@gmail.com> wrote:
>
>
>
>
>
> > On May 8, 5:04 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 8 May, 12:37, strawberry <zac.ca...@gmail.com> wrote:
>
> > > > On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
>
> > > > > After a little PHP education, my first project (to get my feet wet) is
> > > > > to
> > > > > make an employee time-tracking program for our small business.
>
> > > > > *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***
>
> > > > > I'm in my planning stage, and here's my hurdle:
> > > > > On the time report, we want to see EVERY date under each employee,
> > > > > and
> > > > > the string "Absent" for the dates on which the user has no punch
> > > > > times. (It would also be nice to include day-names on the report.)
> > > > > Obviously the Time table will only have entries for when people
> > > > > actually clock in/out, so how do I get the other dates to show on the
> > > > > report?
> > > > > Any suggestions?
>
> > > > > One guy here suggested just adding a table *which includes everydate
> > > > > from here to ...., then just do an outer join.
> > > > > This seems like it could work well, but I would like to get some input
> > > > > from those with more experience.
>
> > > > > Lastly, if this Calendar table is a good way to go, does anyone have
> > > > > any
> > > > > advice on how to go about creating one (preferrably with day and date
> > > > > values), *or a table like this wich they coud just provide a SQL
> > > > > export of.
>
> > > > > While a table dump would get me going quicker, I'd prefer to learnhow
> > > > > to do it myself.
> > > > > I would then be able to create a form enabling me to periodically
> > > > > (probably at the turn of our fiscal year) just enter the new ending
> > > > > date (or date range) to further populate the Calendar table.
>
> > > > > ~Mo
>
> > > > > (NOTE: I originally posted this in comp.lang.php, where it was
> > > > > suggested that I repost in this group.)
>
> > > > You could use an external scripting language like PHP to create the
> > > > calendar dates on-the-fly, and then compare this with data in your
> > > > table but, for a purely mysql solution, yes you'd probably need to
> > > > create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
> > > > little procedure for doing this very quickly. See his sight athttp://datacharmer.blogspot.com/2006/06/filling-test-tables-quickly.html.
> > > > Personally, I'd do as the other respondent suggested and just createa
> > > > calendar table along these lines. Note that you don't have to store
> > > > days, just dates. Days can be easily calculated using mysql's built-in
> > > > date functions.
>
> > > "See his site" (not "sight")!
>
> > > Actually I rather like one of the other approaches that was suggested.
> > > Have a table containing the correct number of rows for the amount of
> > > dates you want in you notional calendar table. You can then create the
> > > dates on the fly by querying this table.- Hide quoted text -
>
> > > - Show quoted text -
>
> > I'd really like to understand both options fully prior to making a
> > decision.
> > Can you further explain the suggestion of creating dates on the fly by
> > querying against a table with day numbers?
>
> > ~Mo
>
> OK, here is an example. Suppose you have a table called dates, which
> contains 366 rows. The rows do not need to contain anything, just an
> empty field will do.
>
> Now, suppose you want to produce a list of all dates from tomorrow for
> one year:
> SET @d =0;
> SELECT
> *MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + ( @d := @d +1 ) ) *calendar
> FROM `dates` ;
>
> Alternatively, if dates contained an integer column with the numbers 1
> to 366 (let's call the column daynum):
> SELECT
> *MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + daynum ) *calendar
> FROM `dates` ;- Hide quoted text -
>
> - Show quoted text -

Wow, that's pretty cool. Being a novice, I've never come across the
MAKEDATE function before.

So in theory, I can create the dates on the fly, load them into an
array and use them from there.
Is there a counter function to MAKEDATE where it would take a date,
and provide the day number from that?
This would allow me to generate the complete date list for the range
specified, whether it be 30 days or 752 days.

~Mo

On May 9, 7:49*am, Mo <Mehile.Orl...@gmail.com> wrote:
> On May 9, 6:58*am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 8 May, 19:47, Mo <Mehile.Orl...@gmail.com> wrote:
>
> > > On May 8, 5:04 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > On 8 May, 12:37, strawberry <zac.ca...@gmail.com> wrote:
>
> > > > > On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
>
> > > > > > After a little PHP education, my first project (to get my feet wet) is
> > > > > > to
> > > > > > make an employee time-tracking program for our small business.
>
> > > > > > *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED***
>
> > > > > > I'm in my planning stage, and here's my hurdle:
> > > > > > On the time report, we want to see EVERY date under each employee,
> > > > > > and
> > > > > > the string "Absent" for the dates on which the user has no punch
> > > > > > times. (It would also be nice to include day-names on the report..)
> > > > > > Obviously the Time table will only have entries for when people
> > > > > > actually clock in/out, so how do I get the other dates to show on the
> > > > > > report?
> > > > > > Any suggestions?
>
> > > > > > One guy here suggested just adding a table *which includes every date
> > > > > > from here to ...., then just do an outer join.
> > > > > > This seems like it could work well, but I would like to get someinput
> > > > > > from those with more experience.
>
> > > > > > Lastly, if this Calendar table is a good way to go, does anyone have
> > > > > > any
> > > > > > advice on how to go about creating one (preferrably with day anddate
> > > > > > values), *or a table like this wich they coud just provide a SQL
> > > > > > export of.
>
> > > > > > While a table dump would get me going quicker, I'd prefer to learn how
> > > > > > to do it myself.
> > > > > > I would then be able to create a form enabling me to periodically
> > > > > > (probably at the turn of our fiscal year) just enter the new ending
> > > > > > date (or date range) to further populate the Calendar table.
>
> > > > > > ~Mo
>
> > > > > > (NOTE: I originally posted this in comp.lang.php, where it was
> > > > > > suggested that I repost in this group.)
>
> > > > > You could use an external scripting language like PHP to create the
> > > > > calendar dates on-the-fly, and then compare this with data in your
> > > > > table but, for a purely mysql solution, yes you'd probably need to
> > > > > create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
> > > > > little procedure for doing this very quickly. See his sight athttp://datacharmer.blogspot.com/2006/06/filling-test-tables-quickly.html.
> > > > > Personally, I'd do as the other respondent suggested and just create a
> > > > > calendar table along these lines. Note that you don't have to store
> > > > > days, just dates. Days can be easily calculated using mysql's built-in
> > > > > date functions.
>
> > > > "See his site" (not "sight")!
>
> > > > Actually I rather like one of the other approaches that was suggested.
> > > > Have a table containing the correct number of rows for the amount of
> > > > dates you want in you notional calendar table. You can then create the
> > > > dates on the fly by querying this table.- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > I'd really like to understand both options fully prior to making a
> > > decision.
> > > Can you further explain the suggestion of creating dates on the fly by
> > > querying against a table with day numbers?
>
> > > ~Mo
>
> > OK, here is an example. Suppose you have a table called dates, which
> > contains 366 rows. The rows do not need to contain anything, just an
> > empty field will do.
>
> > Now, suppose you want to produce a list of all dates from tomorrow for
> > one year:
> > SET @d =0;
> > SELECT
> > *MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + ( @d := @d +1 ) ) *calendar
> > FROM `dates` ;
>
> > Alternatively, if dates contained an integer column with the numbers 1
> > to 366 (let's call the column daynum):
> > SELECT
> > *MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + daynum ) *calendar
> > FROM `dates` ;- Hide quoted text -
>
> > - Show quoted text -
>
> Wow, that's pretty cool. Being a novice, I've never come across the
> MAKEDATE function before.
>
> So in theory, I can create the dates on the fly, load them into an
> array and use them from there.
> Is there a counter function to MAKEDATE where it would take a date,
> and provide the day number from that?
> This would allow me to generate the complete date list for the range
> specified, whether it be 30 days or 752 days.
>
> ~Mo- Hide quoted text -
>
> - Show quoted text -

I just kept digging throught the MySQL Ref Manual and found it :
DAYOFYEAR()
I'm still interested in any input or advise regarding this issue.

Thanks-a-bunch,
Mo

On 9 May, 15:49, Mo <Mehile.Orl...@gmail.com> wrote:
> On May 9, 6:58 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 8 May, 19:47, Mo <Mehile.Orl...@gmail.com> wrote:
>
> > > On May 8, 5:04 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > > On 8 May, 12:37, strawberry <zac.ca...@gmail.com> wrote:
>
> > > > > On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
>
> > > > > > After a little PHP education, my first project (to get my feet wet) is
> > > > > > to
> > > > > > make an employee time-tracking program for our small business.
>
> > > > > > *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***
>
> > > > > > I'm in my planning stage, and here's my hurdle:
> > > > > > On the time report, we want to see EVERY date under each employee,
> > > > > > and
> > > > > > the string "Absent" for the dates on which the user has no punch
> > > > > > times. (It would also be nice to include day-names on the report.)
> > > > > > Obviously the Time table will only have entries for when people
> > > > > > actually clock in/out, so how do I get the other dates to show on the
> > > > > > report?
> > > > > > Any suggestions?
>
> > > > > > One guy here suggested just adding a table which includes every date
> > > > > > from here to ...., then just do an outer join.
> > > > > > This seems like it could work well, but I would like to get some input
> > > > > > from those with more experience.
>
> > > > > > Lastly, if this Calendar table is a good way to go, does anyone have
> > > > > > any
> > > > > > advice on how to go about creating one (preferrably with day and date
> > > > > > values), or a table like this wich they coud just provide a SQL
> > > > > > export of.
>
> > > > > > While a table dump would get me going quicker, I'd prefer to learn how
> > > > > > to do it myself.
> > > > > > I would then be able to create a form enabling me to periodically
> > > > > > (probably at the turn of our fiscal year) just enter the new ending
> > > > > > date (or date range) to further populate the Calendar table.
>
> > > > > > ~Mo
>
> > > > > > (NOTE: I originally posted this in comp.lang.php, where it was
> > > > > > suggested that I repost in this group.)
>
> > > > > You could use an external scripting language like PHP to create the
> > > > > calendar dates on-the-fly, and then compare this with data in your
> > > > > table but, for a purely mysql solution, yes you'd probably need to
> > > > > create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
> > > > > little procedure for doing this very quickly. See his sight athttp://datacharmer.blogspot.com/2006/06/filling-test-tables-quickly.html.
> > > > > Personally, I'd do as the other respondent suggested and just create a
> > > > > calendar table along these lines. Note that you don't have to store
> > > > > days, just dates. Days can be easily calculated using mysql's built-in
> > > > > date functions.
>
> > > > "See his site" (not "sight")!
>
> > > > Actually I rather like one of the other approaches that was suggested.
> > > > Have a table containing the correct number of rows for the amount of
> > > > dates you want in you notional calendar table. You can then create the
> > > > dates on the fly by querying this table.- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > I'd really like to understand both options fully prior to making a
> > > decision.
> > > Can you further explain the suggestion of creating dates on the fly by
> > > querying against a table with day numbers?
>
> > > ~Mo
>
> > OK, here is an example. Suppose you have a table called dates, which
> > contains 366 rows. The rows do not need to contain anything, just an
> > empty field will do.
>
> > Now, suppose you want to produce a list of all dates from tomorrow for
> > one year:
> > SET @d =0;
> > SELECT
> > MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + ( @d := @d +1 ) ) calendar
> > FROM `dates` ;
>
> > Alternatively, if dates contained an integer column with the numbers 1
> > to 366 (let's call the column daynum):
> > SELECT
> > MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + daynum ) calendar
> > FROM `dates` ;- Hide quoted text -
>
> > - Show quoted text -
>
> Wow, that's pretty cool. Being a novice, I've never come across the
> MAKEDATE function before.
>
> So in theory, I can create the dates on the fly, load them into an
> array and use them from there.
> Is there a counter function to MAKEDATE where it would take a date,
> and provide the day number from that?
> This would allow me to generate the complete date list for the range
> specified, whether it be 30 days or 752 days.
>
> ~Mo

Errr, I used that "counter function" in the sample query, to make it
count from today! See if you can spot it.

And by the way, even being "an expert", I had never come across the
MAKEDATE function before either. I just assumed that there would be
date/time functions that could help me out, so I went to the manual
page for date/time functions and looked down the list till I found a
likely candidate.

Believe it or not, you could haev dome the same.