Michael:
Again, Thank you for your excellent suggestions and quick response !!
I agree that "Two types of data means you should use two columns."
There is no excuse for bad design.
(I inherited the database and moved it to MySQL -- I should have converted
then.)
I will split the Source table into N and Period (probably with better
names.)
(I am indeed working with a "temp" table.
Only for debug purposes and to test before I sent the original problem.)
Concerning:
> > $qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
> > TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n,
> > TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period,
> > CASE
> > WHEN Period = 'Month' THEN DATE_ADD(CalDate, INTERVAL n MONTH)
> > WHEN Period = 'Year' THEN DATE_ADD(CalDate, INTERVAL n YEAR)
> > END
> > AS DueDate
> > FROM tblTemp";
Did you try it? (the above)
-- Yes
It should work.
-- I agree. However, It fails.
It doesn't work with "Temporary Calculated" columns
In other words (just for testing purposes because the results don't make
sense),
I was successful calculating and showing (not using) the N and Period
columns and getting results with:
Works:
$qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n,
TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period,
CASE
WHEN TagName = 'FT0701' THEN DATE_ADD(CalDate, INTERVAL Unit
MONTH)
WHEN TagName = 'FCV96008' THEN DATE_ADD(CalDate, INTERVAL Unit
YEAR)
END
AS DueDate
FROM tblTemp";
I think it fails for the same reason, I have trouble with using criteria
against "DueDate"
First Let me re-define tblTemp:
I concur with your recommentation of the following (with the fields already
defined in the table)
-- This is the right solution.
A conversion in tblTemp is a follows.
(Actually nPeriod comes from an other table but for our discussion.)
NEW Table Definition with New fields:
PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType
And new data:
'Valmont', '5', 'FCV96008', '2004-04-15', 1, 'Year'
'Valmont', '5', 'FT0701', '2004-03-10', 3, 'Month'
Returning to the "TemporaryCalculation" discussion:
Fails (in the WHERE clause):
$qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
CalPeriodType,
CASE
WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod MONTH)
WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod YEAR)
END
AS DueDate
FROM tblTemp
WHERE DueDate > '2005-01-01'";
(Note: DueDate is a "TemporaryCalculated" field)
Works:
$qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
CalPeriodType,
CASE
WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod MONTH)
WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod YEAR)
END
AS DueDate
FROM tblTemp
WHERE
CASE
WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod MONTH)
WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod YEAR)
END
> '2005-01-01'";
(Note: Where Criteria does not contain any "TemporaryCalculated" field.)
A simpler case of the above (but continuing the "TemporaryCalculation" field
discussion.)
Fails:
$qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
CalPeriodType,
(CalPeriod + Unit) AS CalNum
FROM tblTemp
WHERE CalNum > 7";
Works:
$qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
CalPeriodType,
(CalPeriod + Unit) AS CalNum
FROM tblTemp
WHERE (CalPeriod + Unit) > 7";
I do appreciate the thoroughness of your effort in coversion to Months,
However the potential for using Days looms ahead,
and the complexity of Days in combinations with Month lengths, and Leap
years
causes more confusion than the well defined CASE of Day, Month, Year.
Again -- Thank you for your excellent support and quick response.
Gerard Gilliland
[EMAIL PROTECTED]
----- Original Message -----
From: "Michael Stassen" <[EMAIL PROTECTED]>
To: "Gerard Gilliland" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, July 19, 2004 13:47
Subject: Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48am
>
> Gerard Gilliland wrote:
>
> > Michael:
> >
> > Thank you for your excellent suggestion and quick response.
>
> You're welcome.
>
> > I now have an operable DATE_ADD.
> > The solution to break nPeriod into n and Period would not work directly.
> > That is, in the SELECT statement in the form of ...
> >
> > $qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
> > TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n,
> > TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period,
> > CASE
> > WHEN Period = 'Month' THEN DATE_ADD(CalDate, INTERVAL n MONTH)
> > WHEN Period = 'Year' THEN DATE_ADD(CalDate, INTERVAL n YEAR)
> > END
> > AS DueDate
> > FROM tblTemp";
> >
> > ... would fail (I think) because n and Period were calculated values.
>
> Did you try it? It should work. The size of the INTERVAL, n, is allowed
to
> be a calculated value, as it is in your working query below. The problem
> you were having before is that the INTERVAL type (MONTH, YEAR, etc.) must
be
> literal text (so neither a column nor calculated value), hence the need
for
> the CASE statement.
>
> > However, I was sucessful using your direction and the other form of CASE
> > That is, I used the logic in the CASE statement directly:
> >
> > $qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
> > CASE TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1))
> > WHEN 'Month' THEN DATE_ADD(CalDate, INTERVAL
TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) MONTH)
> > WHEN 'Year' THEN DATE_ADD(CalDate, INTERVAL
TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) YEAR)
> > END
> > AS DueDate
> > FROM tblTemp";
>
> This is equivalent to the first query, except it displays the value of the
> nPeriod column, rather than separate n and Period values. DueDate will be
> the same either way.
>
> I should point out, however, that my real suggestion was that your nPeriod
> column has 2 pieces of data, interval size and interval type, crammed into
> one column. That's not the best way to go. Two types of data means you
> should use two columns.
>
> My examples below operate on your tblTemp, but I'm guessing that tblTemp
is
> actually a temporary table with results from a query against your main
> tables. If so, you'd make the following changes in the source table.
>
> ALTER TABLE tblTemp
> ADD COLUMN n TINYINT,
> ADD COLUMN Period ENUM ('Day','Month', 'Year');
> # Put the INTERVAL types you actually use in the ENUM
>
> UDATE TABLE tblTemp
> SET n = TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)),
> Period = TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1));
>
> ALTER TABLE tblTemp DROP COLUMN nPeriod,
>
> Then you could simply
>
> SELECT PlantName, Unit, TagName, CalDate,
> CASE
> WHEN Period = 'Month' THEN DATE_ADD(CalDate, INTERVAL n MONTH)
> WHEN Period = 'Year' THEN DATE_ADD(CalDate, INTERVAL n YEAR)
> END
> AS DueDate
> FROM tblTemp
>
>
> On the other hand, if all your INTERVALs are in either months or years,
then
> you can dispense with the Period column altogether, because n years = 12*n
> months:
>
> ALTER TABLE tblTemp ADD COLUMN n TINYINT;
>
> UDATE TABLE tblTemp
> SET n = TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)),
> WHERE nPeriod RLIKE 'Month';
>
> UDATE TABLE tblTemp
> SET n = 12 * TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)),
> WHERE nPeriod RLIKE 'Year';
> # 12 months per year
>
> ALTER TABLE tblTemp DROP COLUMN nPeriod,
>
> Then you could simply
>
> SELECT PlantName, Unit, TagName, CalDate,
> DATE_ADD(CalDate, INTERVAL n MONTH) AS DueDate
> FROM tblTemp;
>
> > Thank you,
> > Gerard Gilliland
> > [EMAIL PROTECTED]
>
> Michael
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
