I have a problem (help need in SQL and ResultSet)

[Maki Jav]

Hi,
I need to get total of all the dates (start dates and end dates of jobs) for each person in table and compare it with some value like say 3 months
and if that person has worked more than that, put it in the hashtable.
The tables are:
1. PersonalInfo
2. dates
dates has these ID, startdate, and enddate columns. PersonalInfo has ID and Names.

I know that I can use the Date object and getTime() of Date class to pick the difference.
I am posting the code I was trying but to be frank I am not very good at SQL and besides
ResultSet doesn't support SQL expressions like
sel = "SELECT items.ItemID, items.Item, items.Size, items.Color, items.Accessories, items.Price, items.Itemsinhand, items.LatestItems, [Itemsinhand]+[LatestItems] AS Total FROM items"

The code I was trying is:
import java.util.*;
import java.sql.*;

public class Test1{
public static void main(String args[]) {
Connection con=null;
Statementst=null;
ResultSet rs=null;
boolean go=true;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dif="";
int [] ids=null;
String q="select distinct id from dates";
String q2="select id,date2,date1 from dates where id=";
String q3="select dates.id,date2,date1,Name from dates,PersonalInfo where dates.ID=PersonalInfo.ID";
con=DriverManager.getConnection("jdbc dbc evdir","","");
st=con.createStatement();
rs=st.executeQuery(q);
int cnt=0;
// first count rows of distinct IDs
while (rs.next()){
cnt++;
}
// now set length of the array to hold IDs
ids=new int[cnt];
cnt=0;
// to run again the query 'q'
rs=st.executeQuery(q);
// now put values in the array
while(rs.next()){
ids[cnt]=rs.getInt("ID");
System.out.println(ids[cnt]);
cnt++;
}
cnt=0;
rs=null;
// now the real game begins
/*
for(int i=0;i<ids.length;i++){
total=0;
rs=st.executeQuery(q2+ids[i]);
while(rs.next()){
System.out.println("ID is "+ids[i]);
int pid=rs.getInt("ID");
java.util.Date dt1=rs.getDate("date1");
java.util.Date dt2=rs.getDate("date2");
long d1=dt1.getTime();
long d2=dt2.getTime();
long diff=d2-d1;
if(ids[i]==pid)
total=total+diff;
//java.util.Date d=new java.util.Date(total);
long days=total/(24*60*60*1000);
System.out.println(days);
}
*/
rs=st.executeQuery(q3);
long tds=0;
int compareParameter=31;
Hashtable hits=new Hashtable();
while(rs.next()){
int pid=rs.getInt("ID");
String name=rs.getString("Name");

if(ids[cnt]!=pid){
System.out.println(tds);
if(tds==compareParameter){System.out.println("Hit");
hits.put(""+pid,name);
}
tds=0;
cnt++;
}
java.util.Date dt1=rs.getDate("date1");
java.util.Date dt2=rs.getDate("date2");
long d1=dt1.getTime();
long d2=dt2.getTime();
long diff=d2-d1;
long days=diff/(24*60*60*1000);

if(ids[cnt]==pid){
tds=tds+days;
if(tds==compareParameter)
{
System.out.println("Hit");
hits.put(""+pid,name);
}

}
// for last record
if(pid==ids[ids.length-1])
{System.out.println(tds);
if(tds==compareParameter){System.out.println("Hit");
hits.put(""+pid,name);
}
}
}
System.out.println(hits);
System.out.println(hits.size());
rs.close();
st.close();
con.close();
go=false;
}catch(Exception e){System.out.println(e);}
if(go)
try{

rs.close();
st.close();
con.close();
}catch(Exception e){System.out.println("SECOND "+e);}

}
}

Thanx in advance

[Greg Charles]

Did you have a question? I assume your code isn't working (though I haven't run it), and you want to know why. You're doing all the basic stuff you need to do, but maybe just not putting it together. Firstly, why three queries? Just run the query that gets you all the information you need for the logic, i.e., the date fields, and what you need to put in the hashtable, i.e. the ID and whatever else you need. You can just iterate through that result set, run the date logic as you are doing, and cram stuff into the hashtable as appropriate.
Actually, I said you are already doing the date logic, but it gets a bit muddled. You compute the right value (I think) into the days variable, but then you run through some strange logic with tds and pid that I can't follow. The only test you need to make, as I see it, is comparing days to 90.

[Maki Jav]

Thanks Greg,
I was looking to do all through a SQL statement
but some how ResultSet was not acting the way I wanted it to.
Anyway I tried (real hard) at a SQL query and got it working.
Now the ResultSet is working beautifully.
Some aspects of a query are always vendor dependant.
I refined a query for Ms Access
(it will probably work with Ms SqlServer too).
The query is
SELECT Name, dates.ID, Sum(DateDiff('m',dates.date1,dates.date2)) AS d
FROM PersonalInfo, dates
WHERE PersonalInfo.ID=dates.ID
GROUP BY Name, dates.ID
HAVING Sum(DateDiff('m',dates.date1,dates.date2))>=3
ORDER BY Sum(DateDiff('m',dates.date1,dates.date2)) DESC;
------------------------------------
DateDiff is MsAccess method to do the obvious
'm' means month, it return numbers of month
date1 date2 are table fields. But you have to write earlier date first and later date last.
Order by is optional for me to use.
I am picking values of d,ID,Name with ResultSet the usual way i.e.
rs.getInt("d");
rs.getString("Name");
rs.getInt("ID");
I hope that you like it.
Thanx
Maki Jav
[ March 22, 2003: Message edited by: Maki Jav ]