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.