RE: Unusual Date Query
hay ross
i had ot pick up data rows for the dates between from-date and to-date
user selection u know
i went about the problem with a loop .
heres the code
for($loop_mon=$frm_mon; $loop_mon=$to_mon ; $loop_mon++){
$ftch_rprt_dta_qry = select sum($rprt_col)
from $rprt_tbl,
tbl_chw
where month($rprt_tbl.date) = $loop_mon
and year($rprt_tbl.date) = $year
and tbl_chw.pu_id=$SelectedPuID
and $rprt_tbl.chw_id=tbl_chw.chw_id;
- do some varibale assignment
- generate a report
}
as u can see all this to generate a report
o i fogot to tell you i had a date field in every table
im not sure if this is what you r actually looking for
but i hope this helps you .
good luck
toby
From: Ross Davis - DataAnywhere.net [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: RE: Unusual Date Query
Date: Fri, 10 Oct 2003 17:02:42 -0700
That will only return the number of rows in Table1 What I want to
return 1 to n rows based on the number of days between date1 and daten
with each row having the date filled in.
Ross Davis
DataAnywhere.net
250-470-9192
ChaletsOnline.com is coming soon
Don't you deserve a vacation!
-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 2:00 PM
To: Ross Davis - DataAnywhere.net
Cc: [EMAIL PROTECTED]
Subject: Re: Unusual Date Query
At 12:59 PM 10/10/2003, you wrote:
I want know if there is a way to return every date between 2 date
ranges regardless if there is a row in a table. I have a table that
contains daily pricing information and I want to be able to do a mass
updated/insert records. I know how to write the updates/inserts if I
have table that contains EVERY date between the date ranges, but I
don't want to keep a table around that has one row for every possible
date.
I can't use the replace into command because of a foreign keys on the
table.
The insert will look something like this
Insert into daily_rate from
select date,123.45
from allpossibledates
left join daily_rate on allpossibledates.date=daily_rate.date
where date between 20030901 and 20031010
and daily_rate.date is null
The problem is I don't know how to generate allpossibledates easily on
the fly.
I am running 3.23.58 and can't upgrade to the latest versions yet due
to incompatibilties with the timestamp formats that were changed.
TIA
Ross
Ross,
This should get you started:
set @n=0;
select date_add(CurDate(), INTERVAL (@n := @n + 1) DAY), col1 from
table1;
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
_
Instant message in style with MSN Messenger 6.0. Download it now FREE!
http://msnmessenger-download.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unusual Date Query
At 12:59 PM 10/10/2003, you wrote: I want know if there is a way to return every date between 2 date ranges regardless if there is a row in a table. I have a table that contains daily pricing information and I want to be able to do a mass updated/insert records. I know how to write the updates/inserts if I have table that contains EVERY date between the date ranges, but I don't want to keep a table around that has one row for every possible date. I can't use the replace into command because of a foreign keys on the table. The insert will look something like this Insert into daily_rate from select date,123.45 from allpossibledates left join daily_rate on allpossibledates.date=daily_rate.date where date between 20030901 and 20031010 and daily_rate.date is null The problem is I don't know how to generate allpossibledates easily on the fly. I am running 3.23.58 and can't upgrade to the latest versions yet due to incompatibilties with the timestamp formats that were changed. TIA Ross Ross, This should get you started: set @n=0; select date_add(CurDate(), INTERVAL (@n := @n + 1) DAY), col1 from table1; Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unusual Date Query
That will only return the number of rows in Table1 What I want to return 1 to n rows based on the number of days between date1 and daten with each row having the date filled in. Ross Davis DataAnywhere.net 250-470-9192 ChaletsOnline.com is coming soon Don't you deserve a vacation! -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 2:00 PM To: Ross Davis - DataAnywhere.net Cc: [EMAIL PROTECTED] Subject: Re: Unusual Date Query At 12:59 PM 10/10/2003, you wrote: I want know if there is a way to return every date between 2 date ranges regardless if there is a row in a table. I have a table that contains daily pricing information and I want to be able to do a mass updated/insert records. I know how to write the updates/inserts if I have table that contains EVERY date between the date ranges, but I don't want to keep a table around that has one row for every possible date. I can't use the replace into command because of a foreign keys on the table. The insert will look something like this Insert into daily_rate from select date,123.45 from allpossibledates left join daily_rate on allpossibledates.date=daily_rate.date where date between 20030901 and 20031010 and daily_rate.date is null The problem is I don't know how to generate allpossibledates easily on the fly. I am running 3.23.58 and can't upgrade to the latest versions yet due to incompatibilties with the timestamp formats that were changed. TIA Ross Ross, This should get you started: set @n=0; select date_add(CurDate(), INTERVAL (@n := @n + 1) DAY), col1 from table1; Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
