Number of entry query.

MSSQL2K
SP4

Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.

--drop table #foo
create table #foo
(empID int,
transDate datetime,
transType varchar(10))

insert into #foo values(1, '01/01/06 01:01:01','Insert')
insert into #foo values(1, '01/01/06 01:01:02','Update')
insert into #foo values(1, '01/01/06 01:01:03','Delete')
insert into #foo values(2, '01/01/06 01:01:01','Insert')
insert into #foo values(2, '01/01/06 01:01:02','Update')

select f.empID, Change =
(select count(transDate) from #foo f2
where f2.empID = f.empID
group by empID),
f.transDate, f.transType
from #foo f

Current results:

1 3 2006-01-01 01:01:01.000 Insert
1 3 2006-01-01 01:01:02.000 Update
1 3 2006-01-01 01:01:03.000 Delete
2 2 2006-01-01 01:01:01.000 Insert
2 2 2006-01-01 01:01:02.000 Update

Desired results:

1 1 2006-01-01 01:01:01.000 Insert
1 2 2006-01-01 01:01:02.000 Update
1 3 2006-01-01 01:01:03.000 Delete
2 1 2006-01-01 01:01:01.000 Insert
2 2 2006-01-01 01:01:02.000 Update

TIA, CFRwhere f2.empID = f.empID AND f2.transdate <= f.transdate

Which presumes no 2 can have the same date/time value.|||So close, yet so far. Thanks.|||Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.|||Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.
Nah, I am pretty sure he means that until your assist, CFR was so closer and yet so far from their solution.

After all, they would have asked for more help other wise, or said what they were getting wrong.

I guess we should be glad that they at least wrote back to thank you. Some folks get the answer, and then disappear.|||As Code Carpenter mentioned, I was so close yet so far. The code did exactly what I needed. As far as disappearing, Im afraid you folks are stuck with me for a while.

Thanks again!

NULLReferenceException from SqlDataReader.GetValue()

I am getting a random exception coming out of ADO.NET's SqlDataReader that I can't seem to track down for the life of me. I have put SQL Server and IIS under heavy load, with neither giving me the exceptions except occasionally (they come when there is no load as well). Web Site is built in VS2005 using .NET 2.0.

Basically, I have a web page that caches a few tables from a SQL database (about 10 tables are loaded into cache from this page) and the error can come from any of the tables while they are being read from the database randomly. I had assumed it was possible that a timeout was occuring while reading the data but from what the brief results google shows, there would be a different exception thrown it a timeout had occured.

Here is the exception that is showing up:

System.NullReferenceException: Object reference not set to an instance of an object.
at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at Library.Data.DataProxy.ExecuteStoredProcedure(ArrayList& returnValues, TypeFactory factory)

I got the following exception once from the same section of code as well:

System.InvalidOperationException: Invalid attempt to FieldCount when reader is closed.
at System.Data.SqlClient.SqlDataReader.get_FieldCount()
at Library.Data.DataProxy.ExecuteStoredProcedure(ArrayList& returnValues, TypeFactory factory)

The code generating these errors is on line 29 below:

1 /// <summary>
2 /// Executes a stored procedure that returns values
3 /// </summary>
4 /// <param name="returnValues">An arraylist of Types</param>
5 /// <param name="factory">The Type specific factory</param>
6 /// <returns>True if successful, false otherwise</returns>
7 public bool ExecuteStoredProcedure(out ArrayList returnValues, TypeFactory factory)
8 {
9 bool bResult =true;
10 SqlDataReader reader =null;
11
12 returnValues =new ArrayList();
13 MenseType nextType =null;
14
15 try
16 {
17 // Open database connection
18 OpenConnection();
19
20 // execute SP
21 reader = m_sqlCommand.ExecuteReader();
22
23 while(reader.Read())
24 {
25 nextType = factory.CreateInstance();
26
27 for(int i = 0; i < reader.FieldCount; i++)
28 {
29 object val = reader.GetValue(i);
30
31 if(DBNull.Value == val)
32 val =null;
33
34 nextType.SetValue(reader.GetName(i), val);
35 }
36
37 returnValues.Add(nextType);
38
39 }// while
40
41 }// @. try
42 catch(Exception ex)
43 {
44 returnValues =null;
45 m_LastError = ex.ToString();
46 bResult =false;
47
48 #if(DEBUG)
49 Library.IO.Logging.AddSQLMessage(ex);
50 #endif
51
52 }// @. catch
53 finally
54 {
55 // close the reader if nessessary
56 if (null != reader)
57 reader.Close();
58
59 // close db connection
60 CloseConnection();
61
62 }// @. finally
63
64 return bResult;
65
66 }// ExecuteStoredProcedure()

Any input would be greatly appreciated.

Do you need to read every table as an untyped arraylist, why not read each column of each table as a specific data type into a typed structure?