Please help!

Please help!

am 08.11.2005 17:30:51 von How Loon

# TABLES STRUCTURES
# =================

CREATE TABLE IF NOT EXISTS `course` (
`Course_Code` smallint unsigned NOT NULL
auto_increment,
`Course_ID` varchar(15) NOT NULL default '',
`Course_Category_Code` smallint unsigned NOT NULL,
`Name` varchar(100) default NULL,
`Date_Start` date default NULL,
`Date_End` date default NULL,
`Day_Involve` varchar(30) default NULL,
`Time_Start` time default NULL,
`Time_End` time default NULL,
`Fee_Type` varchar(30) default 'Monthly',
`Fee` decimal(10,2) default '0.00',
`Trainer_Code` smallint unsigned NOT NULL default
'0',
`Secondary_Trainer_Code` smallint unsigned NOT NULL
default '0',
PRIMARY KEY (`Course_Code`),
UNIQUE KEY (`Course_ID`),
KEY (`Course_Category_Code`,`Day_Involve`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `attendance` (
`Attendance_Code` int unsigned NOT NULL
auto_increment,
`Attendance_Date` date NOT NULL,
`Course_Code` smallint unsigned NOT NULL default
'0',
PRIMARY KEY (`Attendance_Code`),
KEY (`Attendance_Date`,`Course_Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `attendance_detail` (
`Attendance_Code` int unsigned NOT NULL default '0',
`Trainer_Trainee_Code` mediumint unsigned NOT NULL
default '0',
`Attendance_Time` time NOT NULL,
`Type` varchar(3) default NULL,
PRIMARY KEY
(`Attendance_Code`,`Trainer_Trainee_Code`,`Type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `rate` (
`Rate_Code` smallint unsigned NOT NULL
auto_increment,
`Rate_ID` varchar(15) NOT NULL default '',
`Name` varchar(100) default NULL,
`Type` varchar(30) default NULL,
`Rate` decimal(10,2) default '0.00',
PRIMARY KEY (`Rate_Code`),
UNIQUE KEY (`Rate_ID`),
KEY (`Type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `trainer` (
`Trainer_Code` smallint unsigned NOT NULL
auto_increment,
`Trainer_ID` varchar(15) NOT NULL default '',
`Trainer_Category_Code` smallint unsigned NOT NULL,
`Rate_Code` smallint unsigned NOT NULL,
`Name` varchar(50) default NULL,
`Alias` varchar(20) default NULL,
`NRIC_No` varchar(14) default NULL,
PRIMARY KEY (`Trainer_Code`),
UNIQUE KEY (`Trainer_ID`),
KEY (`NRIC_No`,`Trainer_Category_Code`,`Rate_Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



# Scripts
# =======

SET @Calculated_Salary = 0.00;

SET @Salary_Type = 'NA';
SET @MONTHLY = 'MONTHLY';
SET @PER_CLASS = 'PER_CLASS';
SET @HOURLY = 'HOURLY';

SET @Rate_Amount = 0.00;

SET @No_of_Class_Attended = 0;

SET @Trainer_Type_Code = 'TRR';

SET @Duration_In_Hour = 0.00;

SET @Salary_Amount = 0.00;


SELECT
trr.Trainer_ID,
@Salary_Type := rte.Type AS 'Rate Type',
@Rate_Amount := rte.Rate AS 'Rate Amount',
@No_of_Class_Attended := COUNT(*) AS 'Classes
Attended',
@Duration_In_Hour :=
IFNULL(SUM(TIME_TO_SEC(TIMEDIFF(crs.Time_End,crs.Time_Start) )
/ 3600), 0.00) As 'Hours Taught',
@Salary_Amount := CASE @Salary_Type
WHEN @MONTHLY THEN
@Rate_Amount
WHEN @PER_CLASS THEN
@Rate_Amount * @No_of_Class_Attended ## Something
wrong here
WHEN @HOURLY THEN
@Rate_Amount * @Duration_In_Hour ## Something
wrong here
ELSE
0.00
END AS 'Salary Amount'

FROM
course crs

LEFT JOIN
attendance att
ON
(att.Course_Code = crs.Course_Code)
AND
(att.Attendance_Date BETWEEN 20051101 AND 20051231)

LEFT JOIN
attendance_detail atd
ON
(atd.Attendance_Code = att.Attendance_Code)

INNER JOIN
trainer trr
ON
(trr.Trainer_Code = atd.Trainer_Trainee_Code)

LEFT JOIN
rate rte
ON
(rte.Rate_Code = trr.Rate_Code)

WHERE
atd.Type = @Trainer_Type_Code
GROUP BY
trr.Trainer_ID;



# Results Set
# ===========

Trainer_ID Rate Type Rate Amount Classes Attended
Hours Taught Salary Amount
------------------------------------------------------------ ---------------------
ALEXMOK HOURLY 25.00 1
2.50 0
KWAIYING PER_CLASS 30.00 1
4.00 0
NELSONKEW MONTHLY 1000.00 6
24.00 1000
NEWTONCP MONTHLY 750.00 3
12.00 750
SARAVANAN MONTHLY 850.00 1
1.50 850
WMY001 PER_CLASS 30.00 1
2.50 0



# QUESTION
# ========

Why I keep on getting the WRONG result on "Salary
Amount" when the "Rate Type" is HOURLY and PER_CLASS ?

I have noted that the variables @No_of_Class_Attended
and @Duration_In_Hour are always as ZERO !

Please advise and I'm using MySQL ver 4.1.15, thanks :-)



__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Please help!

am 10.11.2005 00:48:18 von jbonnett

There is a statement in the MySQL manual that says: -

"The general rule is to never assign a value to a user variable in one
part of a statement and use the same variable in some other part the
same statement. You might get the results you expect, but this is not
guaranteed."

I think this is why those variables are coming out as zero because they
depend on results of aggregations that are really only available when
the query is finished. You will probably do better making a temporary
table from your aggregate query and do your case statement in a
subsequent query on the temporary table. A nested query might work too
with the case statement in the outer query.

John B.

-----Original Message-----
From: ascll [mailto:ascll@yahoo.com]=20
Sent: Wednesday, 9 November 2005 3:01 AM
To: win32@ lists.mysql.com
Subject: Please help!

# TABLES STRUCTURES
# =================3D

CREATE TABLE IF NOT EXISTS `course` (
`Course_Code` smallint unsigned NOT NULL
auto_increment,
`Course_ID` varchar(15) NOT NULL default '',
`Course_Category_Code` smallint unsigned NOT NULL,
`Name` varchar(100) default NULL,
`Date_Start` date default NULL,
`Date_End` date default NULL,
`Day_Involve` varchar(30) default NULL,
`Time_Start` time default NULL,
`Time_End` time default NULL,
`Fee_Type` varchar(30) default 'Monthly',
`Fee` decimal(10,2) default '0.00',
`Trainer_Code` smallint unsigned NOT NULL default
'0',
`Secondary_Trainer_Code` smallint unsigned NOT NULL
default '0',
PRIMARY KEY (`Course_Code`),
UNIQUE KEY (`Course_ID`),
KEY (`Course_Category_Code`,`Day_Involve`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;

CREATE TABLE IF NOT EXISTS `attendance` (
`Attendance_Code` int unsigned NOT NULL
auto_increment,
`Attendance_Date` date NOT NULL,
`Course_Code` smallint unsigned NOT NULL default
'0',
PRIMARY KEY (`Attendance_Code`),
KEY (`Attendance_Date`,`Course_Code`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;


CREATE TABLE IF NOT EXISTS `attendance_detail` (
`Attendance_Code` int unsigned NOT NULL default '0',
`Trainer_Trainee_Code` mediumint unsigned NOT NULL
default '0',
`Attendance_Time` time NOT NULL,
`Type` varchar(3) default NULL,
PRIMARY KEY
(`Attendance_Code`,`Trainer_Trainee_Code`,`Type`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;

CREATE TABLE IF NOT EXISTS `rate` (
`Rate_Code` smallint unsigned NOT NULL
auto_increment,
`Rate_ID` varchar(15) NOT NULL default '',
`Name` varchar(100) default NULL,
`Type` varchar(30) default NULL,
`Rate` decimal(10,2) default '0.00',
PRIMARY KEY (`Rate_Code`),
UNIQUE KEY (`Rate_ID`),
KEY (`Type`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;

CREATE TABLE IF NOT EXISTS `trainer` (
`Trainer_Code` smallint unsigned NOT NULL
auto_increment,
`Trainer_ID` varchar(15) NOT NULL default '',
`Trainer_Category_Code` smallint unsigned NOT NULL,
`Rate_Code` smallint unsigned NOT NULL,
`Name` varchar(50) default NULL,
`Alias` varchar(20) default NULL,
`NRIC_No` varchar(14) default NULL,
PRIMARY KEY (`Trainer_Code`),
UNIQUE KEY (`Trainer_ID`),
KEY (`NRIC_No`,`Trainer_Category_Code`,`Rate_Code`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;



# Scripts
# =======3D

SET @Calculated_Salary =3D 0.00;

SET @Salary_Type =3D 'NA';
SET @MONTHLY =3D 'MONTHLY';
SET @PER_CLASS =3D 'PER_CLASS';
SET @HOURLY =3D 'HOURLY';

SET @Rate_Amount =3D 0.00;

SET @No_of_Class_Attended =3D 0;

SET @Trainer_Type_Code =3D 'TRR';

SET @Duration_In_Hour =3D 0.00;

SET @Salary_Amount =3D 0.00;


SELECT
trr.Trainer_ID,
@Salary_Type :=3D rte.Type AS 'Rate Type',
@Rate_Amount :=3D rte.Rate AS 'Rate Amount',
@No_of_Class_Attended :=3D COUNT(*) AS 'Classes
Attended',
@Duration_In_Hour :=3D
IFNULL(SUM(TIME_TO_SEC(TIMEDIFF(crs.Time_End,crs.Time_Start) )
/ 3600), 0.00) As 'Hours Taught',
@Salary_Amount :=3D CASE @Salary_Type
WHEN @MONTHLY THEN
@Rate_Amount
WHEN @PER_CLASS THEN
@Rate_Amount * @No_of_Class_Attended ## Something
wrong here
WHEN @HOURLY THEN
@Rate_Amount * @Duration_In_Hour ## Something
wrong here
ELSE=20
0.00
END AS 'Salary Amount'

FROM
course crs

LEFT JOIN
attendance att
ON
(att.Course_Code =3D crs.Course_Code)
AND
(att.Attendance_Date BETWEEN 20051101 AND 20051231)

LEFT JOIN
attendance_detail atd
ON
(atd.Attendance_Code =3D att.Attendance_Code)

INNER JOIN
trainer trr
ON
(trr.Trainer_Code =3D atd.Trainer_Trainee_Code)

LEFT JOIN
rate rte
ON
(rte.Rate_Code =3D trr.Rate_Code)

WHERE
atd.Type =3D @Trainer_Type_Code
GROUP BY
trr.Trainer_ID;



# Results Set
# ===========3D

Trainer_ID Rate Type Rate Amount Classes Attended=20
Hours Taught Salary Amount
------------------------------------------------------------ ------------
---------
ALEXMOK HOURLY 25.00 1 =20
2.50 0
KWAIYING PER_CLASS 30.00 1 =20
4.00 0
NELSONKEW MONTHLY 1000.00 6 =20
24.00 1000
NEWTONCP MONTHLY 750.00 3 =20
12.00 750
SARAVANAN MONTHLY 850.00 1 =20
1.50 850
WMY001 PER_CLASS 30.00 1 =20
2.50 0



# QUESTION
# ========

Why I keep on getting the WRONG result on "Salary
Amount" when the "Rate Type" is HOURLY and PER_CLASS ?

I have noted that the variables @No_of_Class_Attended
and @Duration_In_Hour are always as ZERO !

Please advise and I'm using MySQL ver 4.1.15, thanks :-)


=09
__________________________________=20
Start your day with Yahoo! - Make it your home page!=20
http://www.yahoo.com/r/hs


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org