SQL question

Hey all,

I have an SQL query that's stumping me.

I have two date variables, $start and $end that are in
mm/dd/yyyy format and two database fields, start_date and
no_donations. The start date is mm/dd/yyyy format and
no_donations is an integer that represents the number of
months from start_date that donations will be made.

So if start date is say 02/01/2010 and no_dations is 4 then
donations will be made four times from the start date for four
months.

What I need to do is come up with a query that will determine
if the start_date + no_donations falls within $start and $end.

But I'm pretty stumped. How can I convert start_date +
no_donations in the database to the date when the last
donation will take place so I'll now if the donations fall
between $start and $end?

Any suggestions would be very help and appreciated,
Skip

--
====================================
Skip Evans
PenguinSites.com, LLC
503 S Baldwin St, #1
Madison WI 53703
608.250.2720
http://penguinsites.com
------------------------------------
Those of you who believe in
telekinesis, raise my hand.
-- Kurt Vonnegut

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL question

On Mon, Jan 25, 2010 at 09:54:40PM -0600, Skip Evans wrote:

> Hey all,
>
> I have an SQL query that's stumping me.
>
> I have two date variables, $start and $end that are in
> mm/dd/yyyy format and two database fields, start_date and
> no_donations. The start date is mm/dd/yyyy format and
> no_donations is an integer that represents the number of
> months from start_date that donations will be made.
>
> So if start date is say 02/01/2010 and no_dations is 4 then
> donations will be made four times from the start date for four
> months.
>
> What I need to do is come up with a query that will determine
> if the start_date + no_donations falls within $start and $end.
>
> But I'm pretty stumped. How can I convert start_date +
> no_donations in the database to the date when the last
> donation will take place so I'll now if the donations fall
> between $start and $end?
>
> Any suggestions would be very help and appreciated,

If there's a way to do this in SQL itself, I don't know what it is. But
in my opinion, you need a date class which can do date comparisons.

(If you end up programming one yourself, save yourself some time and
convert all dates to Julian day numbers internally. This saves massive
amounts of computation in determining intervals and durations.
Typically, coders try to store dates in unix timestamps internally, and
then add 86400 seconds for every day to calculate intervals and such.
This is often inaccurate. Julian days are far more accurate.)

Paul

--
Paul M. Foster

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL question

Paul M Foster wrote:

> Typically, coders try to store dates in unix timestamps internally, and
> then add 86400 seconds for every day to calculate intervals and such.
> This is often inaccurate. Julian days are far more accurate.)
>
> Paul
>

I use seconds from epoch in the database simply because it works so well
with the php date() function.

If you need something where Julian day really is better, I assume it
isn't that hard to convert between posix and julian day, though it seems
odd to me that it isn't part of the date() function. It probably should be.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL question

Michael A. Peters wrote:

>
> If you need something where Julian day really is better, I assume it
> isn't that hard to convert between posix and julian day, though it seems
> odd to me that it isn't part of the date() function. It probably should be.
>

Looks like unixtojd() and jdtounix() do it.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL question

At 9:54 PM -0600 1/25/10, Skip Evans wrote:
>Hey all,
>
>I have an SQL query that's stumping me.
>
>I have two date variables, $start and $end that are in mm/dd/yyyy
>format and two database fields, start_date and no_donations. The
>start date is mm/dd/yyyy format and no_donations is an integer that
>represents the number of months from start_date that donations will
>be made.
>
>So if start date is say 02/01/2010 and no_dations is 4 then
>donations will be made four times from the start date for four
>months.
>
>What I need to do is come up with a query that will determine if the
>start_date + no_donations falls within $start and $end.
>
>But I'm pretty stumped. How can I convert start_date + no_donations
>in the database to the date when the last donation will take place
>so I'll now if the donations fall between $start and $end?
>
>Any suggestions would be very help and appreciated,
>Skip
>
>--
>====================================
>Skip Evans

Skip:

Here's a snip-it of code from one of my projects:

$qry = "SELECT SUM(amount) AS subtotal, COUNT(*) AS num
FROM transaction
WHERE product_type = 'video'
AND UNIX_TIMESTAMP(transtime) > " . strtotime($startd) . "
AND UNIX_TIMESTAMP(transtime) < " . strtotime($endd) . "
AND is_charged = 1
AND notes = 'Approved'
AND is_refunded = 0
AND transnum NOT LIKE 'TEST-PNREF'
AND product_id LIKE '$key' ";
$db2->select($qry);
while ($db2->readrow())
{
$rev = $db2->data["subtotal"]; // this is the total amount
collected for the sale
$num = $db2->data["num"]; // this is the number
of this type of sale
}

Clearly, you don't need everything there, but the timestamp notation
will give you better insight into how to use dates in your query.

Cheers,

tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL question

Michael A. Peters wrote on 26/01/2010 06:04:

> I use seconds from epoch in the database simply because it works so well
> with the php date() function.
>
> If you need something where Julian day really is better, I assume it
> isn't that hard to convert between posix and julian day, though it seems
> odd to me that it isn't part of the date() function. It probably should be.

When I do date comparisons in MySQL I use the to_days() function.

> What I need to do is come up with a query that will determine if the
start_date + no_donations falls within $start and $end.

In the given example one could determine that a month is always 30 days
and then say to_days(start_date)+(no_donations*30) < to_days(end). This
would however be a very loose method. You could go for finding the
number of days in the current month and substract that (10th. = 30-10),
play with MySQLs left() function

But Skip, as the others say, use a date class, since you're passing a
php var on to the SQL anyway, then you could determine the exact days
from start to end of donation. Combine this with to_days and you have
your solution

--
Kind regards
Kim Emax - masterminds.dk

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL question

Kim Madsen wrote:
>
> But Skip, as the others say, use a date class, since you're passing a
> php var on to the SQL anyway, then you could determine the exact days
> from start to end of donation. Combine this with to_days and you have
> your solution
>

Yes, this sounds like the best way to go.

Thanks everyone!

Skip

--
====================================
Skip Evans
PenguinSites.com, LLC
503 S Baldwin St, #1
Madison WI 53703
608.250.2720
http://penguinsites.com
------------------------------------
Those of you who believe in
telekinesis, raise my hand.
-- Kurt Vonnegut

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php