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` ;