[iresha]

Hi all,

I have to get leave records for a month from database. There are over 700 employees in the database. And also each employee data
must check with each date in a month(that means 31 or 30 days repeating) I created FOR loop for this one. but it takes very long time(over 20 mins)
Is there any easy way to get the data.
Let me know anyone know to do this.

Thankyou all

this is part of my java code.

//for loop for displaying the details of each date of the month for an employee
for(int i=1;i<=(int)cols;i++)
{
colDateDB = Utility.formatDateToDB(colDate, conn);

try
{
group = Utility.getEmployeeShiftGroup(nic, colDateDB, conn);
}
catch(Exception e){}

if(group == null || group.length() <= 0)
isRoster = false;
else
{
pRoster.clearParameters();

pRoster.setString(1, colDateDB);//***date should be given
pRoster.setString(2, group);

rsRoster = pRoster.executeQuery();

if(rsRoster.next())
isRoster = true;
else
isRoster = false;
}//end of else

cal.setTime(Utility.formatDateToDB2(colDate));
day = Utility.getDayOfWeekName(cal.get(GregorianCalendar .DAY_OF_WEEK));

pLeave.clearParameters();
pLeave.setString(1, colDateDB);
pLeave.setString(2, nic);
rsLeave = pLeave.executeQuery();

var = "";

if(isRoster) //roster employee
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = ""; //blank for full work
if(rsRoster.getString(4).equalsIgnoreCase("N"))
var = "N";

if(!rsAtt.next())
isRsAtt = false;

if(rsLeave.next() && rsLeave.getFloat(3) == 0.5 && rsLeave.getString(4) != null)
var = "HF";
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";

}//end of if(date equals)
else
{
if(rsRoster.getString(4).equalsIgnoreCase("O"))
var = "H";
else
{
if(rsLeave.next()) //check whether he has applied for leave
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}
}//end of if(isRsAtt)
else
{
if(rsRoster.getString(4).equalsIgnoreCase("O"))
var = "H";
else
{
if(rsLeave.next()) //check whether he has applied for leave
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}

}//end of if(isRoster)

else //general employee
{
isHoliday = false;

pHoliday.clearParameters();
pHoliday.setString(1, colDateDB);
rsHoliday = pHoliday.executeQuery();

if(rsHoliday.next())
isHoliday = true;

if(day.equalsIgnoreCase("Sunday"))
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = "";
if(!rsAtt.next())
isRsAtt = false;
}//end of if(date equals)
else
var = "SN";
}
else
var = "SN";
}//end of Sunday

else if(day.equalsIgnoreCase("Saturday"))
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = "";
if(!rsAtt.next())
isRsAtt = false;
}//end of if(date equals)
else if(!workOnSat.equalsIgnoreCase("Y"))
var = "S";
else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of if(isRs)
else if(!workOnSat.equalsIgnoreCase("Y"))
var = "S";
else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of Saturday

else //week day
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = "";
if(!rsAtt.next())
isRsAtt = false;

if(rsLeave.next() && rsLeave.getFloat(3) == 0.5 && rsLeave.getString(4) != null)
var = "HF";
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
}//end of if(date equals)

else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of if(isRs)
else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of weekday


if(rsHoliday!=null)
rsHoliday.close();
}//end general emp
if ("NP".equals(var) && "N".equals(processAttendance) ){
var = "";
}


if(rsLeave!=null)
rsLeave.close();

if(rsRoster!=null)
rsRoster.close();